What is database replication?

MySQL has a feature known as replication where data from one database server (referred to as the source or primary) is copied to other database servers (replicas). Conceptually, it’s pretty straight forward. All of your data mutations (INSERT, UPDATE, DELETE, ALTER, etc.) are done against the primary database. Those commands are then copied and applied to the replicas.

Why is database replication useful?

There are a number of reasons replication like this may be desirable.

How does database replication work in MySQL?

The replication feature in MySQL is achieved with a handful of threads and a few resources.



The fetching and storing of binary log events into the relay log is separated from applying the events. This helps in preventing slow to apply queries from holding up the syncing of the binary log to the replica. It also allows for a local copy of events the applier thread can resume from if the replica was shut down then started up later.

On the primary database, when a write transaction is completed or the commit is done, the binary log is updated and flushed to disk. There is also a mutex around the binary log that allows only the session holding it to read or write to it. You can see the contention around this mutex by checking the performance schema tables for wait events on the following resources:

A wait is simply an event that takes time. These events are formed as a hierarchy divided by the / separator. It should be read from left to right, general to specific (the naming convention is described in more detail on the MySQL docs here). Remember how the binary log and relay log are different instances of the same class, MYSQL_BIN_LOG? That’s the resource that is being shown on the far right of these events! Any of these events mean that there is time being spent (either on the replica or the primary depending what server you query the performance metrics from) where a thread is waiting to access the binary/relay log.