MySQL5.6 -> MySQL5.7 跨版本多源複製(Multi-Source Replication)

神諭丶發表於2016-09-19
MySql
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相關配置：
  1. [mysqld]
  2. server_id    = 185
  3. log-bin      = master_1
  4. log-bin-index    = master_1.index

master_2相關配置：
  1. [mysqld]
  2. server_id    = 186
  3. log-bin      = master_2
  4. log-bin-index    = master_2.index

slave相關配置：
  1. [mysqld]
  2. server_id    = 187
  3. relay-log    = slave
  4. relay-log-index           = slave.index

  6. # 多源複製結構中的slave，官方要求master-info和relay-log-info存放處必須為TABLE.
  7. # 如果為FILE，則在新增多個master時，會失敗：ER_SLAVE_NEW_CHANNEL_WRONG_REPOSITORY.
  8. master-info-repository    = TABLE
  9. relay-log-info-repository = TABLE


〇 為master_1 & master_2上建立複製使用者：
  1. GRANT REPLICATION SLAVE ON *.* to repl@'192.168.1.187' IDENTIFIED BY 'repl';
  2. FLUSH PRIVILEGES;


〇 測試資料準備：
master_1測試資料：
  1. master_1> FLUSH LOGS;
  2. Query OK, 0 rows affected (0.00 sec)

  4. master_1> SHOW BINARY LOGS; -- 記住當前binlog的name和position
  5. +-----------------+-----------+
  6. | Log_name        | File_size |
  7. +-----------------+-----------+
  8. | master_1.000001 | 166       |
  9. | master_1.000002 | 455       |
  10. | master_1.000003 | 120       |
  11. +-----------------+-----------+
  12. 3 rows in set (0.00 sec)

  14. master_1> CREATE DATABASE master_1;
  15. Query OK, 1 row affected (0.03 sec)

master_2測試資料：
  1. master_2> FLUSH LOGS;
  2. Query OK, 0 rows affected (0.00 sec)

  4. master_2> SHOW BINARY LOGS;    -- 記住當前binlog的name和position
  5. +-----------------+-----------+
  6. | Log_name        | File_size |
  7. +-----------------+-----------+
  8. | master_2.000001 | 166       |
  9. | master_2.000002 | 455       |
  10. | master_2.000003 | 120       |
  11. +-----------------+-----------+
  12. 3 rows in set (0.00 sec)

  14. master_2> CREATE DATABASE master_2;
  15. Query OK, 1 row affected (0.02 sec)


〇 在slave上執行：
  1. salve> CHANGE MASTER TO
  2.     -> MASTER_HOST='192.168.1.185',
  3.     -> MASTER_USER='repl',
  4.     -> MASTER_PORT=3306,
  5.     -> MASTER_PASSWORD='repl',
  6.     -> MASTER_LOG_FILE='master_1.000003',
  7.     -> MASTER_LOG_POS=120
  8.     -> FOR CHANNEL 'master_1';
  9. Query OK, 0 rows affected, 2 warnings (0.02 sec)    -- 此處產生的warnings是一些安全建議和警告，本實驗無視。

  11. salve> CHANGE MASTER TO
  12.     -> MASTER_HOST='192.168.1.186',
  13.     -> MASTER_USER='repl',
  14.     -> MASTER_PORT=3306,
  15.     -> MASTER_PASSWORD='repl',
  16.     -> MASTER_LOG_FILE='master_2.000003',
  17.     -> MASTER_LOG_POS=120
  18.     -> FOR CHANNEL 'master_2';
  19. Query OK, 0 rows affected, 2 warnings (0.02 sec)

  21. slave> START SLAVE;
  22. Query OK, 0 rows affected (0.01 sec)

  24. salve> SHOW DATABASES;    -- 此時在master_1和master_2上的binlog events已經被正常的apply了
  25. +--------------------+
  26. | Database            |
  27. +--------------------+
  28. | information_schema |
  29. | master_1           |
  30. | master_2           |
  31. | mysql              |
  32. | performance_schema |
  33. | sys                |
  34. +--------------------+
  35. 6 rows in set (0.00 sec)

最後通過start slave status即可查到複製狀態
  1. slave> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.1.185
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 ……………………………………………………
  8.              Slave_IO_Running: Yes
  9.             Slave_SQL_Running: Yes
  10.                 ……………………………………………………
  11.              Master_Server_Id: 185
  12.                   Master_UUID: ee1f8704-58c4-11e6-95b5-000c297f23b7
  13.              Master_Info_File: mysql.slave_master_info
  14.                     SQL_Delay: 0
  15.           SQL_Remaining_Delay: NULL
  16.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  17.                ……………………………………………………
  18.                  Channel_Name: master_1
  19.            Master_TLS_Version:
  20. *************************** 2. row ***************************
  21.                Slave_IO_State: Waiting for master to send event
  22.                   Master_Host: 192.168.1.186
  23.                   Master_User: repl
  24.                   Master_Port: 3306
  25.                 Connect_Retry: 60
  26.                ……………………………………………………
  27.              Slave_IO_Running: Yes
  28.             Slave_SQL_Running: Yes
  29.                ……………………………………………………
  30.              Master_Server_Id: 186
  31.                   Master_UUID: 53774f2d-7e14-11e6-8900-000c298e914c
  32.              Master_Info_File: mysql.slave_master_info
  33.                     SQL_Delay: 0
  34.           SQL_Remaining_Delay: NULL
  35.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  36.                ……………………………………………………
  37.                  Channel_Name: master_2
  38.            Master_TLS_Version:
  39. 2 rows in set (0.00 sec)



〇 測試：
master_1上操作：
  1. master_1> CREATE TABLE master_1.test_table(id int);
  2. Query OK, 0 rows affected (0.05 sec)

  4. master_1> INSERT INTO master_1.test_table SELECT 666666;
  5. Query OK, 1 row affected (0.01 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

master_2上操作：
  1. master_2> CREATE TABLE master_2.test_table(massage varchar(16));
  2. Query OK, 0 rows affected (0.02 sec)

  4. master_2> INSERT INTO master_2.test_table SELECT '嘿嘿嘿';
  5. Query OK, 1 row affected (0.00 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. master_2> INSERT INTO master_2.test_table SELECT '三陽之炎';
  9. Query OK, 1 row affected (0.00 sec)
  10. Records: 1 Duplicates: 0 Warnings: 0

slave上操作：
  1. salve> SELECT id FROM master_1.test_table;
  2. +--------+
  3. | id     |
  4. +--------+
  5. | 666666 |
  6. +--------+
  7. 1 row in set (0.00 sec)


  10. salve> SELECT massage FROM master_2.test_table;
  11. +--------------+
  12. | massage      |
  13. +--------------+
  14. | 嘿嘿嘿        |
  15. | 三陽之炎      |
  16. +--------------+
  17. 2 rows in set (0.00 sec)



〇 其他相關語法：
  1. START/STOP/RESET ALL/RESET SLAVE FOR CHANNEL 'XXX';

  3. SHOW SLAVE STATUS FOR CHANNEL 'XXX';

ps.
與上述傳統position方式類似，GTID方式配置起來也類似，開啟GTID後，需要注意使用FOR CHANNEL 'xxx'關鍵字即可，比如：
  1. CHANGE MASTER TO
  2.     MASTER_HOST='',
  3.     MASTER_USER='repl',
  4.     MASTER_PORT=3306,
  5.     MASTER_PASSWORD='repl',
  6.     MASTER_AUTO_POSITION = 1
  7.     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


作者微信公眾號（持續更新）


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2125164/，如需轉載，請註明出處，否則將追究法律責任。

相關文章