Is it possible to do N-master => 1-slave replication with MySQL ?

Question

Hello,

I want to make a dedicated SLAVE machine for data replication of three database on three different servers. In other words, I want to do Multiple Master => SIngle Slave replication.

Is there any way to do this, as simple as it can be ?

Thanks !

Answer 1

No way that I'm aware of.

However, if the requirement here is simply to have a single replication-based backup machine, you can easily enough run three MySQL servers (on different addresses and/or ports) - we do that here, with two replication rings that each include our in-house staging server as a node.

An off-the-wall idea, if you really do want all the data into a single server and the table schemas are either fixed, or pretty much static and under your control: set up one server with the three databases on and link all the tables using the federated engine. In theory (huge caveat: I've never tried it!), you can then replicate off those federated tables on to a second server (again, possibly on the same machine), giving you genuine live copies of the data on a single MySQL instance. You could even try replicating back again, but that way possibly lies madness :)

Answer 2

Multi-master replication (a slave with more than one master) is not supported by MySQL (besides MySQL Cluster). You can do a master-master replication of a circular (ring) replication (described here or here).

In High performance MySQL 2nd edition the authors describe a way to emulate multi-master replication using a clever combination of master-master replication and the Blackhole storage engine (Chapter 8 Replication > Replication Topologies > Custom Replication Solutions > Emulating multimaster replication p. 373 - 375).

They show two possibly topologies:

Using two co-masters (allowing to switch the master of the slave from Master 1 to Master 2)

• Master 1: hosts DB1 and replicates DB2 from Master 2; the storage engine for all tables in DB2 is changed to Blackhole so that the data is not effectively stored on Master 1.
• Master 2: hosts DB2 and replicates DB1 from Master 1; the storage engine for all tables in DB1 is changed to Blackhole so that the data is not effectively stored on Master 2
• Slave 1: replicates DB1 and DB2 from either Master 1 or Master 2 (allowing to switch masters); the result is that Slave 1 replicates both databases that are effectively hosted on two different masters.

Using a master-chain

• Master 1: only hosts DB1
• Master 2: hosts DB2 and replicates DB1 from Master 1; the storage engine for all tables in DB1 is changed to Blackhole so that the data is not effectively stored on Master 2
• Slave 1: replicates DB1 and DB2 from Master 2; the result is that Slave 1 replicates both databases that are effectively hosted on two different masters.

Please note that this setup only allows you to send updates to DB1 through Master 1 and updates to DB2 to Master 2. You cannot send updates to either table to arbitrary masters.

Pehaps it's possible to combine the described solution with the hack for a true master-master replication (allowing updates to both masters) that uses some sort of autoincrement-mangling and is described here or here.

Answer 3

I do not know a lot about MySQL, but don't you have the possibility to set an 'upload only' replication configuration, where the role of the master/publisher is only to collect updates made at the slave/subscriber level.

Answer 4

Might be worth a look at maatkit's table sync -- it's not "real" replication but it might be good enough.