MYSQL实验之一

作者: M36_tongwei | 来源:发表于2019-05-06 10:26 被阅读2次

MYSQL实验之一
SQL的介绍及MySQL的安装
MySQL 表分区
Mysql远程连接
Mysql高可用之MHA部署
mysql5.7.2 主从复制
7. MySQL中间件代理服务器-Mycat
MySQL
Mysql主从复制
在liunx 下配置javaweb服务器环境

注意事项，以下实验都是在mariadb-5.5.60版本做的

一、mysqldump命令实现完全备份，并还原到最新状态

前提条件：启用二进制日志，独立存放在单独磁盘上

[root@centos7 network-scripts]#vim /etc/my.cnf

[mysqld]
log_bin=/data/bin/mysql-bin     
[root@centos7 ~]#mkdir /data/bin
[root@centos7 ~]#chown mysql.mysql /data/bin
[root@centos7 ~]#ll /data/bin -d
drwxr-xr-x 2 mysql mysql 6 May  6 09:06 /data/bin
    
[root@centos7 network-scripts]#mysql -e "show variables like 'sql_log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
[root@centos7 network-scripts]#systemctl restart mariadb
[root@centos7 network-scripts]#ll /data/bin/
total 8
-rw-rw---- 1 mysql mysql 245 May  6 09:07 mysql-bin.000001
-rw-rw---- 1 mysql mysql  27 May  6 09:07 mysql-bin.index

完全备份

[root@centos7 ~]#mkdir /data/backup
[root@centos7 ~]#mysqldump -A --single-transaction --master-data=2 > /data/backup/all_`date +%F`.sql
[root@centos7 ~]#ll /data/backup/
total 504
-rw-r--r-- 1 root root 515088 May  6 09:09 all_2019-05-06.sql

修改数据库

