What is MySQL Replication?
MySQL replication allows data from one MySQL server (the master) to be replicated to one or more MySQL servers (slaves). In addition to offering high availability and geographic redundancy, MySQL Replication is used to offload backups and analytic workloads.
Replication Scheme
MySQL Replication currently supports two replication schemes:
- Asynchronous replication
- Semi-synchronous replication
Replication schemes may be mixed within the same topology without restriction. There are advantages and disadvantages to both. MySQL replication does not currently offer a fully-synchronous solution at the time of writing.
Asynchronous Replication
Replication in MySQL is by default asynchronous. This is the oldest, most popular, and most widely deployed replication scheme. The master writes events to its binary log and the slaves request them when they are ready using asynchronous replication. It is not guaranteed that any event will reach any slave. It is a loosely coupled master-slave relationship in which:
- Master does not wait for Slave.
- A slave determines how much data to read and from which point in the binary log to read it.
- The slave can be arbitrarily delayed in reading or applying changes by the master.
In the event that the master crashes, transactions that it has committed may not have been transmitted to slaves. In this situation, failover from master to slave may result in a server that is missing transactions in relation to the master.
Since asynchronous replication acknowledges a write locally before it is written to slaves, asynchronous replication results in lower write latency. Adding more replicas does not affect replication latency, so it is ideal for read scaling. The deployment of read replicas for read scaling, live backup copies for disaster recovery, and analytics/reporting are all good use cases for asynchronous replication.
Semi-Synchronous Replication
MySQL also supports semi-synchronous replication, in which the master does not confirm transactions to the client until at least one slave has copied the changes to its relay log and flushed them to disk. Plugins must be installed on the designated MySQL master and slave in order to enable semi-synchronous replication.
In many cases where high availability and no data loss are important, semi-synchronous processing seems to be a good and practical solution. Due to the additional round trip, semi-synchronous has a performance impact and does not provide strong guarantees against data loss. The data exists in at least two places (on the master and at least one slave) when a commit is successful. It is possible that the transaction may not have reached any slave if the master commits but a crash occurs while the master is awaiting acknowledgement from a slave.
As the commit will not be returned to the application, this is not a significant issue. The application is responsible for retrying the transaction in the future. When a master has failed and a slave has been promoted, the old master cannot join the replication chain. Occasionally, this can result in conflicts with data on the slaves (when the master crashes after the slave receives the binary log event but before the slave acknowledges the event).
In this case, the only safe course of action is to discard the data on the old master and provision it from scratch using the data from the newly promoted master.
As a backup master, semi-synchronous replication can reduce the impact of a master failure by minimizing the risk of data loss.
