How does MySQL replication work

· Category: SQL & Databases

Short answer

MySQL replication copies data from a source (master) server to one or more replica (slave) servers using the binary log.

How it works

The source writes changes to its binary log. Replicas connect to the source, read the log, and apply the events to their own data. Replication can be asynchronous, semi-synchronous, or group-based.

Steps

  1. Enable binary logging on the source.
  2. Create a replication user with REPLICATION SLAVE privileges.
  3. Take a snapshot of the source data and load it onto the replica.
  4. Configure the replica with CHANGE MASTER TO pointing to the source.
  5. Start the replica threads and monitor Seconds_Behind_Master.

Tips

  • Use replicas for read scaling and backups to reduce source load.
  • Monitor replication lag and network health to prevent stale reads.

Common issues

  • Statement-based replication can produce different results for nondeterministic functions like NOW().
  • Large transactions cause significant replication lag.r