Replication in Depth

Why replicate at all#

A single MySQL server is a single point of failure and a single ceiling on read throughput — every read and every write competes for the same CPU, memory, and disk I/O. Replication copies data from one server (the primary — historically called the "master") to one or more other servers (replicas, historically "slaves"), which unlocks three things a lone server can't offer on its own: a standby that can take over if the primary dies, a place to run expensive reporting queries without slowing down live traffic, and the ability to scale reads horizontally by adding more replicas as read volume grows.

The binary log: what replication is actually built on#

Every change MySQL makes to data — inserts, updates, deletes, schema changes — is recorded in the binary log (binlog), a durable, ordered record of everything that happened on that server. Replication is fundamentally simple once you see the binlog: a replica connects to the primary, streams its binlog, and replays the same changes locally, arriving at the same data by re-executing the same history of changes.

-- On the primary: binlog must be enabled (usually on by default in modern MySQL)
SHOW VARIABLES LIKE 'log_bin';

SHOW BINARY LOGS;

SHOW MASTER STATUS;

SHOW MASTER STATUS reports the binlog file and position the primary is currently writing to — historically the exact coordinates a replica needed to start streaming from, though modern setups almost always use GTIDs (below) instead of tracking raw file/position pairs by hand.

Row-based vs statement-based replication#

MySQL can replicate in two ways: statement-based (replaying the literal SQL statement that ran on the primary) or row-based (replaying the actual resulting row changes). Statement-based replication has a real correctness hazard — a statement like UPDATE orders SET updated_at = NOW() WHERE status = 'pending' can produce a different result if replayed a few milliseconds later on the replica, since NOW() isn't guaranteed to evaluate identically. Row-based replication sidesteps this entirely by shipping the actual before/after row values rather than the statement that produced them, and it's been MySQL's default for exactly this reason.

Setting up a primary and a replica, conceptually#

The mechanics (as of MySQL 8.0.23+, which renamed the older CHANGE MASTER TO syntax):

-- On the primary: create a dedicated replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

-- On the replica: point it at the primary and start streaming
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary.internal',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'strong_password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

SHOW REPLICA STATUS\G

SOURCE_AUTO_POSITION = 1 tells the replica to use GTIDs (Global Transaction Identifiers) — a unique id assigned to every transaction, so the replica can figure out exactly where it left off and what it's still missing, without anyone manually tracking binlog file names and byte offsets. GTID-based replication is the modern default specifically because it makes failover (promoting a different replica if the primary dies) far less error-prone than coordinating raw file/position pairs by hand.

SHOW REPLICA STATUS\G is the single most important diagnostic command for a running replica — two fields matter above all others:

• Replica_IO_Running / Replica_SQL_Running — both should read Yes; either being No means replication has stopped, usually due to an error that needs investigating.
• Seconds_Behind_Source — how far behind the primary this replica currently is. 0 means fully caught up; a large or growing number means the replica can't keep up with the primary's write rate.

Replication lag, and why it matters#

By default, MySQL replication is asynchronous — the primary commits a transaction and returns success to the client immediately, without waiting for any replica to confirm it received the change. This is fast, but it means a replica can genuinely lag behind by seconds (or, under heavy load or network trouble, much longer), and a query against a replica can return data that's already stale by the time it's read.

Semi-synchronous replication is a middle ground: the primary waits for at least one replica to acknowledge receiving the transaction (not necessarily applying it) before returning success to the client — reducing, but not eliminating, the window in which a primary failure could lose a transaction no replica ever received.

Read/write splitting#

Once replicas exist, the natural next step is read/write splitting: sending every write to the primary, and spreading reads across one or more replicas. In practice this is handled either by a proxy layer sitting between the application and MySQL (ProxySQL is the most common choice) or by application/framework-level logic that picks a connection based on whether the query is a read or a write.

Application
    |
    |-- writes --> Primary
    |-- reads  --> Replica 1, Replica 2, ... (round-robin or least-lag routing)

The trade-off read/write splitting introduces directly follows from replication lag: a user who just wrote something and immediately reads it back might hit a replica that hasn't caught up yet, and briefly see stale (or missing) data — a real, user-visible bug class sometimes called "read-your-own-writes" inconsistency. The common mitigations are routing a user's own immediately-following read back to the primary for a short window, or routing specific latency-sensitive reads to the primary regardless of general read/write splitting policy.

Common mistakes#

• Treating a replica as an instantly up-to-date mirror of the primary — asynchronous replication means there's always some lag window, and an application that assumes otherwise will eventually show a user their own data as missing right after they created it.
• Never monitoring Seconds_Behind_Source, so a replica silently falling further and further behind (from a slow disk, a large unindexed query, or a spike in write volume) goes unnoticed until reads from it are badly stale.
• Using a replica purely as a backup instead of a real backup strategy — replication propagates every change, including an accidental DROP TABLE or a bad DELETE, to every replica just as faithfully as a legitimate one. A replica protects against server failure, not against application or human error; that's what the backup-and-recovery page in this section covers.
• Accidentally allowing writes directly against a replica (it isn't read_only by default in every configuration) — this can silently desynchronize it from the primary in a way that's hard to detect and even harder to safely undo.