One of the prime feature that makes MySQL popular as an open source database from early days was Replication. MySQL Replication has under gone various improvements in MySQL 5.7. The prominent replication features in MySQL 5.7 are True Multi Threaded Replication ( MTS ) , Multi-source replication and Group Replication ( in lab) . Multi source replication is available from version 5.7.6.

Overview :

1. The Multi-source Replication slave enables to receive a transaction from multiple masters.
2. It is used to back up multiple servers to a single server , to merge the tables and consolidate data from multiple server’s to single server.
3. It helps in Analytic s by combining various databases under single server. Helps in backups too.
4. A slave creates a replication channel for each master.
5. Multi Source is available in MariaDB 10.  At MyDBOPS we have a successfull implemenatation of MariaDB Multi Source on production server and it hold database size of 5TB. ( Will write about it later ).
   

What is Replication Channel ?

1. The Replication Channel it represents a path of transactions flowing from master to slave.
2. The MySQL server automatically creates on default channel (Default Channel name is empty string ” “)
3. This channel is always present , it cannot be created or destroyed by the user.
4. If non empty channels are created, replication statements act on the default channel.

Multi-Source Replication.

In Multi-source replication a slave opens multiple channels, one per master, and each channel has its own relay log and applier (SQL) threads.

Once transactions are received by a replication channel’s receiver (I/O) thread, they are added to the channel’s relay log file.

A replication channel is also contains a host name and port. You can assign multiple channels to the same combination of host name and port.

For additional information Multi-Source Replication

Replication Channel Naming Conventions :

Each replication channel has a unique name and it is a string with a maximum length of 64 characters at case insensitive.

Configuring Multi-Source Replication:

In this configuration we requires at least two masters and one slave configured.

There are 2 ways to configure in multi source Replication.

1) Global transaction identifier (GTID) based replication.
2) Binary log position-based replication.

GTID Based Replication:

• Enable the GTID_mode=ON. ( log_slave_update is not mandatory for GTID in MySQL 5.7 )
• Then use Change Master To statement to add a new master to a channel by using For Channel channel_name.

For Example :

• Change master to master_host=’$host’ , master_user=’$user’ , master_port=3306 , master_password=’$password’ , master_auto_position=1 for channel ‘master-1’;

Binary log position-based replication:

• you have enabled binary logging on the master using log_bin and we need to current master_log_file and master_log_position and add the master channel by using For Channel channel_name.

For Example

• Change master to master_host=’$host’ , master_user=’user’ , master_port=3306 , master_password=” ,master_log_file=’master1-bin.000006′ ,master_log_pos=62 for channel ‘master-1’;

In this blog we discuss about binlog position based replication. Let us consider two master and one slave.

            Master-1 = labs1.mydbops.com

            Master-2 = labs2.mydbops.com

            Slave      = labs3.mydbops.com

Master-1 Setup:

Create a New user for Master-1

mysql> create user ‘multi’@’labs3.mydbops.com’ identified by ‘s3creT‘;

Give the permission for this user.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘multi’@’labs3.mydbops.com’;

Take a full backup on master-1 (labs1.mydbops.com) using mysqldump,xtrabackup etc..here I am using mysqldump.

mysqldump -u root -p –all-databases > /backup/master.sql

Then view the master log file and log position.

Master-2 Setup :

Create a New user for Master-2

mysql> create user ‘multi_user’@’labs3.mydbops.com’ identified by ‘s3cr3T’;

Give the permission for this user.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘multi_user’@’labs3.mydbops.com’;

Take a full backup on master-2 (labs2.mydbops.com) using mysqldump,xtrabackup etc..here I am using mysqldump

mysqldump -u root -p -S/tmp/mysql.sock –all-databases > /backup/master_new.sql

Then view the master logfile and log position.

Slave Setup:

In slave side multi-source replication topology require table based repositories. Multi-source replication is not compatible with file based repositories.

A slave server maximum connecting channel capacity is 256.

stop slave;

set global master_info_repository=’TABLE’;

set global relay_log_info_repository=’TABLE’;

(Better way to put this option in cnf file)

Then restart the slave ,Restore the master-1 & master-2 Bacup file’s in slave side.

Now we can create the channel’s with the name ‘master-1‘,’master-2‘.

Replication Setup on Slave

Setup channel-1 ( master-2 )

Setup channel-2 (  master-2)

Operational commands over MySQL Multi Source replication

1) The start slave thread_types statement is used to start replication
2) If you want to start a specific channel used for channel ‘ channel name’.
For Example :
start slave thread_types from channel channel name;

mysql> start slave for channel ‘master-1’;

mysql> start slave for channel ‘master-2’;

Stopping Multi-Source Replication Slaves :

• The stop slave statement can be used to stop a multi-source replication
  For Example :

mysql> stop slave thread_types;

• To stop only a named channel ( Specific channel ), use a for channel channel name

For Example :

–> stop slave thread_types from channel channel name;
mysql> stop slave for channel ‘master-1’;
mysql> stop slave for channel ‘master-2’;

Multi-Source Replication Monitoring:

• The show variables statement does not work with multiple replication channels. The information that was available through these variables has been migrated to the replication performance tables.

Monitoring the Replication Channels .

• So we can use the following statement in performance schema
  mysql> select * from replication_connection_status\G
• It will shows the channel name , source_UUID , thread_ID .
  
• It makes monitoring more flexible as we can just parse the SQL commands.

Example :

mysql> select * from performance_schema.replication_connection_status\G;

The column LAST_HEARTBEAT_TIMESTAMP can be used find the lag.