共计 5213 个字符，预计需要花费 14 分钟才能阅读完成。
MySQL 多源复制支持【多个低版本 ->MySQL 5.7】的结构, 这样就可以让多个实例的 schema 汇聚在一台实例上，而且无需升级 mysql 版本并避免未知风险, 用户只需要给这些实例安一个 MySQL 5.7 作为 slave 就可以了。
当然 Slave 必须为 MySQL 5.7
本次实验将使用 MySQL 5.6.x 作为多“主”。
〇 测试环境：
OS：CentOS 6.5
master_1: 192.168.1.185(MySQL 5.6.30)
master_2: 192.168.1.186(MySQL 5.6.30)
slave: 192.168.1.1.187(MySQL 5.7.15)
〇 配置：
master_1 相关配置：
- [mysqld]
- server_id = 185
- log–bin = master_1
- log–bin–index = master_1.index
master_2 相关配置：
- [mysqld]
- server_id = 186
- log–bin = master_2
- log–bin–index = master_2.index
slave 相关配置：
- [mysqld]
- server_id = 187
- relay–log = slave
- relay–log–index = slave.index
- # 多源复制结构中的 slave，官方要求 master–info 和 relay–log–info 存放处必须为 TABLE.
- # 如果为 FILE，则在添加多个 master 时，会失败：ER_SLAVE_NEW_CHANNEL_WRONG_REPOSITORY.
- master–info–repository = TABLE
- relay–log–info–repository = TABLE
〇 为 master_1 & master_2 上建立复制用户：
- GRANT REPLICATION SLAVE ON *.* to repl@‘192.168.1.187’ IDENTIFIED BY ‘repl’;
- FLUSH PRIVILEGES;
〇 测试数据准备：
master_1 测试数据：
- master_1> FLUSH LOGS;
- Query OK, 0 rows affected (0.00 sec)
- master_1> SHOW BINARY LOGS; — 记住当前 binlog 的 name 和 position
- +–––––––––––––––––+–––––––––––+
- | Log_name | File_size |
- +–––––––––––––––––+–––––––––––+
- | master_1.000001 | 166 |
- | master_1.000002 | 455 |
- | master_1.000003 | 120 |
- +–––––––––––––––––+–––––––––––+
- 3 rows in set (0.00 sec)
- master_1> CREATE DATABASE master_1;
- Query OK, 1 row affected (0.03 sec)
master_2 测试数据：
- master_2> FLUSH LOGS;
- Query OK, 0 rows affected (0.00 sec)
- master_2> SHOW BINARY LOGS; — 记住当前 binlog 的 name 和 position
- +–––––––––––––––––+–––––––––––+
- | Log_name | File_size |
- +–––––––––––––––––+–––––––––––+
- | master_2.000001 | 166 |
- | master_2.000002 | 455 |
- | master_2.000003 | 120 |
- +–––––––––––––––––+–––––––––––+
- 3 rows in set (0.00 sec)
- master_2> CREATE DATABASE master_2;
- Query OK, 1 row affected (0.02 sec)
〇 在 slave 上执行：
- salve> CHANGE MASTER TO
- –> MASTER_HOST=‘192.168.1.185’,
- –> MASTER_USER=‘repl’,
- –> MASTER_PORT=3306,
- –> MASTER_PASSWORD=‘repl’,
- –> MASTER_LOG_FILE=‘master_1.000003’,
- –> MASTER_LOG_POS=120
- –> FOR CHANNEL ‘master_1’;
- Query OK, 0 rows affected, 2 warnings (0.02 sec) — 此处产生的 warnings 是一些安全建议和警告，本实验无视。
- salve> CHANGE MASTER TO
- –> MASTER_HOST=‘192.168.1.186’,
- –> MASTER_USER=‘repl’,
- –> MASTER_PORT=3306,
- –> MASTER_PASSWORD=‘repl’,
- –> MASTER_LOG_FILE=‘master_2.000003’,
- –> MASTER_LOG_POS=120
- –> FOR CHANNEL ‘master_2’;
- Query OK, 0 rows affected, 2 warnings (0.02 sec)
- slave> START SLAVE;
- Query OK, 0 rows affected (0.01 sec)
- salve> SHOW DATABASES; — 此时在 master_1 和 master_2 上的 binlog events 已经被正常的 apply 了
- +––––––––––––––––––––+
- | Database |
- +––––––––––––––––––––+
- | information_schema |
- | master_1 |
- | master_2 |
- | mysql |
- | performance_schema |
- | sys |
- +––––––––––––––––––––+
- 6 rows in set (0.00 sec)
最后通过 start slave status 即可查到复制状态
- slave> SHOW SLAVE STATUS\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.1.185
- Master_User: repl
- Master_Port: 3306
- ……………………………………………………
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- ……………………………………………………
- Master_Server_Id: 185
- Master_UUID: ee1f8704-58c4-11e6-95b5-000c297f23b7
- 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
- ……………………………………………………
- Channel_Name: master_1
- Master_TLS_Version:
- *************************** 2. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.1.186
- Master_User: repl
- Master_Port: 3306
- Connect_Retry: 60
- ……………………………………………………
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- ……………………………………………………
- Master_Server_Id: 186
- Master_UUID: 53774f2d-7e14-11e6-8900-000c298e914c
- 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
- ……………………………………………………
- Channel_Name: master_2
- Master_TLS_Version:
- 2 rows in set (0.00 sec)
〇 测试：
master_1 上操作：
- master_1> CREATE TABLE master_1.test_table(id int);
- Query OK, 0 rows affected (0.05 sec)
- master_1> INSERT INTO master_1.test_table SELECT 666666;
- Query OK, 1 row affected (0.01 sec)
- Records: 1 Duplicates: 0 Warnings: 0
master_2 上操作：
- master_2> CREATE TABLE master_2.test_table(massage varchar(16));
- Query OK, 0 rows affected (0.02 sec)
- master_2> INSERT INTO master_2.test_table SELECT ‘ 嘿嘿嘿 ’;
- Query OK, 1 row affected (0.00 sec)
- Records: 1 Duplicates: 0 Warnings: 0
- master_2> INSERT INTO master_2.test_table SELECT ‘ 三阳之炎 ’;
- Query OK, 1 row affected (0.00 sec)
- Records: 1 Duplicates: 0 Warnings: 0
slave 上操作：
- salve> SELECT id FROM master_1.test_table;
- +––––––––+
- | id |
- +––––––––+
- | 666666 |
- +––––––––+
- 1 row in set (0.00 sec)
- salve> SELECT massage FROM master_2.test_table;
- +––––––––––––––+
- | massage |
- +––––––––––––––+
- | 嘿嘿嘿 |
- | 三阳之炎 |
- +––––––––––––––+
- 2 rows in set (0.00 sec)
〇 其他相关语法：
- START/STOP/RESET ALL/RESET SLAVE FOR CHANNEL ‘XXX‘;
- SHOW SLAVE STATUS FOR CHANNEL ‘XXX’;
ps.
与上述传统 position 方式类似，GTID 方式配置起来也类似，开启 GTID 后，需要注意使用 FOR CHANNEL ‘xxx’ 关键字即可，比如：
- CHANGE MASTER TO
- MASTER_HOST=”,
- MASTER_USER=‘repl’,
- MASTER_PORT=3306,
- MASTER_PASSWORD=‘repl’,
- MASTER_AUTO_POSITION = 1
- FOR CHANNEL ‘master_1’;
多台主机的 schema 名字不可以一样，（比如 master_1 为 db_00 … db_09 共 10 库，master_2 为 db_10 … db_19，master_3 为 db_20 … db_29 ……）
参考文档：
MySQL 5.7 Reference Manual 14 SQL Statement Syntax – 14.4.2.1 CHANGE MASTER TO Syntax
MySQL 5.7 Reference Manual 18 Replication – 18.1.4 MySQL Multi-Source Replication
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-03/142387.htm
正文完
星哥玩云-微信公众号
