Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to set-up MySql Replication to have one slave listening to two different masters?

share|improve this question
add comment

4 Answers 4

up vote 2 down vote accepted

By design, one mysqld process cannot simultaneously listen to two different Masters.

The CHANGE MASTER TO command only allows you to set one Master as a source to read.

In order to emulate this, you would have to alternate between the two Masters programmatically. How do you do that ?

I described in StackOverflow how to Connect a Slave Manually to different Masters where each Master was a Laptop and the Slave was a Central Computer.

Here is the basic idea

  • Master M1
  • Master M2
  • Slave S1

Setup Replication of M1 to S1 and then M2 to S1 like this

  • 1) Have S1 run CHANGE MASTER TO with M1 as the Source
  • 2) START SLAVE;
  • 3) Run Replication for a Little While
  • 4) STOP SLAVE;
  • 5) Have S1 run CHANGE MASTER TO with M2 as the Source
  • 6) START SLAVE;
  • 7) Run Replication for a Little While
  • 8) STOP SLAVE;
  • 9) Go Back to Step 1

Each time you switch from one Master to another, you must record two values from SHOW SLAVE STATUS\G

  1. Relay_Master_Log_file
  2. Exec_Master_Log_Pos

These two values represent the last SQL Statement that came from the Master and was is next to be executed on the Slave.

There is one major caution : As long as M1 and M2 are updating mutually exclusive databases, this algorithm should be just fine.

Believe it or not, I addressed a question like this in ServerFault back in May 2011. I actually explained how to emulate true multimaster/single slave using the BLACKHOLE Storage Engine based on the book "High Performance MySQL".

share|improve this answer
    
Although I haven't really needed it myself yet, I have been thinking about this issue earlier. Wouldn't it be possible to basically pipe the binlog of the second master into the mysql slave? I guess the best thing would be to have a tool that also keeps an eye on the results of each query and stops on an error just like the normal replication-slave thread does. But in essence a simple pipe should do too. Of course both masters writing to the same database/table would become tricky quickly. Something for you gurus to ponder about? –  Jannes Feb 6 '12 at 10:35
    
I think it's worth adding to your answer that even though MySQL 5.6 doesn't do this, that 5.7 will support multiple masters. –  Phil Sumner Jul 2 at 9:04
add comment

Rolando's solution has many caveats. The first being one replica stream is necessarily not replicating while the other works. This is going to give you periods of time where your slave is out of synch. You now have to play a delicate balancing act to ensure each has enough time to catch up when it has its "turn".

As described you also have to play book keeper of log positions to switch back to. This really just seems buggy, opening the window for missing or inconsistent data or even breaking replication when it goes wrong (either being caused by even a just 'off by one' error in the log position)

I would recommend just running multiple mysql instances. There's nothing stopping you from running two or more mysql's on the same machine. They cannot both operate on the same port of course. I don't really see this as being a problem though as every client and library allows you to specify something other than 3306.

Just specify port=3307 (or whatever in one of the .cnf files).

You will also want to take care in ensuring the individually configured buffer pools and other memory configurations aren't at odds with each other. This is actually a benefit though as you can more finely tune those settings to the specific requirements of the individual databases that are being replicated.

This way you just have two replication streams running into the same server; never behind, no book keeping required, no "swapping" script required.

share|improve this answer
    
I am glad somebody understands the bookkeeping madness. Nice answer as well. +1 !!! –  RolandoMySQLDBA Feb 3 '12 at 17:20
add comment

Fan in (multi-source replication) will be supported from MySQL 5.7.

A labs release is available here: http://labs.mysql.com/

share|improve this answer
add comment

MariaDB which can be used as inplace replacement for MySql can be used. It supports it from version 10.2

The problem with Mysql 5.7 support is the need for GTID , meaning that master MUST be also changed , in case of MariaDb this is not the case.

Example/HowTo link: http://www.skysql.com/blogs/dean-ellis/multi-source-replication-mariadb-100

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.