MySQL5.7から実装された、マルチソースレプリケーションに関しての質問も少しずつ増えてきたので、改めて基本的な挙動をこちらに纏めました。
基本的には、通常のレプリケーションの挙動と変わりませんが、CHANNELに分けて1台のスレーブが複数マスターからのログを受け取るので、運用上、気を付けないといけない部分が増えてくるので、リカバリー含むPOCはしておいた方が安心です。
■ マスター側の設定(複数マスターサーバー全体でサーバーID以外は同じ)
※ マスター側は通常のレプリケーションの設定をしています。(サーバーID, バイナリーログ等)
※ ログポジションベース、GTIDモードどちらも可能ですが、今回はGTIDで設定しています。
※ Masterに予めスレーブ用のアカウントを作成済み。
Optionファイルに設定した方が良いですが検証なので、動的に設定しています。
MySQL8.0ではここら辺の動的設定も永続化するオプションが追加されています。
以下の構成での説明になります
Master1(Port:63301) ---------------------> SLAVE(Port:63303) Master2(Port:63302) --------------------->
■ Slaveにて、各マスターへの接続設定を行います。
mysql> STOP SLAVE;SET GLOBAL master_info_repository = 'TABLE';SET GLOBAL relay_log_info_repository = 'TABLE'; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='multi_source', MASTER_PORT=63301, MASTER_PASSWORD='password', \ -> MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1'; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='multi_source', MASTER_PORT=63302, MASTER_PASSWORD='password', \ -> MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-2'; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> mysql> START SLAVE FOR CHANNEL 'master-1';START SLAVE FOR CHANNEL 'master-2'; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql>
■ レプリケーションステータスの確認
(CHANNELを指定しなければ、全てのCHANNELの接続情報が表示されます)
mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-1' \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: multi_source Master_Port: 63301 Connect_Retry: 60 Master_Log_File: Fabric02-bin.000001 Read_Master_Log_Pos: 885 Relay_Log_File: Fabric02-relay-bin-master@002d1.000002 Relay_Log_Pos: 423 Relay_Master_Log_File: Fabric02-bin.000001 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: 885 Relay_Log_Space: 686 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: 1 Master_UUID: a0c35acf-839f-11e6-89f4-080027d65c57 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: a0c35acf-839f-11e6-89f4-080027d65c57:1-3, a65948cd-839f-11e6-8cb5-080027d65c57:1-3, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 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: localhost Master_User: multi_source Master_Port: 63302 Connect_Retry: 60 Master_Log_File: Fabric02-bin.000001 Read_Master_Log_Pos: 885 Relay_Log_File: Fabric02-relay-bin-master@002d2.000002 Relay_Log_Pos: 1104 Relay_Master_Log_File: Fabric02-bin.000001 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: 885 Relay_Log_Space: 1327 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: 2 Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 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: a65948cd-839f-11e6-8cb5-080027d65c57:1-3 Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-3, a65948cd-839f-11e6-8cb5-080027d65c57:1-3, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-2 Master_TLS_Version: 1 row in set (0.00 sec) mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: multi_source Master_Port: 63301 Connect_Retry: 60 Master_Log_File: Fabric02-bin.000001 Read_Master_Log_Pos: 2102 Relay_Log_File: Fabric02-relay-bin-master@002d1.000002 Relay_Log_Pos: 1640 Relay_Master_Log_File: Fabric02-bin.000001 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: 2102 Relay_Log_Space: 1903 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: 1 Master_UUID: a0c35acf-839f-11e6-89f4-080027d65c57 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: a0c35acf-839f-11e6-89f4-080027d65c57:4-8 Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8, a65948cd-839f-11e6-8cb5-080027d65c57:1-6, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: multi_source Master_Port: 63302 Connect_Retry: 60 Master_Log_File: Fabric02-bin.000001 Read_Master_Log_Pos: 1623 Relay_Log_File: Fabric02-relay-bin-master@002d2.000002 Relay_Log_Pos: 1842 Relay_Master_Log_File: Fabric02-bin.000001 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: 1623 Relay_Log_Space: 2065 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: 2 Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 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: a65948cd-839f-11e6-8cb5-080027d65c57:1-6 Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8, a65948cd-839f-11e6-8cb5-080027d65c57:1-6, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-2 Master_TLS_Version: 2 rows in set (0.00 sec) mysql>
■ Performance_Schemaのコネクションテーブルからも接続を確認可能です。
mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-1'\G *************************** 1. row *************************** CHANNEL_NAME: master-1 GROUP_NAME: SOURCE_UUID: a0c35acf-839f-11e6-89f4-080027d65c57 THREAD_ID: 36 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 38 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:43:04 RECEIVED_TRANSACTION_SET: a0c35acf-839f-11e6-89f4-080027d65c57:4-8 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G *************************** 1. row *************************** CHANNEL_NAME: master-2 GROUP_NAME: SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 THREAD_ID: 38 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 39 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:43:01 RECEIVED_TRANSACTION_SET: a65948cd-839f-11e6-8cb5-080027d65c57:1-6 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) mysql>
全てのマスターからスレーブに配布するスキーマが異なっていれば、運用的な敷居は低いかと思います。
こちらの例では、マスター毎にスキーマ(データベース)が別です。
マスターとスレーブで同じスキーマ、同じテーブルを利用する場合は、データに矛盾が発生しないようにしないといけません。
予めIDが被らないようにするなどの設計と実装が必要です。
同じスキーマを対象にしている場合のオブジェクト変更は、特定のマスターのみで変更して他のマスターではBINLOGをOFFにして変更する等の運用が必要です。
(以下の例では同じオブジェクトに別々のIDでデータをINSERTしています)
こちらは、サーバーPORTにプラスで付けてます。何でも被らなければOKです。
リレーログは、各マスターサーバー毎に作成されます。
こちらの例では、relay-bin-master@002d1とrelay-bin-master@002d2でログがサーバー毎に分かれています。
-bash-4.2$ ls -l /home/mysql/fabric/mysql3/*relay* -rw-r-----. 1 mysql mysql 223 9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.000001 -rw-r-----. 1 mysql mysql 1411 9月 27 11:16 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.000002 -rw-r-----. 1 mysql mysql 82 9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.index -rw-r-----. 1 mysql mysql 223 9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.000001 -rw-r-----. 1 mysql mysql 1411 9月 27 11:17 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.000002 -rw-r-----. 1 mysql mysql 82 9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.index -bash-4.2$ /usr/local/mysql57/bin/mysqlbinlog -v Fabric02-relay-bin-master@002d1.000002 | grep CREATE GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3' CREATE TABLE MASTER01 ( -bash-4.2$ /usr/local/mysql57/bin/mysqlbinlog -v Fabric02-relay-bin-master@002d2.000002 | grep CREATE GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3' CREATE TABLE MASTER02 ( -bash-4.2$
■ RESET (特定CHANNELのみRESETしてますが、CHANNELオプション無しで全てRESETも可能です。)
mysql> STOP SLAVE FOR CHANNEL 'master-1'; Query OK, 0 rows affected (0.00 sec) mysql> RESET SLAVE FOR CHANNEL 'master-1'; Query OK, 0 rows affected (0.07 sec) mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-1'\G *************************** 1. row *************************** CHANNEL_NAME: master-1 GROUP_NAME: SOURCE_UUID: a0c35acf-839f-11e6-89f4-080027d65c57 THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 49 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:48:34 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G *************************** 1. row *************************** CHANNEL_NAME: master-2 GROUP_NAME: SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 THREAD_ID: 38 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 53 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:50:01 RECEIVED_TRANSACTION_SET: a65948cd-839f-11e6-8cb5-080027d65c57:1-6 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) mysql> mysql> STOP SLAVE FOR CHANNEL 'master-2'; Query OK, 0 rows affected (0.00 sec) mysql> RESET SLAVE FOR CHANNEL 'master-2'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G *************************** 1. row *************************** CHANNEL_NAME: master-2 GROUP_NAME: SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 55 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:51:01 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: localhost Master_User: multi_source Master_Port: 63301 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: Fabric02-relay-bin-master@002d1.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No 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: 0 Relay_Log_Space: 1903 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: 1 Master_UUID: a0c35acf-839f-11e6-89f4-080027d65c57 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: a0c35acf-839f-11e6-89f4-080027d65c57:1-8, a65948cd-839f-11e6-8cb5-080027d65c57:1-6, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: localhost Master_User: multi_source Master_Port: 63302 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: Fabric02-relay-bin-master@002d2.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No 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: 0 Relay_Log_Space: 2065 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: 2 Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: a0c35acf-839f-11e6-89f4-080027d65c57:1-8, a65948cd-839f-11e6-8cb5-080027d65c57:1-6, abf190f1-839f-11e6-8dbb-080027d65c57:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-2 Master_TLS_Version: 2 rows in set (0.00 sec) mysql>
参考:
https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-overview.html
http://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-tutorials.html