One slave, multiple masters MySql

Question

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

Answer 1

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".

Answer 2

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.