MySQL 5.7 multi-source replication

MySQL 5.7 multi-source replication

PREVIOUS POST
NEXT POST

Recently Oracle announced several new features for the latest available development version of MySQL that is 5.7.2 at the time of writing this article. Most of them are performance and replication related that show us how incredible the new release will be.

In this post I’m going to try to explain in some easy steps how the new multi-source replication works and how we can configure it for our own tests. It is important to mention that this is a development release, so it is not production ready. Therefore this post is intend to people that want to test the new feature and see how it works with their application, always in a staging environment.

What is multi-source replication?

First, we need to have clear that multi-master and multi-source replication are not the same. Multi-Master replication is the usual circular replication where you can write on any server and data gets replicated to all others.

Multi master replication
Multi-source is different. MySQL replication had a limitation, fixed with this new release, that said that one slave could have only one master. That is a limiting factor when we are designing our replication environment. There were some “hacks” to make it work, but now there is an official way. So, in a nutshell, Multi-Source means that a slave can have more than one master. Now, replication environments like these one are possible:

Multi source replication
This will help us create some replication hierarchies that were impossible in the past. For example, you can have a slave in your office replicating the data from all servers that you have in the offices spread around the world.

How does it work?

Now we have the concept of communication channels. 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.

Pretty easy. There is one single pre-requisite. The slave should have been configured first with the crash-safe feature of MySQL 5.6. That means that info usually included in master.info or relay-log.info should be on a table. Let’s start with the configuration.

Show me an example!

First you need to download the lab version of mysql from this link.

We have a sandbox environment with 2 masters and 1 slave servers. I won’t go over the details of how to configure the server_id, binary logs or replication users. I assume they are well configured. If you need a howto, you can follow this one.

First, we have to enable the crash safe feature on the slave:

After a restart of the slave we can start creating the channels with the names “master1” and “master2”:

To start the slave processes you need to specify what channel are you referring to:

Now, we want to check the status of the slave:

Oh, it is empty. We have to specify again which channel we want to check:

and we can also check that the IO_THREAD and SQL_THREADS are running:

Let’s test it:

It works, that easy!

Conclusion

The new multi-source feature allow us to create new replication environments that were impossible in the past without some complex “hacks”. Of course, your application should be designed and developed with this new architecture in mind. Like with multi-master, multi-source needs special care to not end up with your data messed up.

MySQL replication is getting better on every release giving us more configuration, performance and design possibilities. And all those new features can be combined. Your replication environment can be even better if you mix some of the new (and old) features added recently to the replication. For example, you can configure GTID or enable multi-threaded slave per schema.

PREVIOUS POST
NEXT POST

Share this post

