MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server.
Masters in a multi-source replication topology can be configured to use either global transaction identifier (GTID) based replication, or binary log position-based replication.
Slaves in a multi-source replication topology require TABLE based repositories.
master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = 1
There are 2 ways to configure in multi source Replication.
1) Global transaction identifier (GTID) based replication.
2) Binary log position-based replication.
Global transaction identifier (GTID) based replication.
Enable the GTID_mode=ON. ( log_slave_update is not mandatory for GTID in MySQL 5.7 )
Use the CHANGE MASTER TO statement to add a new master to a channel by using a FOR CHANNEL channel clause.
CHANGE MASTER TO MASTER_HOST='master_server1', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1';
Multi-source replication is compatible with auto-positioning.Repeat this process for each extra master that you want to add to a channel, changing the host name, port and channel as appropriate.
Binary Log Based Multi-Source Replication
We have to enable binary logging on the master using log_bin and we need current master_log_file and master_log_position and add the master channel by using For Channel channel_name.
CHANGE MASTER TO MASTER_HOST='master_server1', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl_password' MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';
Each communication channel is a connection from slave to master to get the binary log events. That means we will have one IO_THREAD for each communication channel. We will need to run different “CHANGE MASTER” commands, one for each master, with the “FOR CHANNEL” argument that we will use to give a name to that channel.
Starting and Stopping Multi-Source Replication Slaves
To start the slave processes you need to specify what channel are you referring to:
slave > start slave for channel="master-1"; slave > start slave for channel="master-2";
To stop only a named channel, use a FOR CHANNEL channel clause:
slave > stop slave for channel="master-1"; slave > stop slave for channel="master-2";
To reset only a named channel, use a FOR CHANNEL channel clause:
slave > RESET SLAVE FOR CHANNEL="master-1";
Now, we want to check the status of the slave:
slave > SHOW SLAVE STATUS FOR CHANNEL="master-1"G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master_server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master1-bin.000006 Read_Master_Log_Pos: 629 Relay_Log_File: relay-bin-master1.000003 Relay_Log_Pos: 395 Relay_Master_Log_File: master1-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes
To monitor the connection status of all channels we can use :
mysql> SELECT * FROM replication_connection_status\G;
mysql> SELECT * FROM replication_connection_status\G; *************************** 1. row *************************** CHANNEL_NAME: master-1 GROUP_NAME: SOURCE_UUID: 046e41f8-a223-11e4-a975-0811960cc264 THREAD_ID: 24 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 046e41f8-a223-11e4-a975-0811960cc264:4-37 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
It will shows the channel name , source_UUID , thread_ID .
The column LAST_HEARTBEAT_TIMESTAMP can be used find the lag.
Multi-Source Replication setup using Percona-Xtrabackup :
- Restore a full backup using percona xtrabackup from Master 1 on slave
- From Master 2 dump all the tables structures (CREATE TABLE) to slave. You can use mysqldump with –no-data
- Take a full backup from Master 2 and restore individual table using export option
Replication events are somehow serialized at slave side, just like a global counter that is not well documented yet. In reality this means that you need to be very careful when troubleshooting issues because you may suffer unexpected issues, for instance if you have 2 replication channels failing with a duplicate key error then is not easy to predict which even you will skip when running set global sql_slave_skip_counter=1
Reference for Multi-Master and Multi-Source replication click here