MySQL Replication, in depth: binlog formats, GTIDs, async versus semi-sync, parallel appliers, lag you can trust and safe failover

The replication path

Everything starts with the binary log (binlog) on the source: an ordered, append-only record of committed changes, written as part of commit. MySQL coordinates the binlog and InnoDB with an internal two-phase commit so that a transaction is either in both or in neither after a crash, provided both are flushed durably (sync_binlog=1 and innodb_flush_log_at_trx_commit=1). The internals of that redo and commit path are covered in InnoDB in depth.

Each connected replica gets a dump thread on the source that reads the binlog and streams events. On the replica, the receiver thread (historically the IO thread) writes those events to the relay log on local disk, and the applier (historically the SQL thread) reads the relay log and executes the changes. In MySQL 8.4 the applier is multi-threaded by default: a coordinator hands transactions to workers when it can prove they do not conflict.

Replication is therefore logical: the replica replays changes rather than copying pages. And there are two separate lags, receiver behind source (network) and applier behind receiver (apply speed), with different causes and fixes.

Binlog formats: row, statement and mixed

The binlog can record changes in three formats. Statement-based logging records the SQL text; the replica re-executes it. It is compact, but any non-determinism, such as UUID(), NOW() in some contexts, LIMIT without ORDER BY, or a trigger reading a different row, makes the replica diverge silently. Row-based logging records the before and after images of each changed row, which is deterministic and is what most tooling, including change data capture, expects. Mixed uses statements and switches to rows for statements MySQL knows are unsafe.

Row format is the default, and the binlog_format variable itself has been deprecated since MySQL 8.0.34, with row-based logging intended to be the only format in a future release. Treat statement format as legacy. The cost of row format is size: a single UPDATE touching a million rows produces a million row images. binlog_row_image defaults to full (all columns); minimal logs only the changed columns and the identifying key, which shrinks the log but breaks consumers that need full rows, so check your CDC pipeline first.

Row format has one sharp edge: tables without a primary key. To apply a row event, the replica must find the row; with no primary or unique key it may scan the table for every row event, and a large delete can stall the applier for hours. Give every replicated table a primary key. sql_require_primary_key enforces this for new tables, and generated invisible primary keys (sql_generate_invisible_primary_key) add one automatically when an application cannot.

Positions versus GTIDs

Classic replication tracks progress as a binlog file name and byte offset on the source. It works until the source fails, because the offsets are meaningless on any other server: to repoint a replica at a new source you must work out which position on the new source matches where the replica stopped, which is error prone under pressure.

A GTID gives every transaction a global name, written as source_uuid:transaction_id, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 for the 23rd transaction committed on that server. MySQL 8.4 also supports tagged GTIDs of the form source_uuid:tag:transaction_id, useful for marking a group of transactions such as a migration. Each server keeps gtid_executed, the set of all transactions it has applied, and gtid_purged, the subset no longer present in its binlogs. With SOURCE_AUTO_POSITION = 1 a replica simply sends its executed set and the source streams everything missing. Repointing becomes a one-line change, and comparing servers becomes set arithmetic.

Turning GTIDs on for an existing topology is done online in steps, so no server ever receives a transaction it cannot handle: set enforce_gtid_consistency to WARN and fix the warnings (statements that cannot be logged safely with GTIDs), then to ON; then move gtid_mode through OFF_PERMISSIVE and ON_PERMISSIVE on every server, wait until no anonymous transactions remain, and finally set ON everywhere. Follow the manual's procedure exactly; skipping a step on one server breaks replication from it.

Durability: asynchronous, semi-synchronous and group replication

With default asynchronous replication the source commits and acknowledges the client without waiting for any replica. If the source dies, any transactions that were committed but not yet received by a replica are lost on failover. The window is usually milliseconds, but it is unbounded under network trouble.

Semi-synchronous replication makes the commit wait until at least one replica confirms it has written the transaction to its relay log. It is a plugin on both sides, named rpl_semi_sync_source and rpl_semi_sync_replica in 8.4, and must be enabled on both or replication stays asynchronous. Three settings define its behaviour:

• rpl_semi_sync_source_wait_point: the default AFTER_SYNC waits for the acknowledgement after the binlog is synced but before the InnoDB commit, so no other session can see a transaction that a replica has not received. AFTER_COMMIT commits first and then waits, so other clients can read data that would vanish in a failover. Keep the default.
• rpl_semi_sync_source_wait_for_replica_count: acknowledgements required, default 1.
• rpl_semi_sync_source_timeout: how long to wait, default 10,000 ms. On timeout the source falls back to asynchronous replication and returns to semi-sync when a replica catches up. That fallback is the important caveat: semi-sync bounds loss only while it is active, so alert on the status variable that reports whether it is on.

Group Replication, the basis of InnoDB Cluster, goes further: members agree on transaction order through a consensus protocol, a transaction commits only once a majority accepts it, and a new primary is elected automatically. It costs commit latency and needs a majority to stay writable; it is a different operational model, not a setting.

Parallel apply and why replicas fall behind

A source executes many transactions concurrently; a single-threaded applier replays them one at a time, which is the classic cause of replica lag on write-heavy systems. MySQL 8.4 defaults to replica_parallel_workers = 4 and replica_preserve_commit_order = ON, with logical-clock scheduling. The source records dependency information in the binlog based on the rows each transaction wrote (its write set), and the coordinator runs transactions in parallel when their write sets do not overlap, while committing them in source order so readers never see an order the source never had.

Parallelism cannot fix everything. A single huge transaction, such as a delete of fifty million rows, is applied by one worker and blocks commit order behind it. DDL on a large table runs on the replica after it finished on the source, so a ten-minute ALTER adds ten minutes of lag. Tables without primary keys make each row event slow. Hot rows updated by every transaction create dependencies that serialize apply. The fixes are on the write side: chunk large deletes and backfills into small transactions, use online schema change tools or instant DDL where available, and add primary keys.

Setting up a GTID replica

# source my.cnf (8.4)
[mysqld]
server_id                  = 1
log_bin                    = binlog
gtid_mode                  = ON
enforce_gtid_consistency   = ON
sync_binlog                = 1
innodb_flush_log_at_trx_commit = 1
binlog_expire_logs_seconds = 604800     # keep 7 days; size this to your rebuild time

# replica my.cnf
[mysqld]
server_id                  = 2
log_bin                    = binlog      # so it can be promoted and serve replicas
gtid_mode                  = ON
enforce_gtid_consistency   = ON
super_read_only            = ON          # nobody writes here except replication
relay_log_recovery         = ON          # crash-safe relay log handling

-- on the source
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';

-- on the replica, after provisioning it from a consistent copy
-- (for example the clone plugin or a physical backup)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db-source.internal',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '...',
  SOURCE_SSL = 1,
  SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G

The privilege is still spelled REPLICATION SLAVE in 8.4. Provision replicas from a consistent physical copy, which carries gtid_purged correctly, and keep binlogs on the source longer than a rebuild takes, or a lagging replica will request purged transactions and stop with error 1236.

Measuring lag honestly

Seconds_Behind_Source in SHOW REPLICA STATUS is the number everyone watches and the one that misleads most. It is NULL when a replication thread is stopped, reads 0 when the applier has caught up with a receiver starved by a slow link, and jumps when a long transaction starts. Use it as a hint.

Two better signals exist. A heartbeat table: a job on the source writes the current timestamp to a row every second, and the lag is the replica's clock minus the value it can see, which measures end-to-end staleness as readers experience it. And GTID set comparison: GTID_SUBTRACT(source_executed, replica_executed) lists exactly which transactions the replica lacks. The Performance Schema tables replication_connection_status and replication_applier_status_by_worker break this down into receiver and per-worker state, including the last error each worker hit.

For applications that need read-your-writes on replicas, capture the GTID of the write (with session_track_gtids) and have the read path call WAIT_FOR_EXECUTED_GTID_SET(gtid_set, timeout) on the replica, falling back to the source on timeout.

Worked example: a GTID failover

A source S has three asynchronous replicas R1, R2 and R3, all with GTIDs and auto-positioning. S's host dies. The steps below are what an orchestrator should do, and what you should be able to do by hand.

1. Fence the old source. Make sure S cannot come back and accept writes: stop it, remove it from the load balancer or revoke its network path. Split brain is worse than downtime.
2. Pick the most advanced replica. Compare each replica's received set (RECEIVED_TRANSACTION_SET in replication_connection_status). Suppose R2 received up to uuidS:1-10450, R1 to 10447 and R3 to 10431. R2 is the candidate.
3. Let it finish applying. On R2, wait until its executed set contains everything it received, using WAIT_FOR_EXECUTED_GTID_SET with a timeout.
4. Check for errant transactions. On each other replica, compute GTID_SUBTRACT(replica_executed, R2_executed). A non-empty result with a UUID other than S's means someone wrote directly to that replica; promoting R2 and repointing that replica would either fail or make the errant transaction the only copy. Resolve it before continuing.
5. Promote. On R2: STOP REPLICA; RESET REPLICA ALL; then turn off super_read_only and read_only, and move the writer endpoint to it.
6. Repoint the rest. On R1 and R3: CHANGE REPLICATION SOURCE TO SOURCE_HOST='R2', SOURCE_AUTO_POSITION=1; START REPLICA;. GTID auto-positioning sends R1 the three transactions it lacks and R3 the nineteen.

What was lost? Anything S committed after 10450 that no replica received, a number that stays unknown with asynchronous replication. With semi-sync in AFTER_SYNC mode still active at the crash, every acknowledged transaction reached at least one relay log, so promoting that replica loses none of them.

Failure modes

• Writes on a replica: drift and errant GTIDs; prevent with super_read_only=ON on every non-primary.
• Error 1236 after purge: the replica needs transactions the source no longer has; rebuild from a fresh copy and lengthen binlog retention.
• Silent semi-sync fallback: a slow replica triggers the timeout and the cluster runs asynchronously for days; alert on semi-sync status.
• Duplicate key or missing row errors: the replica diverged, usually from statement format, a direct write or a skipped event; never skip events blindly, find the cause and re-sync if needed.
• Lag from one huge transaction: commit-order preservation stalls all workers behind it; chunk large writes.
• Unsafe crash recovery on a replica: without relay_log_recovery a replica crash can leave a corrupt relay log; enable it.
• Failover without fencing: the old source returns and accepts writes, and two diverged histories now exist.

Trade-offs

What to do next

1. Run SHOW REPLICA STATUS and the Performance Schema replication tables on every replica, and record format, GTID mode, workers and semi-sync state.
2. Enable GTIDs with auto-positioning if you still use file positions, following the manual's online procedure.
3. Set super_read_only and relay_log_recovery on every replica, and give every replicated table a primary key.
4. Add a heartbeat-based lag metric and alert on it instead of Seconds_Behind_Source alone.
5. Decide your loss budget; if it is zero for acknowledged writes, enable semi-sync with AFTER_SYNC and alert on fallback.
6. Write and rehearse the failover runbook above, including fencing and the errant-transaction check, in staging.
7. Compare MySQL's model with PostgreSQL's if you run both, starting with PostgreSQL logical replication and MySQL versus PostgreSQL.