MySQL5.7.10多元复制功能搭建

iojsioi

MySQL5.7.10多元复制功能搭建
　　1.环境：centos6.5
　　[root@mysql-m1 mysql-5.7.10]# cat /etc/redhat-release

　　CentOS>　　主机：
　　

mysql-m1   192.168.56.21　　
mysql -m2  192.168.56.22
　　
mysql-s    192.168.56.23
　　

　　现在是mysql-m1数据库已经搭建好，我使用自己的虚拟机进行clone 就省去多次安装数据库。
　　2.确保两个主库的server_id  log-bin 开启 并且三个库的server_id 不同：
　　

[root@mysql-m1 aliyun]# sed -n '36,37p' /etc/my.cnf　　
log-bin = /aliyun/data/mysqllogs/bin-log/mysql-bin
　　
server_id = 1
　　

　　
[root@mysql-m2 mysql-5.7.10]# sed -n '36,37p' /etc/my.cnf
　　
log-bin = /aliyun/data/mysqllogs/bin-log/mysql-bin
　　
server_id = 2
　　
[root@mysql-s mysql-5.7.10]# sed -n '36,37p' /etc/my.cnf
　　
log-bin = /aliyun/data/mysqllogs/bin-log/mysql-bin
　　
server_id = 3
　　

　　3.master上创建测试库和表：
　　3.1登陆数据库mysql-m1创建库test01 和表h1：
　　[root@mysql-m1 tmp]# mysql -uroot -p -S /tmp/mysql.sock
　　

