Topology for MySQL Replication

Master with Slaves (Single Replication)

This is the most straightforward MySQL replication topology. One master receives writes, while one or more slaves replicate from the same master using asynchronous or semi-synchronous replication. In the event that the designated master is unavailable, the most recent slave must be promoted to become the new master. From the new master, the remaining slaves resume replication.

Master with Relay Slaves (Chain Replication)

The setup uses an intermediate master as a relay to communicate with the other slaves. If there are many slaves connected to a master, the network interface of the master may become overloaded. With this topology, the read replicas are able to pull the replication stream from the relay server in order to offload the master server. Binary logging and log_slave_updates must be enabled on the slave relay server, whereby updates received from the master server are logged in the slave’s binary log.

There are several disadvantages to using slave relays:

• There is a performance penalty associated with log_slave_updates.
• A replication lag on the slave relay server will result in a delay on all of its slaves.
• All slaves of the slave relay server will be infected by rogue transactions.
• In the event that a slave relay server fails and you are not using GTID, all of its slaves will cease to replicate and will need to be re-initialized.

Master with Active Master (Circular Replication)

A ring topology, also known as a multi-master configuration, requires two or more MySQL servers to function. There are a few caveats to receiving writes and generating binlogs on all masters:

• The auto-increment offset must be set on each server in order to prevent primary key collisions.
• There is no conflict resolution process.
• There is no locking protocol supported by MySQL Replication at the present time to ensure the atomicity of a distributed update across two servers.
• There is a common practice of only writing to one master and using the other master as a hot-standby. In the event that the designated master fails, you must manually switch to the new master if you have slaves below that tier.

This topology can be deployed with ClusterControl 1.4 and later. ClusterControl would previously raise an alarm when two or more masters were running simultaneously. One of the masters will be configured as read-only, while the other will be configured as writable. It is, however, the responsibility of the application to handle locking and conflict resolution. A replication setup involving two writable masters is not supported by ClusterControl. One of those two masters must be read-only.

Master with Backup Master (Multiple Replication)

Changes are pushed from the master to a backup master and to one or more slaves. The replication between the master and backup master is semi-synchronous. The master sends an update to the backup master and waits for the transaction to be committed. The backup master receives an update, writes it to its relay log, and flushes it to disk. The backup master acknowledges receipt of the transaction to the master and proceeds with the transaction commit. There is a performance impact associated with semi-sync replication, but the risk of data loss is minimized.

The topology works well when performing master failover in the event that the master becomes unavailable. Compared to other slaves, the backup master acts as a warm standby server since it is most likely to have current data.

Multiple Masters to Single Slave (Multi-Source Replication)

Multi-Source Replication allows a replication slave to receive transactions from multiple sources at the same time. Multiple-source replication can be used to backup multiple servers to a single server, to merge table shards, and to consolidate data from multiple servers.

In MySQL and MariaDB, multi-source replication is implemented differently, with MariaDB requiring GTID and gtid-domain-id to distinguish originating transactions, while MySQL uses a separate replication channel for each master. The masters in a multi-source replication topology can be configured to use either global transaction identifier (GTID) or binary log position replication.

Galera with Replication Slave (Hybrid Replication)

Hybrid replication combines MySQL asynchronous replication with virtually synchronous replication provided by Galera. As a result of the implementation of GTID in MySQL replication, the process for setting up and performing master failover has been simplified on the slave side.

The performance of the Galera cluster is as fast as the slowest node. Asynchronous replication slaves minimize the impact on the cluster if you send long-running reporting/OLAP queries to the slave, or if you perform heavy jobs that require locks, such as mysqldump. In addition to serving as a live backup, the slave can also be used for disaster recovery both onsite and offsite.