[root@centos7 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> insert teachers (name,age)value('xiaoming',30);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> insert teachers (name,age)value('xiaohong',40);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

数据库破坏

[root@centos7 ~]#rm -fr /var/lib/mysql/*
[root@centos7 ~]#ll /var/lib/mysql/
total 0

还原
- 5.1 查看二进制文件列表

[root@centos7 ~]#ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 721 May  6 09:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql  27 May  6 09:18 mysql-bin.index

5.2 systemctl restart mariadb

[root@centos7 ~]#systemctl restart mariadb
[root@centos7 ~]#ll /data/bin
total 1060
-rw-rw---- 1 mysql mysql     740 May  6 09:29 mysql-bin.000001
-rw-rw---- 1 mysql mysql   30373 May  6 09:29 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1038814 May  6 09:29 mysql-bin.000003
-rw-rw---- 1 mysql mysql     245 May  6 09:29 mysql-bin.000004
-rw-rw---- 1 mysql mysql     108 May  6 09:29 mysql-bin.index

MariaDB [(none)]> set sql_log_bin=off; 注：关闭二进制日志功能，防止还原过程再次记录到二进制日志文件中。
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> source /data/backup/all_2019-05-06.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

表明完全备份的数据库还原了

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [test]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

5.4 查看备份时二进制日志的位置,从此位置后的日志导出到inc.sql

[root@centos7 ~]#less /data/backup/all_2019-05-06.sql

image.png

[root@centos7 ~]#mysqlbinlog --start-position=245 /data/bin/mysql-bin.000001 > /data/backup/inc.sql
[root@centos7 ~]#ll /data/backup/
total 516
-rw-r--r-- 1 root root 521622 May  6 09:18 all_2019-05-06.sql
-rw-r--r-- 1 root root   2397 May  6 09:45 inc.sql

5.5 还原完全备份之后数据库改变的内容

MariaDB [hellodb]> source /data/backup/inc.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> set sql_log_bin=on;  注：启动二进制日志功能
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

二、新版xtrabackup实现完全备份

实验前准备工作，安装

[root@centos7 ~]#yum -y install /root/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

在原主机做完全备份到/data/backup 注：/data/backup是自己提前自定义建的放备份文件的目录
- 2.1

[root@centos7 ~]#rm -f /data/backup/*
[root@centos7 ~]#xtrabackup --backup --target-dir=/data/backup/
[root@centos7 ~]#ll /data/backup/
total 18460
-rw-r----- 1 root root      431 May  6 10:57 backup-my.cnf
drwxr-x--- 2 root root      146 May  6 10:57 hellodb
-rw-r----- 1 root root 18874368 May  6 10:57 ibdata1
drwxr-x--- 2 root root     4096 May  6 10:57 mysql
drwxr-x--- 2 root root     4096 May  6 10:57 performance_schema
drwxr-x--- 2 root root       20 May  6 10:57 test
-rw-r----- 1 root root       21 May  6 10:57 xtrabackup_binlog_info
-rw-r----- 1 root root      113 May  6 10:57 xtrabackup_checkpoints
-rw-r----- 1 root root      468 May  6 10:57 xtrabackup_info
-rw-r----- 1 root root     2560 May  6 10:57 xtrabackup_logfile

2.2将备份文件拷贝到目标主机上

[root@centos7 ~]#scp -r /data/backup/* 192.168.18.17:/data/backup

在目标主机上

[root@centos7bp ~]#yum -y install /root/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

3.1 预准备：确保数据一致，提交完成的事务，回滚未完成的事务

[root@centos7bp ~]#ll /data/backup
total 18460
-rw-r----- 1 root root      431 May  6 11:04 backup-my.cnf
drwxr-x--- 2 root root      146 May  6 11:04 hellodb
-rw-r----- 1 root root 18874368 May  6 11:04 ibdata1
drwxr-x--- 2 root root     4096 May  6 11:04 mysql
drwxr-x--- 2 root root     4096 May  6 11:04 performance_schema
drwxr-x--- 2 root root       20 May  6 11:04 test
-rw-r----- 1 root root       21 May  6 11:04 xtrabackup_binlog_info
-rw-r----- 1 root root      113 May  6 11:04 xtrabackup_checkpoints
-rw-r----- 1 root root      468 May  6 11:04 xtrabackup_info
-rw-r----- 1 root root     2560 May  6 11:04 xtrabackup_logfile

[root@centos7bp ~]#xtrabackup --prepare --target-dir=/data/backup

[root@centos7bp ~]#ll /data/backup
total 49180
-rw-r----- 1 root root      431 May  6 11:04 backup-my.cnf
drwxr-x--- 2 root root      146 May  6 11:04 hellodb
-rw-r----- 1 root root 18874368 May  6 11:09 ibdata1
-rw-r----- 1 root root  5242880 May  6 11:09 ib_logfile0
-rw-r----- 1 root root  5242880 May  6 11:09 ib_logfile1
-rw-r----- 1 root root 12582912 May  6 11:09 ibtmp1
drwxr-x--- 2 root root     4096 May  6 11:04 mysql
drwxr-x--- 2 root root     4096 May  6 11:04 performance_schema
drwxr-x--- 2 root root       20 May  6 11:04 test
-rw-r----- 1 root root       21 May  6 11:04 xtrabackup_binlog_info
-rw-r----- 1 root root      113 May  6 11:09 xtrabackup_checkpoints
-rw-r----- 1 root root      468 May  6 11:04 xtrabackup_info
-rw-r----- 1 root root  8388608 May  6 11:09 xtrabackup_logfile
-rw-r--r-- 1 root root        1 May  6 11:09 xtrabackup_master_key_id

3.2 复制到数据库目录
a. 将备份文件复制到数据库目录之前，数据库目录必须为空，Mysql服务不能启动

[root@centos7bp ~]#systemctl stop mariadb
[root@centos7bp ~]#rm -fr /var/lib/mysql/*

b. 将备份文件复制到数据库目录

[root@centos7bp ~]#xtrabackup --copy-back --target-dir=/data/backup

3.3 还原属性

image.png

[root@centos7bp ~]#chown -R mysql:mysql /var/lib/mysql/
[root@centos7bp ~]#ll /var/lib/mysql/
total 40976
drwxr-x--- 2 mysql mysql      146 May  6 11:13 hellodb
-rw-r----- 1 mysql mysql 18874368 May  6 11:13 ibdata1
-rw-r----- 1 mysql mysql  5242880 May  6 11:13 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 May  6 11:13 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May  6 11:13 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  6 11:13 mysql
drwxr-x--- 2 mysql mysql     4096 May  6 11:13 performance_schema
drwxr-x--- 2 mysql mysql       20 May  6 11:13 test
-rw-r----- 1 mysql mysql      468 May  6 11:13 xtrabackup_info
-rw-r----- 1 mysql mysql        1 May  6 11:13 xtrabackup_master_key_id

3.4 启动服务

[root@centos7bp ~]#systemctl start mariadb

[root@centos7bp ~]#systemctl start mariadb
[root@centos7bp ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@centos7bp ~]#mysql -e 'select * from hellodb.teachers'
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
+-----+---------------+-----+--------+

三、新版xtrabackup完全，增量备份及还原

备份过程（在原主机上备份数据库）
- 1.1 完全备份：xtrabackup --backup --target-dir=/data/backup/base

[root@centos7 backup]#rm -fr ./*
[root@centos7 backup]#ls
[root@centos7 backup]#xtrabackup --backup --target-dir=/data/backup/base
[root@centos7 backup]#ls
base

1.2 第一次修改数据

[root@centos7 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> insert teachers (name,age)value('aaa',18);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
|   7 | aaa           |  18 | NULL   |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)

1.3 第一次增量备份：xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

[root@centos7 backup]#xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
[root@centos7 backup]#ls
base  inc1

1.4第二次修改数据

MariaDB [hellodb]> insert teachers (name,age)value('bbb',28);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
|   7 | aaa           |  18 | NULL   |
|   8 | bbb           |  28 | NULL   |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

1.5 第二次增量备份：xtrabackup --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1

[root@centos7 backup]#xtrabackup --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1
ll

[root@centos7 backup]#ll
total 0
drwxr-x--- 6 root root 217 May  6 12:35 base
drwxr-x--- 6 root root 243 May  6 12:48 inc1
drwxr-x--- 6 root root 243 May  6 12:55 inc2

1.6 scp -r /data/backup/* 目标主机:/data/backup/

[root@centos7bp ~]#rm -fr /data/backup/*  注：centos7bp该主机为目标主机

[root@centos7 backup]#scp -r /data/backup/* 192.168.18.17:/data/backup/

[root@centos7bp ~]#ll /data/backup/  注：备份过程生成三个备份目录
total 0
drwxr-x--- 6 root root 217 May  6 12:59 base
drwxr-x--- 6 root root 243 May  6 12:59 inc1
drwxr-x--- 6 root root 243 May  6 12:59 inc2

还原过程(在目标主机上还原数据库)

2.1 预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务

[root@centos7bp ~]#xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

2.2 合并第1次增量备份到完全备

[root@centos7bp ~]#xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1

2.3 合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only

[root@centos7bp ~]#xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2

2.4 复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/data/backup/base

[root@centos7bp ~]#rm -fr /var/lib/mysql/*
[root@centos7bp ~]#systemctl stop mariadb
[root@centos7bp ~]#xtrabackup --copy-back --target-dir=/data/backup/base

[root@centos7bp backup]#ll /var/lib/mysql/
total 40980
drwxr-x--- 2 root root      146 May  6 13:15 hellodb
-rw-r----- 1 root root 18874368 May  6 13:15 ibdata1
-rw-r----- 1 root root  5242880 May  6 13:15 ib_logfile0
-rw-r----- 1 root root  5242880 May  6 13:15 ib_logfile1
-rw-r----- 1 root root 12582912 May  6 13:15 ibtmp1
drwxr-x--- 2 root root     4096 May  6 13:15 mysql
drwxr-x--- 2 root root     4096 May  6 13:15 performance_schema
drwxr-x--- 2 root root       20 May  6 13:15 test
-rw-r----- 1 root root       31 May  6 13:15 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      518 May  6 13:15 xtrabackup_info
-rw-r----- 1 root root        1 May  6 13:15 xtrabackup_master_key_id

2.5 还原属性：chown -R mysql:mysql /var/lib/mysql

[root@centos7bp backup]#chown -R mysql:mysql /var/lib/mysql/
[root@centos7bp backup]#ll /var/lib/mysql/
total 40980
drwxr-x--- 2 mysql mysql      146 May  6 13:15 hellodb
-rw-r----- 1 mysql mysql 18874368 May  6 13:15 ibdata1
-rw-r----- 1 mysql mysql  5242880 May  6 13:15 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 May  6 13:15 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May  6 13:15 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  6 13:15 mysql
drwxr-x--- 2 mysql mysql     4096 May  6 13:15 performance_schema
drwxr-x--- 2 mysql mysql       20 May  6 13:15 test
-rw-r----- 1 mysql mysql       31 May  6 13:15 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      518 May  6 13:15 xtrabackup_info
-rw-r----- 1 mysql mysql        1 May  6 13:15 xtrabackup_master_key_id

2.6 启动服务：systemctl start mariadb

[root@centos7bp backup]#systemctl start mariadb
[root@centos7bp backup]#mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaoming      |  30 | NULL   |
|   6 | xiaohong      |  40 | NULL   |
|   7 | aaa           |  18 | NULL   |
|   8 | bbb           |  28 | NULL   |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

四、主从复制

主节点
- 1.1 修改配置

[root@centos7 backup]#vim /etc/my.cnf

[mysqld]
server_id=7
binlog_format=row  
log_bin=/data/bin/mysql-bin

[root@centos7 backup]#systemctl restart mariadb 注：修改配置文件重启服务

1.2 查看位置信息：show master logs

[root@centos7 backup]#mysql -e 'show master logs'
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |  注：最新的位置信息
+------------------+-----------+

1.3 创建有复制权限的可用账号
GRANT REPLICATION SLAVE ON . TO 'repluser'@'HOST' IDENTIFIED BY 'replpass'

MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.18.17' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

从节点配置
- 2.1 修改配置

[root@centos7bp backup]#vim /etc/my.cnf

[mysqld]
server_id=17
read_only=ON  

[root@centos7bp backup]#systemctl restart mariadb 注：重启服务

2.2 配置同步信息

[root@centos7bp backup]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.18.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=711;
Query OK, 0 rows affected (0.01 sec)

2.3 启动复制tread

MariaDB [(none)]> start slave;  注：启动重线程
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 注：二进制日志文件
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 687
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  注：io线程启动
            Slave_SQL_Running: Yes  注：sql线程启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 403
              Relay_Log_Space: 983
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 7  注：主服务器id为7
1 row in set (0.00 sec)

验证主从服务器是否同步
主服务器

[root@centos7 ~]#mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

从服务器

[root@centos7bp ~]#mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

在主服务器上导入一个数据库

[root@centos7 ~]#mysql < hellodb_innodb.sql 
[root@centos7 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

查看从服务器是否同步数据

[root@centos7bp ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

注：通过上面对比，主从服务器已经同步

五、实验：基于一台旧服务的基础上，实现主从复制（增加一台从服务器）

主节点
- 1.1 修改配置

[root@centos7 backup]#vim /etc/my.cnf

[mysqld]
server_id=7
binlog_format=row  
log_bin=/data/bin/mysql-bin

[root@centos7 backup]#systemctl restart mariadb 注：修改配置文件重启服务

[root@centos7 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

1.2 创建有复制权限的可用账号
GRANT REPLICATION SLAVE ON . TO 'repluser'@'HOST' IDENTIFIED BY 'replpass'

MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.18.17' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

1.3 完全备份

[root@centos7 backup]#mysqldump -A --single-transaction -F --master-data=1 > /data/backup/all.sql

1.4 将备份数据库传到目标主机上

[root@centos7 backup]#scp /data/backup/all.sql 192.168.18.17:/data/backup/
root@192.168.18.17's password: 
all.sql                                                               100%  510KB  19.5MB/s   00:00

查看传过来的数据库备份文件

[root@centos7bp ~]#ll /data/backup/
total 512
-rw-r--r-- 1 root root 521834 May  6 15:23 all.sql

从节点
- 2.1 修改配置

[root@centos7bp backup]#vim /etc/my.cnf

[mysqld]
server_id=17                                                                                            
read_only=ON

[root@centos7bp backup]#systemctl restart mariadb

修改前数据库备份文件

[root@centos7bp backup]#vim all.sql 

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)                                         
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.60-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=245;

--

修改后数据库备份文件，注注：将主服务器上增加的用户信息填进来

[root@centos7bp backup]#vim all.sql 

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.60-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_HOST='192.168.18.7',  注：增加主机
MASTER_USER='repluser',  注：用户
MASTER_PASSWORD='123456',  注：用户密码
MASTER_PORT=3306,  注：端口
MASTER_LOG_FILE='mysql-bin.000003', 
MASTER_LOG_POS=245;                                                                                     
注：将主服务器上增加的用户信息填进来

2.2 导入备份数据库文件

[root@centos7bp backup]#mysql < all.sql

root@centos7bp backup]#mysql -e 'select * from hellodb.teachers'
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+

2.3 查看线程状态

root@centos7bp backup]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.18.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No  注：io线程没有启动
            Slave_SQL_Running: No  注：sql线程没有启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 245
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.01 sec)

ERROR: No query specified

2.4 启动tread线程

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 630
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 914
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes 注：线程启动
            Slave_SQL_Running: Yes  注：线程启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 630
              Relay_Log_Space: 1210
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 7
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | aaa           |  19 | NULL   |
|   6 | bb            |  29 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
注：此种操作说明，备份数据库文件之后，就算主服务器数据库发生了变化，从服务器只要启动start slave ，所有改变的数据同时都同步过来了

MYSQL实验之一
注意事项，以下实验都是在mariadb-5.5.60版本做的一、mysqldump命令实现完全备份，并还原到最新...
SQL的介绍及MySQL的安装
说明：文章内容截选自实验楼教程【MySQL 基础课程】第一节内容~ 一、实验介绍本课程为实验楼提供的 MySQL...
MySQL 表分区
实验环境数据库：MySQL 5.7.24官方文档：https://dev.mysql.com/doc/refma...
Mysql远程连接
实验环境 ubuntu-16.04.1 mysql 5.7.16 xshell_5.0.0.37 ...
Mysql高可用之MHA部署
实验环境其中: 系统为Centos 6.5 x86_64 mysql版本为 mysql 5.5 Note本文中出...
mysql5.7.2 主从复制
实验环境：系统：CentOS Linux release 7.3.1611 (Core) MySQL versi...
7. MySQL中间件代理服务器-Mycat
8 MySQL中间件代理服务器-Mycat 实验拓扑: MySQL: 5.7.31版本Mycat: 1.6.7.4...
MySQL
MySQL MySQL概要和历史 MySQL是当下最火的开源关系型数据库之一，属于Oracle旗下产品，MySQL...
Mysql主从复制
实验环境 ubuntu-16.04.1 mysql 5.7.16 xshell_5.0.0.37 Virt...
在liunx 下配置javaweb服务器环境
此实验教大家如何配置 JDK 、Tomcat 和 Mysql 实验资源：云服务器软件环境：CentOS 7.2 ...

MYSQL实验之一

一、mysqldump命令实现完全备份，并还原到最新状态

二、新版xtrabackup实现完全备份

三、新版xtrabackup完全，增量备份及还原

四、主从复制

五、实验：基于一台旧服务的基础上，实现主从复制（增加一台从服务器）

相关文章

MYSQL实验之一

SQL的介绍及MySQL的安装

MySQL 表分区

Mysql远程连接

Mysql高可用之MHA部署

mysql5.7.2 主从复制

7. MySQL中间件代理服务器-Mycat

MySQL

Mysql主从复制

在liunx 下配置javaweb服务器环境

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

高性能MySQL