MySQL – Replication

Replication keeps a second MySQL server continuously up to date with a first. The primary writes its changes to the binary log; the replica reads that log and applies them.

It buys you read capacity, a warm standby, and somewhere to run reports without touching production. It is not a backup, for a reason worth being precise about.

Setting up a pair

Both servers need a unique server_id and the primary needs binary logging on. GTID mode makes everything afterwards easier, so turn it on from the start:

# primary: my.cnf
server_id                = 1
log_bin                  = binlog
gtid_mode                = ON
enforce_gtid_consistency = ON

# replica: my.cnf
server_id                = 2
gtid_mode                = ON
enforce_gtid_consistency = ON
read_only                = ON
super_read_only          = ON

read_only stops ordinary accounts writing to the replica; super_read_only stops privileged ones too. Set both — read_only alone still lets anyone with SUPER write, and a stray write on a replica breaks replication in a way that is tedious to unpick.

On the primary, an account for the replica to connect as:

-- unavailable: run on the PRIMARY of a replication pair. Creates a server-level account.
CREATE USER 'repl'@'%' IDENTIFIED BY 'a-strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Take a consistent snapshot and note where it came from:

mysqldump -u root --all-databases --single-transaction \
          --source-data=2 --triggers --routines --events > snapshot.sql

mysql -h replica -u root < snapshot.sql

--source-data=2 (called --master-data before 8.0.26) records the position — or, with GTIDs, the executed GTID set — as a comment in the dump. That is the point the replica must start from, and getting it wrong means silently missing or duplicating transactions.

Then point the replica at the primary and start:

-- unavailable: run on the REPLICA. This checker's server is a standalone instance.
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary.internal',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'a-strong-password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

SOURCE_AUTO_POSITION = 1 is what GTIDs buy you: the replica works out for itself which transactions it is missing, rather than you supplying a file name and byte offset. That is the difference between a routine failover and an afternoon of arithmetic.

(MySQL 8.0.22 renamed this whole family of commands — CHANGE MASTER TO became CHANGE REPLICATION SOURCE TO, SHOW SLAVE STATUS became SHOW REPLICA STATUS, and so on. Older documentation uses the old names.)

Checking it

-- unavailable: run on the REPLICA. A standalone server reports ERROR 1200.
SHOW REPLICA STATUS\G

Four fields tell you almost everything:

Monitor Seconds_Behind_Source and alert on it. Note it measures how far behind the applied transaction is, not network latency — and it reads 0 both when the replica is caught up and when it has nothing to do, so alert on the IO/SQL threads being No as well.

Lag is the thing that bites the application

Replication is asynchronous by default: the primary commits and tells the client "done" without waiting for any replica. So there is always a window where the replica does not have your write.

The bug that produces is specific and common. A customer places an order — written to the primary — and the confirmation page reads from a replica that has not received it yet. The order appears to have vanished. Under load, when lag is largest, this happens most.

Three real answers, in order of preference:

1. Read your own writes from the primary. After a write, route that user's reads to the primary for a few seconds. Simple and effective.
2. Route by query type deliberately rather than by default — reports, exports and analytics to replicas; anything a user just changed to the primary.
3. Semi-synchronous replication, where the primary waits for at least one replica to acknowledge receipt before returning. It shrinks the window at the cost of write latency, and it does not eliminate it — the replica acknowledges receipt, not application.

Read/write splitting

Splitting reads across replicas is where the capacity comes from, and it is a decision to make per query rather than globally. Spring supports an AbstractRoutingDataSource for this; ProxySQL does it outside the application; managed databases give you a reader endpoint.

Whatever the mechanism, the rule holds: a read that must reflect a write the same user just made goes to the primary.

When it breaks

-- unavailable: run on the REPLICA. A standalone server reports ERROR 1200.
SHOW REPLICA STATUS\G          -- read Last_Error first

STOP REPLICA;
START REPLICA;                 -- after fixing the cause

The usual causes are a write that happened directly on the replica, a schema change applied in one place only, or a statement that is not deterministic under STATEMENT binlog format — which is the argument for ROW.

You will find advice to skip the offending transaction. Understand what that means: the replica is now permanently missing a change, so its data differs from the primary's in a way nothing will report. Sometimes it is the pragmatic choice; treat it as accepting divergence, verify with pt-table-checksum, and rebuild the replica when you can.

⚠️ A replica is not a backup

This is the part to take seriously. Replication copies everything, promptly — including your mistake. DROP TABLE customer_order on the primary is on the replica a moment later. So a replica protects you against hardware failure and not at all against human error, which is the more common cause of data loss.

Backups protect against human error precisely because they are a point in the past. You need both: replicas for availability, backups plus binlogs for recovery.

A delayed replica is a useful middle ground — CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600 keeps one an hour behind, giving you an hour to notice a destructive statement before it arrives there.

What to remember

• Unique server_id, binlog on, GTIDs on, and both read-only flags on the replica.
• SOURCE_AUTO_POSITION = 1 — let GTIDs find the position for you.
• Watch the IO and SQL threads and Seconds_Behind_Source.
• Asynchronous means a read-after-write window. Route those reads to the primary.
• A replica is not a backup. It replicates your mistakes faithfully.