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.

Answer 3

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

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

Answer 4

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

Answer 5

Replication with One slave and Two master: Yes! we can do it by creating channels in slaves. Below are the steps to do it using GTID

Following are the steps

masters

• 192.168.0.201:3307
• 192.168.0.201:3308

slave

• 192.168.0.209:3306

On Master

1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
2. Create a replication user on the master: CREATE USER 'replication_user'@'192.168.0.209' IDENTIFIED WITH mysql_native_password BY 'password';
3. Grant replication privileges to the user: GRANT REPLICATION SLAVE ON . TO 'replication_user'@'192.168.0.209'; FLUSH PRIVILEGES;
4. Show the master status: SHOW MASTER STATUS\G;

On Slave

1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
2. Stop and reset the slave on the slave server:

STOP REPLICA FOR CHANNEL "DEVELOPMENT"; 
RESET REPLICA FOR CHANNEL "DEVELOPMENT";

3. Add GTID on the slave for channel DEVELOPMENT: SET GLOBAL gtid_purged='ccb0d141-dc54-11ee-923c-0242c0a8b005:1-33'; <!--- Should be same as master 192.168.0.201:3307 --->

4. Change the master settings on the slave for channel DEVELOPMENT: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3307, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "DEVELOPMENT";

5. Start the slave for the specified channel: START REPLICA FOR CHANNEL "DEVELOPMENT";

6. Show the slave status for the specified channel: SHOW REPLICA STATUS FOR CHANNEL "DEVELOPMENT"\G;

7. Add GTID on the slave for channel PRODUCTION: SET GLOBAL gtid_purged='180c0583-e1e0-11ee-84fa-0242c0a84003:1-107'; <!--- Should be same as master 192.168.0.201:3308 --->

8. Change the master settings on the slave for channel PRODUCTION: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3308, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "PRODUCTION";

9. Start the slave for the specified channel: START REPLICA FOR CHANNEL "PRODUCTION";

10. Show the slave status for the specified channel: ````SHOW REPLICA STATUS FOR CHANNEL "PRODUCTION"\G;```

Reference: https://www.svastikkka.com/2024/03/mysql-multi-source-master-slave.html