Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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 !

like image 618
Amadeus45 Avatar asked Oct 16 '09 07:10

Amadeus45


People also ask

Does MySQL support master master replication?

MySQL Master Master replication is a development of master-slave replication that addresses its major flaws. This method of replication requires two or more master nodes that can handle both read and write requests. Additionally, each of your masters can have several slave nodes.

Does MySQL support multi master replication?

Multi source replication is supported as of MariaDB 10.0 and MySQL 5.7 . Basically this means that a replica is allowed to replicate from multiple masters. To enable this, the replica should not have multiple masters writing to the same schema as this would lead to conflicts in the write set.

How does master-slave replication work in MySQL?

The MySQL replication feature allows a server - the master - to send all changes to another server - the slave - and the slave tries to apply all changes to keep up-to-date with the master.


1 Answers

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.

like image 109
Stefan Gehrig Avatar answered Nov 12 '22 04:11

Stefan Gehrig