mysql> create database test01;　　
Query OK, 1 row affected (0.01 sec)
　　
mysql> use test01;
　　
Database changed
　　

　　
mysql> CREATE TABLE `h1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `b1` int(11) DEFAULT NULL,   `b2` int(11) DEFAULT NULL,   `b3` int(11) GENERATED ALWAYS AS ((`b1` + `b2`)) VIRTUAL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
　　
Query OK, 0 rows affected (0.03 sec)
　　

　　
mysql> desc h1;
　　
+-------+---------+------+-----+---------+-------------------+
　　
| Field | Type    | Null | Key | Default | Extra             |
　　
+-------+---------+------+-----+---------+-------------------+

　　
|>　　
| b1    | int(11) | YES  |     | NULL    |                   |
　　
| b2    | int(11) | YES  |     | NULL    |                   |
　　
| b3    | int(11) | YES  |     | NULL    | VIRTUAL GENERATED |
　　
+-------+---------+------+-----+---------+-------------------+
　　
4 rows in set (0.00 sec)
　　

　　
mysql> insert into h1(b1,b2) values(2,2);
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> select * from h1;
　　
+----+------+------+------+

　　
|>　　
+----+------+------+------+
　　
|  1 |    2 |    2 |    4 |
　　
+----+------+------+------+
　　
1 row in set (0.00 sec)
　　

　　3.2登陆数据库mysql-m2创建库test01 和表和h2：
　　

mysql> create database test01;　　
Query OK, 1 row affected (0.03 sec)
　　

　　
mysql>  CREATE TABLE `h2` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `e1` int(11) DEFAULT NULL,   `e2` int(11) DEFAULT NULL,   `e3` int(11) GENERATED ALWAYS AS ((`e1` + `e2`)) VIRTUAL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
　　
Query OK, 0 rows affected (0.02 sec)
　　

　　
mysql> insert into h2(e1,e2) values(3,3);
　　
Query OK, 1 row affected (0.06 sec)
　　

　　
mysql> select * from h2;
　　
+----+------+------+------+

　　
|>　　
+----+------+------+------+
　　
|  1 |    3 |    3 |    6 |
　　
+----+------+------+------+
　　
1 row in set (0.00 sec)
　　

　　以下不停库锁表，自动切割bin-log日志，全量备份bin-log，导入从库中（此方法只适合5.5版本以下的数据库备份，不适合5.7版本的数据库备份，有待验证）（不是这篇文档的步骤）
　　

[root@mysql-m1 bin-log]# mysqldump -uroot -p -S /tmp/mysql.sock --master-data=2 -A -B -R -x -F --events|gzip >/tmp/m1.sql.gz　　

　　
[root@mysql-m2 mysql-5.7.10]#  mysqldump -uroot -p -S /tmp/mysql.sock --master-data=2 -A -B -R -x -F --events|gzip >/tmp/m2.sql.gz
　　

　　适合5.7版本的多元同步的数据库备份(不同的表同步到一个数据库中):
　　

[root@mysql-m1 tmp]#  mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases  --add-drop-database  test01  >m1.sql　　
[root@mysql-m2 tmp]#  mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases  --add-drop-database  test01  >m2.sql
　　
[root@mysql-m1 tmp]# cat m1.sql|grep "CHANGE MASTER"
　　
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;
　　
[root@mysql-m2 tmp]#  cat m2.sql|grep "CHANGE MASTER"
　　
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=154;
　　

　　
[root@mysql-m1 tmp]#  scp -rp -P22 m1.sql 192.168.56.23:/tmp/
　　
[root@mysql-m2 tmp]#  scp -rp -P22 m2.sql 192.168.56.23:/tmp/
　　

　　4.slave上操作：
　　

[root@mysql-s tmp]# ll /tmp/　　
total 396
　　
-rw-r--r--. 1 root  root  199233 May  8 01:15 m1.sql
　　
-rw-r--r--. 1 root  root  199243 May  8 01:14 m2.sql
　　
srwxrwxrwx. 1 mysql mysql      0 May  8 00:35 mysql.sock
　　
-rw-------. 1 mysql mysql      5 May  8 00:35 mysql.sock.lock
　　

[root@mysql-s tmp]# mysql -uroot -p -S /tmp/mysql.sock　　
mysql> show databases;
　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| sys                |
　　
| txt001             |
　　
+--------------------+
　　
5 rows in set (0.06 sec)
　　

　　分别在Slave上把Master_1和Master_2的数据导入Slave服务器，在导入前先修改从库的MySQL存储master-info和relay-info的方式，即从文件存储改为表存储，在my.cnf里添加以下选择：(注意此修改的是从库)
　　

[root@mysql-s tmp]# cat -n /etc/my.cnf|sed -n '36,39p'　　36  log-bin = /aliyun/data/mysqllogs/bin-log/mysql-bin
　　37  server_id = 1
　　38  master_info_repository=TABLE

　　39 >　　

　　或者再MySQL从库命令行操作：
　　

[root@mysql-s ~]# mysql -uroot -p -S /tmp/mysql.sock　　
mysql> stop slave;
　　
mysql> SET GLOBAL master_info_repository = 'TABLE';
　　
Query OK, 0 rows affected (0.00 sec)

　　
mysql> SET GLOBAL>　　
Query OK, 0 rows affected (0.00 sec)
　　

　　把数据导入数据库：
　　[root@mysql-s tmp]# mysql -uroot -p -S /tmp/mysql.sock  use test01;
　　
Database changed
　　
mysql> show tables;
　　
+------------------+
　　
| Tables_in_test01 |
　　
+------------------+
　　
| h1               |
　　
| h2               |
　　
+------------------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql> select * from h1;
　　
+----+------+------+------+
　　
|>　　
+----+------+------+------+
　　
|  1 |    2 |    2 |    4 |
　　
+----+------+------+------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> select * from h2;
　　
+----+------+------+------+
　　
|>　　
+----+------+------+------+
　　
|  1 |    3 |    3 |    6 |
　　
+----+------+------+------+
　　
1 row in set (0.00 sec)
　　

　　5.在mysql-m1和mysql-m2上创建复制账号：
　　这个操作跟MySQL 5.7之前版本一样：
　　在mysql-m1上创建
　　

mysql> grant replication slave on *.* to 'rep'@'192.168.56.23'>
mysql> flush privileges;（必须刷新权限否则不生效的）
　　

　　在mysql-m2上创建
　　

mysql> grant replication slave on *.* to 'rep'@'192.168.56.23'>
Query OK, 0 rows affected, 1 warning (0.02 sec
　　
mysql> flush privileges; （必须刷新权限否则不生效的）
　　

　　6.在slave从库上操作进行change master :
　　

[root@mysql-s ~]# mysql -uroot -p -S /tmp/mysql.sock　　
mysql> change master to MASTER_HOST='192.168.56.21' ,MASTER_USER='rep' ,MASTER_PASSWORD='123456' ,MASTER_LOG_FILE='mysql-bin.000012' ,MASTER_LOG_POS=154 FOR CHANNEL 'mysql-m1';
　　
Query OK, 0 rows affected, 2 warnings (0.01 sec)
　　

mysql>  change master to MASTER_HOST='192.168.56.22' ,MASTER_USER='rep' ,MASTER_PASSWORD='123456' ,MASTER_LOG_FILE='mysql-bin.000010' ,MASTER_LOG_POS=154 FOR CHANNEL 'mysql-m2';　　
Query OK, 0 rows affected, 2 warnings (0.03 sec)
　　

mysql> start slave for CHANNEL 'mysql-m1';　　
Query OK, 0 rows affected (0.02 sec)
　　

　　
mysql> start slave for CHANNEL 'mysql-m2';
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql>
　　

　　[root@mysql-s ~]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL 'mysql-m1'\G"|egrep  "Slave_IO_Running|Slave_SQL_Running"
　　mysql: [Warning] Using a password on the command line interface can be insecure.
　　Slave_IO_Running: Connecting  (报错) 此处为yes才是正常的
　　Slave_SQL_Running: Yes

　　Slave_SQL_Running_State: Slave has read all>　　7.同步报错解决方法:
　　7.1.看错误日志:
　　[root@mysql-s ~]# cat /aliyun/data/mysql/mysql_3306.err
　　7.2.检查防火墙是否关闭:
　　（从库本地和主库本地的防火墙）
　　

[root@mysql-s ~]# mysql -urep -p123456 -h192.168.56.21　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.21' (113)
　　

　　
[root@mysql-s ~]# mysql -urep -p123456 -h192.168.56.22
　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.22' (113)
　　

　　
[root@mysql-s ~]# /etc/init.d/iptables stop
　　
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
　　
iptables: Flushing firewall rules:                         [  OK  ]
　　
iptables: Unloading modules:
　　
[root@mysql-m1 tmp]# /etc/init.d/iptables stop
　　
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
　　
iptables: Flushing firewall rules:                         [  OK  ]
　　
iptables: Unloading modules:
　　

　　
[root@mysql-m2 tmp]# /etc/init.d/iptables stop
　　
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
　　
iptables: Flushing firewall rules:                         [  OK  ]
　　
iptables: Unloading modules:                               [  OK  ]
　　
[root@mysql-m2 tmp]#
　　

　　7.3. 修改/ect/my.cnf中server_id 未生效:
　　

[root@mysql-s ~]# mysql -urep -p123456 -h192.168.56.21 成功连接　　
但是从库连接mysql-m1还是报错：
　　
但是从库连接mysql-m2还是报错：
　　

　　
[root@mysql-s ~]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL 'mysql-m1'\G"|egrep  "Slave_IO_Running|Slave_SQL_Running|Last_IO_Error"
　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　Slave_IO_Running: No
　　Slave_SQL_Running: Yes

　　Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server>
　　Slave_SQL_Running_State: Slave has read all>　　Last_IO_Error_Timestamp: 160508 03:03:05
　　

　　
原因是/ect/my.cnf 配置文件中修改server_id时。没有重启服务，所以修改没有生效
　　
[root@mysql-s mysql-5.7.10]# cd /aliyun/server/mysql-5.7.10/
　　
pkill mysqld
　　
[root@mysql-s mysql-5.7.10]# sh start_mysql.sh
　　
三个数据库都同样操作
　　

　　7.4.但是还是报错:
　　

[root@mysql-s ~]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL 'mysql-m1'\G"|egrep  "Slave_IO_Running|Slave_SQL_Running|Last_IO_Error"　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　Slave_IO_Running: No
　　Slave_SQL_Running: Yes
　　Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

　　Slave_SQL_Running_State: Slave has read all>　　Last_IO_Error_Timestamp: 160508 03:23:36
　　

　　原因是mysql-s 虚拟机是基于mysql-m1的虚拟机克隆过来的，mysqlm1上的文件auto.cnf和mysql-s /mysql=m2上文件的auto.cnf 是一样的，所以报错
　　

[root@mysql-s mysql]# cat /aliyun/data/mysql/auto.cnf　　
[auto]
　　
server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66
　　

　　
[root@mysql-m1 mysql-5.7.10]# cat /aliyun/data/mysql/auto.cnf
　　
[auto]
　　
server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66
　　
[root@mysql-m2 mysql-5.7.10]# cat /aliyun/data/mysql/auto.cnf
　　
[auto]
　　
server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66
　　
把这三个auto.cnf文件中的数值改成随便不一样的就行。然后依次重启数据库，就好了
　　

[root@mysql-s mysql-5.7.10]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL 'mysql-m1'\G"|egrep  "Slave_IO_Running|Slave_SQL_Running|Last_IO_Error"　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Last_IO_Error:

　　Slave_SQL_Running_State: Slave has read all>　　Last_IO_Error_Timestamp:
　　

　　可以通过查看performance_schema相关的表查看同步状态，执行命令：
　　

mysql> SELECT * FROM performance_schema.replication_connection_status;　　
监控复制状态
　　
+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
　　
| CHANNEL_NAME | GROUP_NAME | SOURCE_UUID                          | THREAD_ID | SERVICE_STATE | COUNT_RECEIVED_HEARTBEATS | LAST_HEARTBEAT_TIMESTAMP | RECEIVED_TRANSACTION_SET | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
　　
+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
　　
| mysql-m1     |            | 3652e06e-1467-11e1-ab9f-000c2970db66 |      NULL | OFF           |                        37 | 2016-05-08 04:26:38      |                          |                 0 |                    | 0000-00-00 00:00:00  |
　　
| mysql-m2     |            | 3652e06e-1467-11e6-ab9f-000c2970db66 |        41 | ON            |                        47 | 2016-05-08 04:51:32      |                          |                 0 |                    | 0000-00-00 00:00:00  |
　　
+--------------
　　
+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+
　　

　　mysql> stop slave;(停止时必须同时开启各个slave，否则不能同步)
　　

Query OK, 0 rows affected (0.01 sec)　　

　　
mysql> set global sql_slave_skip_counter=10;
　　
mysql> start slave for CHANNEL 'mysql-m1';
　　
mysql> start slave for CHANNEL 'mysql-m2';
　　
mysql> SHOW SLAVE STATUS FOR CHANNEL 'mysql-m1'\G
　　
mysql> SHOW SLAVE STATUS FOR CHANNEL 'mysql-m2'\G
　　

　　8.MySQL 5.7的多源复制用处：
　　8.1、MySQL 5.7的多源复制，能有效的解决分库分表的数据统计问题，同时也可以实现在一台从服务器对多台主服务器的数据备份。
　　8.2、MySQL 5.7的多源复制的出现，我们就不需要使用MariaDB 的多主一从的架构了，让很多小伙伴又看到了新的希望。
　　参考资料：
　　http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
　　http://www.longlong.asia/2015/10/21/mysql57-new-features.html
　　MySQL-5.7文档看考资料：
　　http://www.cnblogs.com/xuanzhi201111/p/5148113.html
　　mysql多元复制资料地址
　　http://www.mamicode.com/info-detail-1189601.html
　　mysql 数据库5.7编译安装
　　http://www.cnblogs.com/xuanzhi201111/p/5148113.html
　　http://www.mamicode.com/info-detail-1189601.html
　　http://huqiji.iteye.com/blog/2068613