Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
Is it possible to set-up MySql Replication to have one slave listening to two different masters?
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
Fan in (multi-source replication) will be supported from MySQL 5.7.
A labs release is available here: http://labs.mysql.com/
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
Setup Replication of M1 to S1 and then M2 to S1 like this
Each time you switch from one Master to another, you must record two values from SHOW SLAVE STATUS\G
SHOW SLAVE STATUS\G
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".
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?
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.
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.
I am glad somebody understands the bookkeeping madness. Nice answer as well. +1 !!!