A replication topology is not a high-availability design. It becomes one the day you can state, with numbers you have measured, how many transactions you lose when the primary dies and how long writes stop. Most teams we meet have the topology and not the numbers, and they discover both during the incident.
This walks through the mechanisms in the order they decide your outcome: what the replication mode promises about data loss, what GTIDs decide about recovery, and what the connection layer does while all of that happens. The last one is usually the longest part of the outage and gets the least design attention.
Write the two numbers down first
RPO is how much committed data you are willing to lose. RTO is how long writes may stop. Every choice below is a purchase of one with the other, plus latency.
The useful exercise takes ten minutes: write down the RPO and RTO your business actually requires, then write down what your current setup delivers. If you cannot fill in the second column from evidence — a real failover test, not a design document — that gap is the finding, and it is the one worth fixing before you change any topology.
Asynchronous replication: fast, and lossy by definition
The default. The primary commits, writes the binary log, and returns success to the client. Replicas fetch the events afterwards on their own schedule.
The primary never waits, so write latency is unaffected by replica health or network distance, and a dead replica cannot stall production. The cost is stated precisely by one number: whatever is in the primary's binary log but not yet received by any replica is lost when the primary's storage goes. Under normal conditions that window is milliseconds. Under the conditions that precede a crash — a replica lagging behind a long-running batch job, a saturated network link — it can be minutes.
Async is the right choice more often than purists admit. It is the wrong choice when you cannot afford to lose a single payment record and you have told someone that you cannot.
Semi-synchronous replication: a bounded loss window
With semi-sync enabled, the primary waits for at least rpl_semi_sync_source_wait_for_slave_count replicas to acknowledge receipt of the transaction's binlog events before returning success to the client. The replica acknowledges on receipt into its relay log, not on apply, so an acknowledged transaction is durable somewhere else but may not yet be visible on the replica.
Two settings decide the real behaviour:
rpl_semi_sync_source_wait_point = AFTER_SYNC(the default, and the one you want) makes the primary wait before committing in the engine. A crash then cannot leave a transaction visible on the old primary that no replica ever received — the phantom read that makes failed-over data reconciliation miserable.rpl_semi_sync_source_timeout(10 seconds by default) is the fallback. When no replica acknowledges within it, the primary silently downgrades to asynchronous and keeps serving writes. This is deliberate: it chooses availability over your RPO guarantee. It also means a cluster can spend a week in async mode believing it is semi-sync.
So if you run semi-sync, alert on Rpl_semi_sync_source_status going to zero. A guarantee that disables itself without telling anyone is not a guarantee.
The cost is latency: every commit now includes a network round trip to a replica. On a same-AZ link that is a fraction of a millisecond and effectively free. Across regions it is tens of milliseconds added to every write, which for a chatty ORM doing forty writes per request is the difference between a fast page and a timeout. Put your semi-sync acknowledger close and your disaster-recovery replica far, asynchronously.
One naming note for anyone upgrading: in MySQL 8.4 the semi-sync plugins were replaced by components, the variables were renamed, and the old rpl_semi_sync_master_* names are gone. If your provisioning code sets them by name, it will fail on 8.4 rather than warn.
Group replication: consensus, with conditions
Group replication puts a Paxos-style consensus protocol under the commit path. A transaction is certified by a majority of the group before it commits, so a majority of members always holds it. In single-primary mode the group elects a new primary automatically when the current one leaves; InnoDB Cluster wraps this with MySQL Shell and MySQL Router.
This is the strongest built-in answer, and it has real constraints that need to be checked against your schema before you plan a migration:
- Every table needs an explicit primary key. Group replication's certification is row-based and cannot work without one. Any legacy table without a primary key blocks adoption until it is fixed.
- InnoDB only,
binlog_format = ROW, GTIDs on. - Serializable isolation is unsupported, and concurrent cross-node writes to the same rows in multi-primary mode roll back with certification failures the application must handle. Most teams should run single-primary.
- The group needs low, stable latency between members. Stretching a group across distant regions produces flow-control stalls that look exactly like a database performance problem and are not.
- A member that falls too far behind gets expelled and needs distributed recovery — either replaying binlogs or a full clone — before it rejoins.
When the schema qualifies and the members are close, it removes the most error-prone part of failover: a human or a script deciding who is primary.
GTIDs, errant transactions, and why the old primary cannot rejoin
Whatever mechanism promotes the new primary, GTIDs are what make the rest of the cluster agree afterwards. Each transaction carries a globally unique identifier, so a replica repointed with CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1 works out for itself which transactions it is missing. Rebuilding that by hand from binlog file and offset, at 3 a.m., across four replicas, is how failovers turn into data divergence.
The failure mode to know by name is the errant transaction: a transaction that exists on one server and in no other server's gtid_executed. It gets there the ordinary way — someone ran a fix directly on a replica, or a monitoring tool wrote a heartbeat row without sql_log_bin = 0. It is invisible until that server is promoted or the demoted primary tries to rejoin, at which point the topology refuses to converge.
Check for them as a routine, not during the incident:
-- on each replica, compare against the primary's set
SELECT @@GLOBAL.gtid_executed;
-- and the useful form: what does this server have that the primary does not
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, '<primary gtid_executed set>');
An empty result is the answer you want. Anything else is a decision you should make deliberately — inject an empty transaction with that GTID everywhere, or rebuild the server — rather than one you discover under pressure.
The related question is whether the demoted primary can come back at all. If it committed transactions that no replica received, its gtid_executed is a superset of the new primary's and it cannot become a replica of it. Set super_read_only = ON on every replica so that only a promotion tool can make a server writable, and plan on rebuilding the old primary from a backup or a clone rather than assuming it will rejoin.
Split brain, and fencing the old primary
The scenario that costs the most is not a dead primary; it is a primary that stops answering health checks while still accepting writes — a network partition, a long GC pause on the host, a saturated NIC. Promote a replica without fencing the old one and you have two writable servers and a reconciliation job nobody wants to own.
Three defences, in order of how reliably they work:
- Majority quorum. Group replication does this natively: a minority partition loses quorum and stops accepting writes on its own. An orchestrator-style tool needs enough observers placed in separate failure domains to tell "the primary is down" apart from "I cannot see the primary."
- Fencing at the network or proxy layer. Remove the old primary from the proxy's writer hostgroup, or revoke its floating address, before promoting. This is the layer that decides what clients can reach, which makes it the layer that decides whether split brain is possible.
super_read_onlyeverywhere by default. Cheap, static, and it turns a stray write into an error rather than a divergence.
The proxy layer decides your actual RTO
Here is the part that surprises teams who have done the database work carefully. The server election finishes in seconds. Then the application spends two more minutes failing, because:
- DNS-based failover is slow and lies. JVM connection factories and many runtimes cache resolved addresses well past the record's TTL. A 30-second TTL does not produce 30-second failover.
- Connection pools hold dead connections. A pool with no validation query and a long idle timeout will hand out sockets to a host that is gone until each one individually times out. Set a connection validation check and a
socket_timeoutshorter than your RTO target. - The client-side timeouts are the real budget. If the driver's connect timeout is 30 seconds and it retries three hosts, your floor is 90 seconds no matter how fast the cluster elected.
The layer that fixes this is a proxy that holds the client connections and swaps the backend underneath them. ProxySQL with mysql_replication_hostgroups follows read_only and super_read_only to move a server between the writer and reader hostgroups automatically, and can queue writes briefly during the transition rather than erroring them. MySQL Router does the equivalent for InnoDB Cluster. RDS Proxy does it for managed instances and cuts the failover time RDS clients actually observe.
Whichever you pick, the proxy is now a component with its own availability requirement. Two of them behind a load balancer, or one per application host, not a single shared box that becomes the thing that takes you down.
Test it on a Tuesday
A failover design is a claim, and the claim is checkable. Run a real one in a staging environment with production-shaped traffic, and measure four things:
- Seconds from fault injection to a replica accepting writes.
- Seconds from fault injection to the application completing a write, which is the number your users experience.
- Transactions lost, by comparing
gtid_executedon the old primary against the new one. - Whether the old primary rejoined cleanly, or needed a rebuild.
Then do the same in production, in daylight, with the team present. A planned failover that is one command and forty seconds of degraded writes is a routine you can also use for kernel patching and version upgrades. A failover procedure that has only ever run during an outage is a document, not a capability.
We design and rehearse these for a living, usually alongside an upgrade that needs the same cutover discipline. If you want a second pair of eyes on a topology before you rely on it, send the replication mode, the member layout, and how the application connects — those three things predict most of what we would find.