Mysql 5.7 基于Binlog的多源复制

MySQL多源复制使复制从服务器可以同时接收来自多个来源的事务。多源复制可用于将多台服务器备份到单台服务器，合并表分片并将多台服务器的数据合并到单台服务器。多源复制在应用事务时不会执行任何冲突检测或解决方案，并且如果需要，这些任务将留给应用程序。在多源复制拓扑中，从服务器为每个主服务器创建一个复制通道，以便从中接收事务。

环境说明：

maseter1：192.168.1.32:3306 redhat6.7
maseter2：192.168.1.33:3306 redhat6.7
slave1：192.168.1.32:3406 redhat6.7

1 关闭主从服务器防火墙

[root@www.cndba.cn ~]# systemctl stop firewalld
[root@www.cndba.cn ~]# systemctl disable firewalld

2 修改主从库/etc/my.cnf配置文件

Master1 参数文件修改
server_id = 32
log-bin = master1-bin
Master2 参数文件修改
server_id = 33
log-bin = master2-bin
Slave 参数文件修改
server_id    = 34
relay-log    = relay-bin
master-info-repository    = TABLE
relay-log-info-repository = TABLE

3 创建测试数据

master1 创建数据库
mysql> create database test32;
Query OK, 1 row affected (0.10 sec)

mysql> use test32
Database changed
mysql> create table t1(id int(5));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Master2 创建数据库
mysql> create database test33;
Query OK, 1 row affected (0.01 sec)

mysql> use test33
Database changed
mysql> create table t2(id int(5));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t2 values(2);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

4 导出需要同步的数据库

分别在master1和master2上导出需要同步的数据库
在 master1 上备份要同步的数据库
mysqldump -uroot -p --master-data=2 --single-transaction --add-drop-database --databases test32  >/backup/test32.sql
在master2 上备份要同步的数据库
mysqldump -uroot -p --master-data=2 --single-transaction --add-drop-database --databases test33  >/backup/test33.sql
分别把备份scp到slave上
[root@www.cndba.cn backup]# scp test32.sql 192.168.1.34:/backup/
[root@www.cndba.cn ~]# scp /backup/test33.sql 192.168.1.34:/backup/

5 在master1和master2上创建复制账号

这个操作跟MySQL 5.7之前版本一样：
在master1 
mysql> grant replication slave on *.* to 'rep1'@'192.168.1.34' identified by 'rep1';
Query OK, 0 rows affected, 1 warning (0.30 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在master2 
mysql> grant replication slave on *.* to 'rep2'@'192.168.1.34' identified by 'rep2';
Query OK, 0 rows affected, 1 warning (0.30 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

6 初始化Slave服务器

分别Slave上把master1和master2的数据导入Slave服务器
在导入前先确认是否修改MySQL存储master-info和relay-info的方式即从文件存储改为表存储，在my.cnf里添加以下选择：
master_info_repository=TABLE
relay_log_info_repository=TABLE

也可以在线修改，灰常方便：
mysql>  stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL master_info_repository = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

在slave 上恢复两个主库要同步的数据库
[root@www.cndba.cn backup]# mysql -uroot -p < /backup/test32.sql 
[root@www.cndba.cn backup]# mysql -uroot -p < /backup/test33.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test32             |
| test33             |
+--------------------+

7 查看主库binlog位置和pos位置

分别找出master1和master2的binlog位置和pos位置：
[root@www.cndba.cn backup]# cat /backup/test32.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='master1-bin.000002', MASTER_LOG_POS=154;
[root@www.cndba.cn backup]# cat /backup/test33.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='master2-bin.000002', MASTER_LOG_POS=154;

8 登录Slave进行同步操作

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.32',MASTER_USER='rep1', MASTER_PASSWORD='rep1',MASTER_LOG_FILE='master1-bin.000002',MASTER_LOG_POS=154 FOR CHANNEL 'Master_1';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.33',MASTER_USER='rep2', MASTER_PASSWORD='rep2',MASTER_LOG_FILE='master2-bin.000002',MASTER_LOG_POS=154 FOR CHANNEL 'Master_2';
Query OK, 0 rows affected, 2 warnings (0.07 sec)

9 启动slave 复制

默认开启所有的复制通道
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
也可以指定复制通道开启
mysql> start slave for CHANNEL  'Master_1';
Query OK, 0 rows affected (0.01 sec)

mysql> start slave for CHANNEL  'Master_2';
Query OK, 0 rows affected (0.04 sec)

10 查看从库复制状态

注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。
show slave status/G;
或者单独查看每个通道的复制状态
mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'/G;
mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_1'/G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.32
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin-master_1.000007
                Relay_Log_Pos: 371
        Relay_Master_Log_File: master1-bin.000004
             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: 154
              Relay_Log_Space: 749
              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: 32
                  Master_UUID: 6715b696-3d3c-11e8-b66c-08002703bb35
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master_1
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'/G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.33
                  Master_User: rep2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master2-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin-master_2.000007
                Relay_Log_Pos: 371
        Relay_Master_Log_File: master2-bin.000004
             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: 154
              Relay_Log_Space: 749
              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: 33
                  Master_UUID: 73acf59b-3d3c-11e8-b976-080027686323
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master_2
           Master_TLS_Version: 
1 row in set (0.00 sec)

11 测试数据库同步

--master1 插入数据
mysql> use test32
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

--master2 插入数据
mysql> use test33
mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> insert into t2 values(3);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

slave 查看数据被同步过来
mysql> select * from test32.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from test33.t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)