The Difference Between MySQL Multi-Master and Multi-Source Replication

MySQL replication can have various topologies and the most well known topology is the Master/Slave topology, where one host is the master and the slave replicates all transactions from the master. Lesser known are Multi-Master and Multi-Source replication. Even though they sound similar they are actually quite different. This blog post will illustrate their differences.

Multi Master

Multi Master is similar to the Master/Slave topology, with the difference that both nodes are both master and replica at the same time. This means there will be circular replication between the nodes. It is advisable to configure both servers to log the transactions from the replication thread (log-slave-updates), but ignore its own already replicated transactions (set replicate-same-server-id to 0) to prevent infinite loops in the replication. This needs to be configured even with GTID enabled.

Multi master topologies can be configured to have either a so called active/passive setup, where only one node is writable and the other node is a hot standby. Then there is the active/active setup, where both nodes are writable.

Caution is needed with active/active as both masters are writing at the same time and this could lead to conflicts, if the same dataset is being written at the same time on both nodes. Generally this is handled on application level where the application is connecting to different schemas on the two hosts to prevent conflicts. Also as two nodes are writing data and replicating data at the same time they are limited in write capacity and the replication stream could become a bottleneck.

Multi source 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.

Multi source replication is currently not widely supported by replication tools. In general these tools use the output from SHOW SLAVE STATUS to determine the replication state of the replicas. With multi source replication, there are several replication channels and thereby multiple replication streams. Therefore it is not advised to use multi source replication in combination with a replication manager unless you have tested this thoroughly. The alternative is to make the slave an unmanaged replica.

Why would you need multi source replication? Multi source replication may be suitable for data warehousing needs, delayed slaves or data locality in some cases. Especially if multiple database clusters are not utilizing their full write capacity, it may save a few hosts by consolidating multiple slaves into one node.

This blog post is a condensed version of the replication topology chapter in our MySQL Replication Blueprint whitepaper. Download the whitepaper and learn more about MySQL replication end-to-end.