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

Question

I want to create a specialized SLAVE machine to replicate data from three databases on three different servers. In other words, I want to execute Multiple Master => SIngle Slave.

Is there a way to do this as much as possible?

Thanks!

Answer 1

Multiple master replication (multi-master slave) is not supported by MySQL (other than MySQL Cluster). You can perform master-master replication of circular (ring) replication (described here or here ).

In High-Performance MySQL 2nd edition, the authors describe a method for emulating replication with multiple masters using a smart combination of master replication replication and Blackhole storage engine (Chapter 8 Replication> Replication Topologies> Selective Replication Solutions> Multimaster Replication Emulation p. 373 - 375 ).

They show two possible topologies:

Using two comasters (allowing you to switch the master from Master 1 to Master 2 )

• Master 1: hosts DB1 and replicates DB2 with Master 2 ; The storage mechanism for all tables in DB2 has been changed to Blackhole so that data is not stored efficiently on Wizard 1 .
• Wizard 2: hosts DB2 and replicates DB1 from Wizard 1 ; The storage mechanism for all tables in DB1 has been changed to Blackhole , so that data is not effectively stored in Master 2
• Slave 1: replicates DB1 and DB2 from Master 1 or Master 2 (allows you 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: DB1 hosts only
• Wizard 2: hosts DB2 and replicates DB1 from Wizard 1 ; The storage mechanism for all tables in DB1 has been changed to Blackhole , so that data is not effectively stored in Master 2
• Slave 1: replicates DB1 and DB2 with Master 2 ; the result is that Slave 1 replicates both databases that are effectively hosted on two different masters.

Note that this setting only allows updates to be sent to DB1 through Wizard 1 and to upgrade DB2 to Wizard 2 . You cannot send updates to any table to arbitrary masters.

Allows you to combine the described solution with a hack for genuine replication of the master (allowing updates for both masters), which uses some kind of auto-increment mechanism and is described here or here .

Answer 2

I don’t know what I know.

However, if you just need to have one backup machine based on replication here, you can easily start three MySQL servers (at different addresses and / or ports) - we do this here with two replication rings, which each turn on our intermediate server as a node.

The idea is “off the wall” if you really want all the data to be on the same server, and the table schemas are either fixed or rather static and under your control: set up one server with three databases and link all the tables using the combined engine. In theory (a huge caution: I have never tried it!), You can then replicate these joined tables to a second server (again, possibly on the same computer), giving you real live copies of the data in one MySQL example. You can even try to play back, but maybe this is crazy :)

Answer 3

I don't know much about MySQL, but you don’t have the ability to set up a download-only replication configuration, where the role of the master / publisher is only to collect updates made at the subordinate / subscriber level,

Answer 4

Maybe you should take a look at the maatkit table sync - this is not "real" replication, but it can be quite good.