Replication exists because every database server goes down eventually: a disk fails, a kernel panics, a data center loses power, or the server simply needs patching. With a single server, the application is down until it comes back, and if the disk is gone, the data exists only in backups until someone restores them. Replication keeps the same data on more than one server, so that when one goes down another takes over and the application carries on, ideally noticing nothing more than a few failed queries and a reconnect. That is high availability, and it comes in two broad shapes: a cluster whose members agree on every write before it counts, or one server that sends its changes to others that can be promoted when it fails. MySQL has both. This post starts with the second, the classic source and replica setup that most MySQL installations run, adds semi-synchronous replication to it, and ends with Group Replication, the basis of InnoDB Cluster, which is the first kind.
Here is how it works, in outline. One server, the source (older MySQL versions called it the master), takes every write and records each change in a log; the others, the replicas, fetch that log and replay it against their own copy. When the source fails, a replica is promoted to take its place, and tools such as Orchestrator automate that part: they watch the source, pick a replica, promote it and point the application at it. Because the replicas hold the data anyway, they can also answer reads, while writes still go to one server.
The weak spot is “ideally noticing nothing”. The replicas are always a little behind: a row read from a replica the moment its insert committed was missing all 6,000 times I tried it, even with both servers on the same machine, and arrived about 2 milliseconds later. How far behind depends on settings this post goes through one at a time, and what that gap costs when the source dies is Part 4.
The binary log: what a commit leaves behind
A transaction is a group of statements the database treats as one unit, and COMMIT promises that its changes survive a crash. MySQL keeps that promise with two logs. InnoDB, the storage engine, writes each change to its redo log, which it replays after a crash. The server itself writes the whole transaction to the binary log, a file of every change in commit order, and that is the log replication ships. At the defaults (innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1) both are flushed to disk before COMMIT returns. Durable means durable on this server’s disk, though: if the machine is gone for good, so is every commit that existed only there, and copying commits elsewhere before anyone needs them is the job replication does.
Here is what one small transaction looks like in the binary log, decoded with mysqlbinlog -vv --base64-output=DECODE-ROWS (MySQL 8.4.13, output trimmed):
| |
The statement was UPDATE orders SET status = 'shipped' WHERE id = 1, and the statement is not what got logged. With binlog_format = ROW, the default, the log holds the row as it was and as it became, every column of both, because binlog_row_image = FULL is also the default. A replica applying this event does not run the WHERE; it finds the row by its values (by its primary key, when the table has one; Part 2 measures what happens when it does not) and overwrites it. Three more fields matter later: the GTID, which names this transaction everywhere it goes, and last_committed and sequence_number, which tell a replica which transactions it may apply at the same time.
The format decides how much gets shipped. One UPDATE that changed every row of a 1,000,000-row table wrote this much to the binary log, and the replica showed the change this long after COMMIT returned:
| Setting | Binary log written | Replica showed it after (median of 3) |
|---|---|---|
ROW, binlog_row_image = FULL (default) | 142.6 MB | 3.7 s |
ROW, binlog_row_image = MINIMAL | 10.0 MB | 3.1 s |
ROW, FULL, binlog_transaction_compression = ON | 3.4 MB | 5.2 s |
STATEMENT | 307 bytes | 2.9 s |
MySQL 8.4.13, one source and one replica on the same host, 1 CPU each. Replica times varied by up to 2 s between runs (ranges: 3.4–5.2, 3.0–4.2, 3.5–8.1, 2.7–3.0 s).
STATEMENT ships the SQL text and the replica runs it again, which is tiny on the wire and only as correct as the statement is deterministic; MySQL warns about statements it considers unsafe (LIMIT without ORDER BY, UUID()), and MIXED switches those to row events. MINIMAL logs only the primary key in the before image and only the changed columns in the after image, which is most of the size of FULL saved, at the price of a log that no longer shows what a row looked like before a bad update. Compression cut the log by a factor of 41 here and cost CPU on both ends, which on one core each showed up as slower and noisier apply. For network-bound replicas in another region it is often a good trade; on the same rack it mostly is not.
The binary log is also a disk-space setting. Files rotate at max_binlog_size (1 GB) and are deleted after binlog_expire_logs_seconds, 2,592,000 seconds or 30 days by default. A replica that is stopped for longer than that, or a source that purged early to free disk, leaves the replica asking for history that no longer exists, which is one of the breakages in Part 3.
Two threads, a relay log, and the settings around them
The tests in this series ran in Docker on one laptop. Each server got 1 CPU and 900 MB of memory, a source had two replicas with GTIDs on, the Group Replication tests used a group of three, and everything ran at MySQL 8.4.13 defaults except where a test says otherwise. On the same host a round trip between two servers took 0.1 ms. To stand in for servers further apart I added network delay with tc netem; the Docker VM could not add less than about 3 ms in each direction, so the tables quote the round trip I measured (22 ms), not the delay I asked for.
A replica runs two kinds of threads. The I/O thread (renamed the receiver in 8.0.26) connects to the source, asks for binary log events from where it left off, and writes them to a local file, the relay log. The SQL thread (now the applier) reads the relay log and applies each transaction as ordinary row changes. Since 8.0.27 the applier is a coordinator that hands transactions to parallel workers, four by default (replica_parallel_workers = 4), and replica_preserve_commit_order = ON makes them commit in the source’s order even when they run side by side, so a reader on the replica never sees transaction 10 without transaction 9.
Which transactions may run side by side is what last_committed and sequence_number in the binary log encode. MySQL 8.4 decides it by writeset: the source hashes the keys each transaction touched, and two transactions that touched no common row may be applied in parallel even if they committed one after the other from the same session. Before 8.4 the default was commit order, where only transactions that committed in the same group on the source could overlap, and the standard tuning was binlog_group_commit_sync_delay on the source: wait a few hundred microseconds before each binary log flush so more transactions share it, giving replicas wider groups to parallelize. 8.4 removed the choice (binlog_transaction_dependency_tracking is gone; writesets are always used), and in my runs the delay no longer helped the replica apply faster. It slowed the source instead, which shrank the backlog because the writes arrived more slowly; Part 2 has those numbers, and they cut both ways.
The two halves run independently, so a replica can have received a transaction and not applied it, and most of what follows lives in that gap. SHOW REPLICA STATUS shows both sides:
| |
The replicas here ran with read_only on, which is meant to keep anyone from writing to them. It stopped the application’s user and let root straight through:
The replicas here ran with read_only on, which is meant to keep anyone from writing to them. It stopped the application’s user and let root straight through:
| |
read_only exempts accounts with administrative privileges, and super_read_only is the setting that covers them too. That matters more on MySQL than on a byte-for-byte copy, because a replica applying row changes is a separate server with its own tables: handy for an extra reporting index or a rolling upgrade, and able to drift from the source after a write it should never have taken.
The time between a commit on the source and the moment a replica’s queries can see it is called replication lag. Here is what it did to a page that reads a row right after writing it. One connection inserted a row on the source and, as soon as COMMIT returned, a second connection looked for it on a replica:
| Round trip between servers | Reads that missed the new row | Row visible on the replica after (median / p99) |
|---|---|---|
| Same host (0.1 ms) | 6,000 of 6,000 | 2.4 ms / 5.8 ms |
| 22 ms | 1,000 of 1,000 | 13.6 ms / 31.1 ms |
Same host: three runs of 2,000; p99 is the median of the three runs’ p99.
A couple of milliseconds of lag is invisible to a person and certain to a program, whose next query arrives sooner than that. The usual fix is to send a session’s reads to the source for a short while after it writes (read-your-writes routing).
That gap grows with the shape of the writes: many small commits, transactions too large to start applying before they end, tables without a primary key, schema changes, and whatever else the replica is busy with. Part 2 measures each of those.
DELETE without a WHERE or a dropped table is replayed as faithfully as an insert, which in the tests above took about 2 ms. Replicas protect against losing a machine; protecting against a bad statement takes backups you can restore to a point in time, which the backups post covers.GTIDs: how a replica knows where it is
Before GTIDs, a replica tracked its place as a file name and a byte offset in the source’s binary log, binlog.000042 at position 1,987,123. That works until the source changes: the new source’s files have different names and offsets, and working out where each remaining replica should resume was a manual, error-prone step in every failover.
A GTID, a global transaction identifier, names each transaction instead. It is the UUID of the server where the transaction first committed plus a sequence number, 534f6c00-e228-11f1-9e8a-66177ab163d8:9 in the event above, and it travels with the transaction to every server that applies it. Each server keeps the set of GTIDs it has executed, written as ranges:
| |
With auto-positioning, a replica connects with its executed set, and the source sends every transaction not in it, from whatever files hold them:
| |
That is what makes a failover tractable. Pointing the remaining replicas at a newly promoted source is the same statement with a different host, and choosing which replica to promote is a set comparison: GTID_SUBSET(a, b) and GTID_SUBTRACT(a, b) tell which server has more. gtid_purged is the part of the executed set whose binary logs have already been deleted; a replica that needs anything in it cannot be served and has to be rebuilt.
Two caveats. gtid_mode is OFF by default even in 8.4 (enforce_gtid_consistency likewise), so a server set up from defaults is still on file and position until someone turns it on, which can be done online in steps. And a set comparison is only as good as the sets: a transaction committed directly on a replica gets the replica’s own UUID, and that errant GTID is how a stray write turns into a failed failover months later, which is Part 3.
Semi-synchronous: waiting for one replica to receive it
The lag above comes from a choice about when COMMIT returns. In asynchronous replication, the default, the source acknowledges a commit as soon as its own disk has it, and replicas fetch the change afterwards. Semi-synchronous replication makes the commit wait until at least one replica says it has the transaction. It ships as a pair of plugins, installed and enabled on both sides:
| |
“Has the transaction” means received, not applied: “The replica acknowledges receipt of a transaction’s events only after the events have been written to its relay log and flushed to disk” (manual). With the default wait point, AFTER_SYNC, the source waits for that acknowledgement after writing the transaction to its binary log and before committing it in InnoDB.
So semi-sync protects a committed transaction against losing the source, for as long as a replica is there to acknowledge it, and does nothing for reads. The tests show the read side plainly: with semi-sync on the same host, a read on the replica right after the commit missed the new row 2,000 times out of 2,000, because the replica had it in its relay log and had not applied it yet. At a 22 ms round trip it missed 22 of 1,000, only because waiting for the round trip gave the applier time to catch up.
The protection also switches itself off. If no replica acknowledges within rpl_semi_sync_source_timeout, which is 10 seconds by default, “the source reverts to asynchronous replication” (manual), and it returns to semi-sync on its own once a replica catches up. With the only semi-sync replica down, a commit stalls for up to ten seconds and then writes carry on with no second copy, which is the exact window in which losing the source loses data. Nothing fails and no client sees an error. The sign is Rpl_semi_sync_source_status reading 0 and Rpl_semi_sync_source_no_tx climbing. I did not run this case; it is the documented behaviour. A setup that needs the guarantee to hold sets the timeout high enough that the source blocks instead, and accepts that losing the replica then stops writes.
What the wait costs depends on distance. On the same host it did not register: a commit took 1.6 ms either way. At a 22 ms round trip, every commit paid the round trip:
| Setting, 22 ms round trip | Commit time (median / p99) | Replica reads that missed the new row | Commits per second, 16 sessions |
|---|---|---|---|
| Async | 1.6 ms / 12.6 ms | 1,000 of 1,000 | 2,220 to 3,010 |
| Semi-sync | 27.2 ms / 34.2 ms | 22 of 1,000 | about 600 |
Commits per second: three 10-second runs of 16 sessions each inserting one row per transaction.
A session that commits one row at a time can do about 37 commits a second at 27 ms each, however fast the server is, so with 16 sessions, throughput here fell from 2,220 to 3,010 commits a second to about 600, and only more sessions in parallel win it back. That is why semi-synchronous replicas usually sit in the same region, often in a different availability zone a millisecond or two away, while replicas in other regions stay asynchronous. And semi-sync’s promise has an expiry date, 10 seconds by default, after which it quietly reverts to asynchronous replication; Part 4 measures what that costs in a failover.
Synchronous: Group Replication, and whether it can lag
Group Replication replaces the source and its replicas with a group of servers, three in these tests, that agree on every write. In the default single-primary mode one member takes writes and the others are secondaries. When a transaction is ready to commit, the primary broadcasts the rows it changed to the group, and “read/write transactions commit only after they have been approved by the group” (manual): a majority of members, two of three here, agree on the transaction’s place in the order and check it for conflicts with other transactions (certification). Only then does COMMIT return. A member cut off from the majority cannot commit anything. That is the sense in which it is synchronous, and it is why it is usually read as “every member has the change when COMMIT returns”.
It does not mean that. The group agrees on the order of transactions, not on having applied them. Each secondary queues the agreed transaction in its relay log and applies it afterwards with its own applier threads, the same way an asynchronous replica does, and the manual says so plainly: “Group Replication is an eventual consistency system, meaning that as soon as the incoming traffic slows down or stops, all group members have the same data content.”
So yes, synchronous replication can lag. At the 8.4 default, group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER (which changes only what a newly elected primary does and otherwise behaves like EVENTUAL), a read on a secondary right after the commit missed the new row 4,042 times out of 6,000 on the same host, and 953 of 1,000 at a 22 ms round trip. Under a 20-second burst from 16 sessions, which left an asynchronous replica 44,300 transactions behind (Part 2 follows that run second by second), a secondary fell 19,192 transactions behind and caught up 26 seconds after the writes stopped. Flow control, which slows the primary down once a member’s queue passes group_replication_flow_control_applier_threshold (25,000 transactions by default), never stepped in.
The setting that closes the gap is group_replication_consistency = AFTER, under which “any subsequent transaction reads the written value or a more recent value on any group member” (manual): the commit waits until every member has applied the change. With it, 0 of 6,000 reads missed. It is also the costliest setting measured here, because each commit now waits for the slowest member to apply, not only for the network:
| Setting | Commit, same host (median) | Commit, 22 ms round trip (median / p99) | Replica reads that missed the new row | Write burst, commits per second |
|---|---|---|---|---|
| Async | 1.6 ms | 1.6 ms / 12.6 ms | 6,000 of 6,000 | 2,909 |
| Semi-sync | 1.6 ms | 27.2 ms / 34.2 ms | 2,000 of 2,000 (same host) | not measured |
| Group Replication, default | 2.1 to 2.5 ms | 51.5 ms / 68.8 ms | 4,042 of 6,000 | 1,695 |
Group Replication, AFTER | 2.3 to 2.6 ms | 76.2 ms / 102.5 ms | 0 of 6,000 | 502 |
Same host unless noted. Write burst: 16 sessions inserting for 20 seconds, same host.
At a 22 ms round trip, Group Replication’s default commit took about twice as long as semi-sync’s, close to two round trips, and still left 95% of immediate reads on a secondary stale. AFTER also exists per session, so the transactions that must be visible everywhere can pay for it while the rest do not, and BEFORE puts the wait on the reading side instead.
What to watch
Replicas, semi-synchronous or not, and Group Replication secondaries all add read capacity and no write capacity, because every one of them applies every write. The PostgreSQL replication post measured that on PostgreSQL (three servers, 3.2 times the reads, every write on the primary); my MySQL run of the same comparison was limited by its single load-generating container, so its figure is left out. When the writes outgrow one server, the next step is splitting the rows across several sources, called sharding, which gets a post of its own.
For lag, Seconds_Behind_Source is accurate about the age of what the applier is working on and says nothing about how long the backlog will take, which is the number a failover or a read-routing decision needs. Comparing Retrieved_Gtid_Set with Executed_Gtid_Set shows what has arrived and not been applied, and performance_schema.replication_applier_status_by_worker reports each applier’s last applied transaction with its original commit time on the source. On a semi-synchronous source, Rpl_semi_sync_source_status is the one to page on, since it is the only sign that the setting has quietly turned itself off. In Group Replication, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE in performance_schema.replication_group_member_stats is each member’s backlog, the number that reached 19,192 above. And a replica that falls behind with a quiet source usually has a cause on its own side; the stalled replica post walks through finding one.
| Question | Async | Semi-sync | Group Replication, default | Group Replication, AFTER | Source |
|---|---|---|---|---|---|
| Read right after a write sees it on another member? | No (6,000 of 6,000 missed) | No (2,000 of 2,000 missed) | Usually not (4,042 of 6,000 missed) | Yes (0 of 6,000 missed) | measured |
| Commit time at a 22 ms round trip (median) | 1.6 ms | 27.2 ms | 51.5 ms | 76.2 ms | measured |
| Can the copy fall behind under load? | Yes, 44,300 transactions | Yes (it waits for receipt only) | Yes, 19,192 transactions | No (6) | measured, semi-sync reasoned |
| More write capacity | No | No | No | No | reasoned |