Comments (54)

  • Marc Castrovinci Reply

    Great feature! It looks helpful for when you want to funnel sharded data into a single location so you can run OLAP type operations over all the data.

    October 2, 2013 at 10:05 am
    • Nick Reply

      Marc? As in SOLN, Marc Castrovinci?

      January 18, 2016 at 6:08 pm
  • Jonatas Reply

    Very nice feature. Keeping eye on 5.7…

    October 2, 2013 at 10:40 am
  • shiv Reply

    Hi Miguel,
    You can use the new replication performance schema tables to look at all the channels or choose which channel you want…

    October 2, 2013 at 11:32 am
  • Miguel Angel Nieto Reply

    shiv, yes, it is possible to get that info from P_S tables:

    replication_connection_configuration
    replication_execute_configuration

    but… in my personal opinion that should be in I_S. That info is not performance related, it just show us configuration information so it should be available via SHOW (for example SHOW SLAVE STATUS CHANNEL ALL) or via I_S tables. Maybe I’m wrong, but I’ve opened a feature request anyway:

    http://bugs.mysql.com/bug.php?id=70434

    October 2, 2013 at 1:22 pm
  • Tariq Reply

    I trying to imagine the practical implementation of this new ‘multi source replication’ and how does it really help having a slave getting replicated from multiple sources when the data_set_from_every_master is the same ?

    Consider the M-M replication master1 and master2 having the dataset X and I could easily run a slave1 either from Master1 or Master2 and possibly have many other slaves from slave1 or master[1-2] and so on, why would one need to replicate from multiple masters if data is all the same unless for some arcane reason i want to replicate a particular db from master 1 and another db2 from another master which is not necessarily in the M-M replication chain .

    October 2, 2013 at 2:25 pm
  • Miguel Angel Nieto Reply

    Tariq, the data from masters don’t need to be the same. The masters on the second graph they are not in M-M replication. There are lot of examples where multi-source can be applied:

    – maybe you have hundred of apache servers, logging accesses to a mysql. in that case, having all those mysql replicating to a single slave could help you to run queries there.

    – or you have radar-controlled speed cameras all around the country and you need to have one single point to query the data.

    – or just the example made by Marc on the first comment.

    So, masters don’t need to have the same data set. Now practical implementation depends on what you need not on MySQL limitations.

    October 2, 2013 at 2:35 pm
  • James Attard Reply

    I will definitely test and blog this setup. Will make downtime-less migrations easier, without any doubt.

    October 3, 2013 at 4:13 am
  • Hugo Reply

    Nice Nice.

    This will drastically reduce the complexity on our OLAP servers.

    Does this works nice if the salve uses BlackHole Engine for all the replicated databases?

    October 3, 2013 at 7:17 am
  • Miguel Angel Nieto Reply

    Hugo, this still works like regular replication, just with more IO and SQL threads. No limitation on what storage engine you use.

    October 3, 2013 at 9:12 am
  • Terence Monteiro Reply

    Hi Miguel,

    For the multi-source replication, I would like to know if it is sufficient to upgrade the slave to 5.7.2, or do the masters also need to run the same mysql version?

    October 8, 2013 at 3:34 pm
  • Miguel Angel Nieto Reply

    Terence, didn’t have time to check it. But the theory says “it should”. It just opens more “io threads”, so for the master server is like having two different slaves. But as I said, didn’t check it. If I get some spare time I will try it.

    October 20, 2013 at 10:59 am
  • Terence Monteiro Reply

    Hi Miguel,

    Thanks for your reply. I just got multi-source replication working with MySQL 5.7.2 on my laptop with 2 vms running 5..

    October 29, 2013 at 2:33 pm
  • Terence Monteiro Reply

    I got multi-source replication running with 2 VMs with different MySQL versions (5.1.49 and 5.1.63). Thanks.

    October 29, 2013 at 2:46 pm
  • Marko Reply

    Hi all,
    I’ve just slave it in m-m with two percona’s 5.6 and it works as expected. Everything is replicated everywhere. I noticed. after 5.7.2 is restarted the replications on it can’t be started again and have to be setup again.

    November 30, 2013 at 3:48 am
  • rucypli Reply

    is percona plans to add feathure Parallel Slave like MariaDB? thanks

    December 5, 2013 at 1:49 am
  • Mike Siekkinen Reply

    During my (still labs-version 5.7.2) testing of multi source replication, it seems there is a bug where replication does not automatically pick back up after a server restart.

    Information remains in the slave_master_info table, how ever attempt to start slave for channel or show slave status for channel returns an error as if it had never been setup.

    Rerunning a full change master to command from the log positions left in slave_master_info table seemed to allow replication to resume.

    Aside from that one gotcha I ran into was trying to replicate a table with the now-reserved word “channel” for a column name. The application wasn’t encasing the column names in backticks to replication would break on a syntax error for those inserts.

    January 29, 2014 at 6:07 pm
  • Mike Siekkinen Reply

    Further testing off a real replication stream broke with an error about not being able to parse log events. This was trying to read binlogs from a percona 5.5.34 build. Between mixing percona builds with mysql labs builds and a big “TESTING ONLY” warning on the download I’ll wait for it to become more stable before pursuing further.

    January 30, 2014 at 11:38 am
  • Miguel Angel Nieto Reply

    Mike, thanks for your comments. That is what Oracle wants with Lab version, to have people breaking it like you are doing. It would be good if you could report those bugs on http://bugs.mysql.com/ That will help to make the final release even better 🙂

    January 30, 2014 at 11:44 am
  • Wayne Gaylard Reply

    I am trying to do some testing but I keep getting syntax errors when trying to do CHANGE MASTER TO …. FOR CHANNEL = ‘channel_name’. Any ideas. Using 5.7.3, tried in Workbench and on command line – same result.

    January 31, 2014 at 2:52 am
  • s capkan Reply

    I’m wondering how mysql does 5.7 handle auto_increment feature. What if i have two master without any communication and both of them has auto increment row, they can easily get same ids. Should i also use auto_increment_increment and auto_increment_offset parameters like master-master structure?.

    February 7, 2014 at 10:49 am
  • Mike Siekkinen Reply

    @wayne, you have to download the laps specific version from http://labs.mysql.com/ Select multi source replication from the drop down.

    February 7, 2014 at 12:16 pm
  • Mike Siekkinen Reply

    *labs; I wish I could edit comments here

    February 7, 2014 at 12:18 pm
  • Mike Siekkinen Reply

    @s capkan, I believe the point of multi source replication is to get the databases from server A and server B into the same instance on the slave C instance. If both A and B hold the same database/table schemas I imagine you’re going to have a bad time.

    The auto_increment_offest is meant for multi master replication so they don’t step on each other since mysql replication is not synchronous.

    February 7, 2014 at 12:37 pm
  • Alex Reply

    We have many MySQL instances that are set up with the same set of databases for each instance. I was planning to use multi-source to create a single backup point and reporting point. The problem is that all the source instances have the same databases. Are there any considerations for a prefix on the database name, or something that creates a unique database name on the replica in such a scenario?

    March 21, 2014 at 2:21 pm
  • Moody Reply

    I have the same concern as the post above me, Alex. If Master1 and Master2 both have a schema called “test”, how does this conflict with multi-source replication? I would imagine there would be 2 problems, the first one being can’t have 2 schemas with the same name. Second, how does the slave know which schema to apply the slave event?

    April 10, 2014 at 11:51 am
  • alex Reply

    have you checked if mysql 5.7.4 support this feature? when i issue this statement “change master to master_host=”127.0.0.1″, master_port=12047, master_user=”msandbox”,master_password=”msandbox” for channel=”master1″; , mysql reports an syntax error near ‘ for channel’. i havent found any document for ‘for channel’ clause. could you please give me some help ? thanks .

    June 8, 2014 at 2:06 am
  • Alex Reply

    @alex: It is not in the release builds yet, you have to download the multi-source replication version from http://labs.mysql.com/

    June 25, 2014 at 3:18 pm
  • Lilian Reply

    Hello,

    Could you help me with the installation of MySQL 5.7.2, because I am unable to install on my Slave

    September 16, 2014 at 11:14 am
  • Tom Diederich Reply

    Hi Lilian,

    I suggest asking this on our MySQL discussion forums: https://www.percona.com/forums/

    September 16, 2014 at 8:59 pm
  • Kiran Reply

    Hi All,

    Does this gives HA facilit. For e.g., If we assume M1,M2,M3 are three multisource masters and S1 is the Slave server.if one among the Master server fails. Let us say M2 failed. Can we failover to Slave S1 and is it possible to continuously write to new Master?

    Any real time example or Use case for this to get a clear idea ?

    Best Regards,
    Kiran.M.K.

    October 7, 2014 at 3:14 am
    • Shruti Reply

      Did you get an answer for this?

      October 15, 2016 at 1:25 pm
  • Kiran Reply

    Is the GTID mandatory in Multi source Replication ?

    Best Regards,
    Kiran.M.K.

    October 7, 2014 at 3:15 am
  • Andrew Griffin Reply

    Is this a reasonable solution for a fallback/backup server for disparate master instances? We have two different instances running completely unrelated data in unique DBs. I’m looking to put together a single fallback source for maintenance/failures and I’m still not perfectly sure this is a good option, though it’s really attractive. should I just go with 1:1 instances instead?

    November 6, 2014 at 1:25 pm
  • Vasu Reply

    It has been more than a year since the original announcement, and multi-source replication is still available only in the lab version not intended for production use. Is there any way to find out if and when this feature will be available in a production release of MySQL?

    January 6, 2015 at 10:13 am
  • dirham Reply

    can you show me step by step create 2 master for one slave ?

    server A = Master
    server B = Master
    Server C = slave

    cen you help me ?

    January 9, 2015 at 6:34 am
  • breddy Reply

    How does this multi-source replication configuration works, if all masters (masters only) are in circular replication setup and slave try to pull data from all masters?

    February 24, 2015 at 1:21 pm
  • Adi webster Reply

    Hi,

    In mysql 5.7 multiple source replication, do masters also need to run on same mysql version?
    For clarification. I have 3 master having mysql version 5.6 and slave with 5.7 will multiple master and single slave replication will work?

    June 6, 2015 at 3:36 am
    • Nick Reply

      did you find out about this? I have the same setup.

      January 18, 2016 at 2:42 pm
  • Gabriel Wang Reply

    Hi,
    I’m building a multi-source replication on mysql 5.7.7-rc. The scenario in my side is there are multiple mysql instances, all of them has the same table structure, but with different data, all I need to do is to merge all the data into 1 replica mysql instance.
    Therefore I planned to add 1 column in replica database tables named master-name, next step is to write a “before update/insert” trigger to update the replica table when every time insert/update come. BUT, I found it’s not easy to identify the source of the insert/update.
    Some findings from my side:
    1. I can find replication channel information from table “performance_schema.replication_connection_configuration”.
    2. Also there is process information on information_schema.PROCESSLIST.

    Which mysql master instance does the insert/update comes from?
    Thanks in advance!

    June 24, 2015 at 3:58 am
  • Jürgen Friedrich Reply

    @Dirham follow https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql and http://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-tutorials.html helped me a lot

    actually, just add “FOR CHANNEL ‘master-XX’ for each Master on slave and it will work!

    June 24, 2015 at 8:30 am
  • Marieke Janssen Reply

    it this feature still only in the labs version? I’ve installed 5.7.7-rc and get syntax error

    July 14, 2015 at 4:51 pm
  • Nick Reply

    Before setting up a test environment, figured I’d ask before wasting the time. Anyone know if having a mysql master running 5.6 and having slaves at version 5.7 would work? Or would we need to upgrade the masters to 5.7 also?

    January 18, 2016 at 2:39 pm
    • Nick Reply

      Guess I can answer my own question. Yes it works using 5.6 master and 5.7 slaves making use of the CHANNELs

      January 18, 2016 at 6:08 pm
  • Dhanaekaran Reply

    Hi Guys,

    Have tried MySQL5.7.10, syntax changed little bit.

    It’s works for me.

    >change master to master_host=”172.16.60.8″,master_port=3306, master_log_file = ‘bin.000011’, master_log_pos= 403, master_user=”replicator”, master_password=”pass” for channel “master1”;

    January 27, 2016 at 9:25 am
  • Agastya Reply

    nice article.. I am using Xampp and I successfully replicated only for one master and one slave…I want to replicate multiple master and one slave..can you explain how to do this in Xampp?.Thanks

    March 18, 2016 at 1:45 am
  • Jason Becker Reply

    I don’t know if this changed from the time this article was written or what but with Percona-Server-server-57-5.7.11-4.1.el6.x86_64 it is not:

    … for channel=’master1′;

    It is:

    … for channel ‘master1’;

    See the MySQL docs: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

    But even the MySQL docs omit the quotes around the channel name.

    Hope this helps someone.

    April 5, 2016 at 11:51 am
  • Ruichao Lin Reply

    how to skip one transaction in one channel ?

    May 30, 2016 at 1:50 am
    • Dominik Klein Reply

      # stop all slaves
      stop slave;

      # set skip counter
      set global sql_slave_skip_counter=1;

      # start slave that shall skip one entry
      start slave for channel “$channel”;

      # start all other slaves
      start slave;

      July 12, 2016 at 7:30 am
  • Mahesh Patil Reply

    Hello Miguel,

    I am not sure whether multi source replication in Percona server v5.7.13 supports replicate-rewrite-db option for multiple channels. Similarly as explained here – https://mariadb.com/blog/multisource-replication-how-resolve-schema-name-conflicts

    Can you please share your thoughts on this. Any blog which can give more insights on this would be helpful.

    August 17, 2016 at 8:14 am
  • Nick Reply

    Fix or delete the link at the end of the article. intra-database it links to malware.

    August 31, 2017 at 4:19 pm
    • Dave Avery Reply

      Nick, thanks for your comment. We have deleted the link.

      August 31, 2017 at 4:26 pm

Leave a Reply