Multi-source replication for MySQL has been released as a part of 5.7.5-labs-preview
downloadable from labs.mysql.com. It is one among the several features that are
cooking in the replication technologies at MySQL.  (For a birds eye view of all
replication features introduced in 5.7 and labs, look  at the blog posts here and here.

Previously, we have introduced a preliminary multi-source feature labs preview. Based on the feed back from that labs release, we have released a feature preview based on 5.7.5 DMR which:

1.  has a better user interface;
2. is well tested;
3. and has a better integration with other features such as Multi-threaded slave (MTS), Global Transaction ID(GTID) and replication performance_schema tables.

In this blog, I give you a description of what and how of a multi-source replication.

Motivation

Multi-source replication makes a replication slave connected to several sources (aka masters) simultaneously and aggregates the data from these sources. This setup has use cases like, having to backup of several servers to a single place or merging of tableshards or making any replication topology possible. Note that, multi-source replication doesn’t do any conflict detection and resolution and those tasks are left to the user’s application. If you are looking for a multi-master update everywhere with conflict detection and automatic resolution, look at the post here.

Replication Channel 

In my previous blog post, the concept of replication channel has been introduced. To recap, a replication channel encompasses the path of binary log events from master to the IO thread to the Relay log files to the applier threads (SQL thread or coordinator & worker threads). Multi-sourced slave creates a replication channel for each master. Each channel has a unique name and configuration parameters can be specified per channel (described below).

Preconditions for setting up multi-source replication

Replication repositories are either stored in FILE or TABLE based repository. TABLE based repository is crash-safe and multi-source replication is usable only in the case of TABLE based repositories. To setup multi-source replication, start mysqld with
—master-info-repostiory=TABLE && –relay-log-info-repository=TABLE.
(A better way, is to put these options in the .cnf files). If you are using a single source replication using FILE repository and want to setup a multi-source replication, convert repositories to TABLE dynamically in the following way:
STOP SLAVE;
SET GLOBAL @@master_info_repository = TABLE;
SET GLOBAL @@relay_log_info_repository = TABLE;

Commands for operations on a single channel

To make replication commands act per channel, we have introduced a new clause
called FOR CHANNEL “<channel_name>” to the replication commands so that
a user can manage a channel independent of other channels.
Following commands are channel specific:

• CHANGE MASTER… FOR CHANNEL “<channel_name>”
  – Creates a channel if it doesn’t exist.
• START SLAVE … FOR CHANNEL “<channel_name>”
• STOP SLAVE… FOR CHANNEL “<channel_name>”
• SHOW RELAYLOG EVENTS …FOR CHANNEL “<channel_name>”
• FLUSH RELAY LOGS FOR CHANNEL “<channel_name>”
• SHOW SLAVE STATUS FOR CHANNEL “<channel_name>”
• RESET SLAVE [ALL] FOR CHANNEL “<channel_name>”

There is an extra parameter introduced for the following functions.

• MASTER_POS_WAIT(binlog_file, binlog_pos, time_out, channel_name);
•  WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set, time_out, channel_name);

Compatibility with single source replication

The server automatically creates a channel whose name is the empty string (“”). We call this the default channel. Replication commands with no FOR CHANNEL clause act on this channel if there is no named channel configured (A named channel is one that has a name different than “”).
This means a default channel exists the user can still use the same commands to interact with replication. As such, there is no need in that case to use the FOR CHANNEL clause at all, since the behavior will match the one before multi-source replication. This means that with a single channel configured, we get backwards compatibility command-wise.

Commands for operations on all channels

Since a slave could have several replication channels, and if FOR CHANNEL clause is not provided, then replication commands shall act on all the channels wherever it is semantically valid. (Note that this behavior is different from previous labs release).
For example:

• START SLAVE [IO_THREAD|SQL_THREAD] starts replication threads for all the  channels.
• STOP SLAVE [IO_THREAD/SQL_THREAD] stops replication threads for all the      channels.
• SHOW SLAVE STATUS reports the status for all the channels.
• RESET SLAVE [ALL] resets the slave for for all channels.
  – To preserve backward compatibiliy though, server purges all the relay log files
           of named channels but initialize them again for only default channel.
  – The ALL option would delete the internal memory structures and thereby all the
           information and configurations assosiated with a particular channel.
• FLUSH RELAY LOGS flushes the relay logs of all channels.

However, not all the commands make sense to do for all channels. The following
commands generate error 1964 (“Multiple channels exist on the slave. Please provide channel name as an argument.”) when a channel is not provided and if number of channels are greater than 1. (Note that, a default channel always exists).

• SHOW RELAYLOG EVENTS;
• CHANGE MASTER..;
• MASTER_POS_WAIT();
• WAIT_FOR_SQL_THREAD_AFTER_GTIDS();

Multi-source monitoring

To monitor the status of all slave channels, a user has two options:

1. performance_schema tables.: 5.7.2 introduced replication performance_schema tables to monitor replication status. Multi-source replication extended these tables to add Channel_Name as the first column to these tables. This will enable the users to write complex queries based on Channel_Name as a key.
2. SHOW SLAVE STATUS [FOR CHANNEL “<channel_name>”]: This command by default shows the slave status for all channels with one row per channel. The identifier channel_name is added as the column in the result set. If a FOR CHANNEL clause is provided, the user will be provided the status of only that replication channel.

(NOTE: Replication SHOW VARIABLES: SHOW VARIABLES does not work with multi-source
replication. The information that was available through these variables have
been migrated to the replication performance tables. For more information, look at
Shiv’s post  here. The current SHOW VARIABLES will show status of only the default channel.)

User session and server log error messages

Since, a multi-sourced slave can have multiple channels, we introduced explicit
error messages pertaining to a channel. To make this more consistent across all
channels, we introduced new error codes and messages.
For example: The notes that used to be emitted in previous versions of mysql
 “Slave is already running” or “Slave is already stopped”  is replaced with
  “Replication thread(s) for channel “channe_name” are already running”  and
  “Replication threads(s) for channel “channel_name” are already stopped” respectively.
We also changed server log messages to indicate the channel on which the note/warning/error was generated. This makes debugging and/or tracing easier.

Integration with other features

This feature has been well integrated with Global Transaction Identifiers and
Multi-Threaded Slave. Currently, replication filters are global and applies
to all channels and cannot be set per channel. Semi-sync replication only works when
all the masters and slave is semi-sync enabled. We are looking at further extending
the Multi-source framework for better support of these features.

Conclusion

Multi-source replication has been released as part of 5.7.5-labs-preview. Please try out this
new labs release which can be downloaded  at labs.mysql.com and tell us if you need
some things more to make this feature even better.