Replication keeps a second server carrying the same data, applying the same changes shortly after the first. It is useful for three things and is regularly deployed for a fourth that it does not provide.

What it is good for

  • Read capacity - reports and search go to the replica, writes to the primary.
  • Backups without load - dump from the replica and the live site notices nothing.
  • Failover - a warm copy ready to be promoted, which turns a rebuild into a switch.

What it is not

A replica is not a backup. DROP TABLE orders replicates in under a second and now both copies are missing the table. Backups protect against mistakes; replicas protect against a machine dying. You need both - see backups as a security control.

The lag nobody plans for

Replication is asynchronous by default: the primary does not wait. A write followed immediately by a read from the replica can return the old value - a customer saves a profile and it appears unchanged.

mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Seconds_Behind|Replica_(IO|SQL)_Running'

The rule that avoids it: read from the replica only where slightly stale data is acceptable. Anything the user just wrote is read from the primary.

Setting one up, in outline

  1. Enable binary logging on the primary — log_bin, a unique server_id, and gtid_mode = ON.
  2. Create a replication user — with REPLICATION SLAVE only.
  3. Seed the replica from a consistent dump — mysqldump --single-transaction --source-data=2
  4. Point it at the primary and start it — then watch the status until it catches up.

Watch it after that

A replica that has stopped is silent. It keeps answering reads with data that is now days old, which is worse than being down. Alert on both the running flags and on the lag.