MySQL Master-Slave Replication: Async, Semi-Sync, and Replication Lag

MySQL replication works by shipping the binlog from master to slave. The master has one binary log of every data change; a slave has an IO thread that opens a replication connection to the master, pulls binlog events, and writes them into the slave's relay log. A second slave thread, the SQL thread, reads the relay log and replays the events against the slave's copy of the data. Three binlog formats exist. STATEMENT logs the SQL text — compact but breaks with non-deterministic functions: NOW(), UUID(), RAND(), or LIMIT without ORDER BY can produce different results on the slave. ROW logs the actual rows changed — larger binlog but deterministic. MIXED picks per-statement. Modern production uses ROW (often with binlog_row_image = MINIMAL to reduce size). Default replication is asynchronous — master commits locally, writes to binlog, returns success to the client, and the slave catches up later. This means a master crash can lose the tail of the binlog that slaves haven't received. Semi-synchronous replication changes the contract — master waits for at least one slave to ACK that it has received (not executed) the binlog before responding to the client. It trades 1-2 ms of latency for zero data loss on single-node failure. Group Replication (MGR) uses a Paxos-based protocol for multi-primary or single-primary clusters with automatic failover and conflict detection — heavier but much stronger. Replication lag is the operational pain point. Monitor it with Seconds_Behind_Master (rough) or pt-heartbeat (precise). Common causes: a long transaction on master holds the binlog so slave SQL thread stalls; a DDL statement that blocks the SQL thread; the single-threaded SQL thread is CPU-bound on hot updates. MySQL 5.7+ supports parallel replication by schema or by logical clock groups — enabling slave_parallel_workers > 0 is usually a 3-5x lag improvement. For read-your-own-writes consistency, the standard fix is to route critical reads back to master for a short window after a write, or use GTID-based session wait — MASTER_POS_WAIT / WAIT_FOR_EXECUTED_GTID_SET — to block until the slave has caught up past the write.