Multi-Source Replication in MySQL 5.7

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 )

gtid-mode=on
enforce-gtid-consistency

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

Ref: https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/restoring_individual_tables_ibk.html

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: