The idea: ship the binlog, apply it again

MySQL's built-in replication is asynchronous by default. The source writes every committed transaction to its binary log (binlog). Each replica connects, receives the binlog events as they are written, stores them in its own relay log, and applies them. Each step is done by a separate thread, and each transaction carries a global name, its GTID, so both sides can tell exactly what the replica has.

On the source, sessions commit into binlog.000003 as GTID event groups 3E11:6, 3E11:7, and a binlog dump thread streams them to the replica. On the replica, the receiver (IO) thread writes them to the relay log, the coordinator hands them to workers W1 to W4, and gtid_executed records what is applied.
Five threads in a row: session → binlog → dump thread → receiver → relay log → coordinator → workers.

The page runs one source and one replica. Pick a transaction and press Commit; every commit is one second of wall clock, and the replica works during that second. Wait 5 s lets time pass, Disconnect drops the network, and the Options row switches the binlog format, the number of applier workers and semi-sync.

Binlog events and GTIDs

A transaction reaches the binlog only at COMMIT. MySQL commits in two phases: InnoDB prepares the transaction, the server writes it to the binlog (and, with sync_binlog = 1, fsyncs it), then InnoDB commits. In the binlog a transaction is one event group: a Gtid event, then the changes, then the commit (Xid) event.

A GTID is server_uuid:N, for example 3e11fa47-71ca-11e1-9e33-c80aa9429562:23: the UUID of the server where the transaction first committed and a counter on that server. The page shortens the UUID to 3E11. Every server keeps gtid_executed, the set of GTIDs it has committed, written as ranges such as 3E11:1-9. A server never applies a GTID that is already in its set, so the same transaction can't run twice.

The Gtid event also carries two numbers used by the parallel applier: last_committed and sequence_number (Demo: one transaction, end to end).

Dump thread and receiver thread

For each connected replica the source runs a binlog dump thread. It reads the binlog and sends new events as soon as they are written. On the replica the receiver thread (called the IO thread before 8.0.26) writes them unchanged to the relay log and adds their GTIDs to Retrieved_Gtid_Set. Receiving and applying are split on purpose: the replica copies events quickly even when applying them is slow, so a source crash loses less.

Asynchronous means the source does not wait: the client gets OK as soon as the source has committed, before the dump thread has sent anything. If the source dies at that moment, a transaction the client saw committed may never reach the replica.

The applier: coordinator and workers

The applier (the SQL thread) reads the relay log. With replica_parallel_workers = 4 (the default since 8.0.27) it is a coordinator plus four worker threads. The coordinator reads transactions in order and decides which may run at the same time, using the two numbers in each Gtid event (replica_parallel_type = LOGICAL_CLOCK):

  • sequence_number: the transaction's position in the binlog file (1, 2, 3, …).
  • last_committed: the sequence number of the newest transaction it may depend on. With commit-order tracking that is the last transaction already committed when this one prepared.

A transaction may start once every transaction with a sequence number up to its last_committed has committed on the replica. Transactions that committed in one binlog group commit share a last_committed: they were running at the same time on the source, so they didn't block each other, and the replica runs them in parallel. Transactions that waited for the same row lock commit one after another; each one's last_committed is the one before it, so the replica applies them in turn (Demo: a commit group in parallel, dependent ones in turn).

Left: the binlog with 3E11:6, 7 and 8 all last_committed 5, then 3E11:9, 10 and 11 with last_committed 8, 9 and 10. Right: a time axis where W1, W2 and W3 apply 6, 7 and 8 in the same second, then W1 applies 9, 10 and 11 one second each.
Same last_committed: apply together. A chain of last_committed: apply in turn.

replica_preserve_commit_order = ON (the default since 8.0.27) makes workers commit in the source's order: a worker that finishes early waits for the ones before it. So the replica never shows a state the source never had, and gtid_executed has no gaps.

The page uses commit-order tracking, the 8.0 default (binlog_transaction_dependency_tracking = COMMIT_ORDER). Since 8.4 the source always uses WRITESET tracking: it also compares the rows each transaction wrote, so non-conflicting transactions get an older last_committed even when they were not committed in the same group, and the replica can run more of them in parallel.

Reconnecting with auto-positioning

Without GTIDs, a replica must remember a binlog file name and byte offset on the source (SOURCE_LOG_FILE, SOURCE_LOG_POS), which mean nothing on another server. With SOURCE_AUTO_POSITION = 1 the receiver sends its GTID set when it connects (COM_BINLOG_DUMP_GTID). The source subtracts it from its own gtid_executed, finds the first binlog file that holds a missing transaction, and sends exactly what is missing (Demo: reconnect with GTID auto-positioning). The same works after a failover: point the replica at a new source and it asks for what it lacks.

It fails in two cases. If the source has already purged binlogs that hold missing GTIDs, the receiver stops with error 1236. If the replica has a GTID the source doesn't (an errant transaction, for example a write made directly on the replica), that transaction would be sent to other replicas after a failover.

Row-based vs statement-based

binlog_format = ROW (default)STATEMENT
LogsEach changed row: before image (to find the row) and after imageThe SQL text, plus context such as the timestamp
The replicaWrites the same valuesRuns the statement again
SizeBig for an UPDATE of many rowsOne line however many rows
Non-deterministic SQLSafeCan drift: UUID(), SYSDATE(), LIMIT without ORDER BY

Some functions are handled in statement mode: NOW() is replicated correctly because the binlog stores the statement's timestamp, and RAND() gets its seed logged. UUID() can't be: the replica generates its own value, and from then on its data differs from the source with no error at all (Demo: STATEMENT format drifts with UUID()). MySQL logs "Unsafe statement written to the binary log", and MIXED switches such statements to row format.

STATEMENT: the binlog holds UPDATE accounts SET token = UUID() WHERE id = 1; the source has token 6ccd780c and the replica, running it again, gets f3a91b2e, marked as differs. ROW: the binlog holds row id 1 with before token NULL and after token 6ccd780c; both servers end with 6ccd780c, marked same.
Statement-based replays the SQL and can get a different answer; row-based ships the answer.

Replication lag

SHOW REPLICA STATUS reports Seconds_Behind_Source: the replica's clock minus the time, on the source, of the event it is applying. It is 0 when the relay log is applied, and NULL when the receiver is not running and nothing is left to apply. Events are stamped with the time their statement started, so a transaction that ran 20 s on the source shows 20 s of lag the moment the replica begins it, and the lag keeps growing while it applies. Everything committed behind it waits too, because it depends on the big one (Demo: a big transaction builds lag). Split big writes into small batches. Because the number depends on the clocks of both servers, many people measure lag with a heartbeat row instead (pt-heartbeat), or with the timestamps in performance_schema.replication_applier_status_by_worker.

Semi-synchronous replication

The semi-sync plugin makes a commit wait until at least rpl_semi_sync_source_wait_for_replica_count (default 1) replicas confirm they have written the events to their relay log. With rpl_semi_sync_source_wait_point = AFTER_SYNC (the default) the source waits after the binlog sync and before InnoDB commits, so no other session can see the transaction before a replica has it. If no ACK comes within rpl_semi_sync_source_timeout (10000 ms), the source falls back to asynchronous replication and switches back once a replica catches up (Demo: semi-sync AFTER_SYNC waits for the ACK). The replica has received the transaction but not necessarily applied it. See Primary–Replica Replication for sync modes in general.

Looking at it

-- on the source
SHOW BINARY LOG STATUS;              -- current file, position, Executed_Gtid_Set
SHOW BINLOG EVENTS IN 'binlog.000003';
mysqlbinlog -vv binlog.000003        -- shows last_committed / sequence_number

-- on the replica
CHANGE REPLICATION SOURCE TO SOURCE_HOST='src', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G                -- Replica_IO_Running, Retrieved_Gtid_Set,
                                     -- Executed_Gtid_Set, Seconds_Behind_Source
SELECT @@GLOBAL.gtid_executed;

See also How MySQL Runs a Query for the two-phase commit between InnoDB and the binlog, MySQL Binlog Group Commit for how commit groups form, MySQL Group Replication for the consensus-based alternative, and PostgreSQL Logical Decoding for the PostgreSQL counterpart.

What the page leaves out

Real binlog events include Table_map, Query BEGIN and Xid events, binlog files rotate at max_binlog_size, and sequence_number restarts in each file. The page shows one replica, three rows, whole-second timing and applies each normal transaction in exactly one second. It skips multi-source replication and replication channels, replication filters, delayed replicas (SOURCE_DELAY), crash-safe relay log recovery (relay_log_recovery), MIXED format, binlog compression and encryption, the WRITESET history size, worker queue limits, the ACK receiver thread of semi-sync and failover tools that promote a replica.