MySQL async replication HA, part 2 – the source dies

Series: 1 the build · 2 the source dies · 3 replicas die, and the reads you sent them

This is the failure everybody builds HA for: the server taking the writes just stops. No graceful shutdown, no warning. What does the application actually get back while Orchestrator decides what to do? I wanted the answer as sysbench lines, not as a diagram, so I killed the source of the cluster from part 1 while sysbench was writing to it.

What I expected was a short stop in writes, Orchestrator promoting mysql-2, mysql-3 moving under it, and ProxySQL’s writer changing from mysql-1 to mysql-2. That’s what happened in the end. It’s the run before that one I learned the most from, and I’ll get to it.

The kill

With sysbench running, in another terminal:

docker kill mysql-1

There’s no restart policy, so the container stays stopped (Exited (137)). I watched docker logs -f sysbench, Orchestrator’s web page, and these:

docker exec proxysql mysql -h127.0.0.1 -P6032 -uadmin -padmin \
  -e "SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers ORDER BY 1,2"
docker logs orchestrator 2>&1 | tail
docker exec orchestrator tail -n 20 /var/log/proxysql-failover.log

The kill went in at 21:59:17. This is Orchestrator’s view, one second at a time, from its /api/all-instances endpoint. Each entry is a server, then its source, its replication lag, whether Orchestrator’s last check of it worked, rw or ro, and whether both replication threads are running:

21:59:18 mysql-1[src=-,null,ok,rw,repl=no] mysql-2[src=mysql-1,0s,ok,ro,repl=yes] mysql-3[src=mysql-1,0s,ok,ro,repl=yes]
21:59:19 mysql-1[src=-,null,invalid,rw,repl=no] mysql-2[src=mysql-1,null,ok,ro,repl=no] mysql-3[src=mysql-1,null,ok,ro,repl=no]
21:59:20 mysql-1[src=-,null,invalid,rw,repl=no] mysql-2[src=mysql-1,null,ok,ro,repl=no] mysql-3[src=mysql-1,null,ok,ro,repl=no]
21:59:21 mysql-1[src=-,null,invalid,rw,repl=no] mysql-2[src=-,null,ok,rw,repl=no] mysql-3[src=mysql-2,0s,ok,ro,repl=yes]

The hook log starts the second Orchestrator detects the failure and is done one second later. DeadMaster is Orchestrator’s name for “the source stopped answering”:

2026-10-19T21:59:19+00:00 detected DeadMaster cluster=mysql-1:3306 failed=mysql-1:3306
2026-10-19T21:59:19+00:00 pre-failover DeadMaster cluster=mysql-1:3306 failed=mysql-1:3306
2026-10-19T21:59:20+00:00 hook start type=DeadMaster failed=mysql-1 successor=mysql-2
2026-10-19T21:59:20+00:00 fenced mysql-1 into hostgroup 30
2026-10-19T21:59:20+00:00 successor mysql-2 read_only=0
2026-10-19T21:59:20+00:00 published mysql-2 into writer hostgroup 10
2026-10-19T21:59:20+00:00 post-failover DeadMaster failed=mysql-1 successor=mysql-2:3306

And sysbench, on the same clock:

21:59:16 [ 217s ] thds: 4 tps: 968.05 qps: 19378.05 (r/w/o: 13565.74/3876.21/1936.10) lat (ms,95%): 4.49 err/s: 0.00 reconn/s: 0.00
21:59:17 [ 218s ] thds: 4 tps: 653.84 qps: 13060.89 (r/w/o: 9143.82/2609.38/1307.69) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 4.00
21:59:18 [ 219s ] thds: 4 tps: 0.00   qps: 0.00     (r/w/o: 0.00/0.00/0.00)        lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
21:59:19 [ 220s ] thds: 4 tps: 0.00   qps: 0.00     (r/w/o: 0.00/0.00/0.00)        lat (ms,95%): 0.00 err/s: 4.03 reconn/s: 0.00
21:59:20 [ 221s ] thds: 4 tps: 858.55 qps: 17220.06 (r/w/o: 12057.75/3441.21/1721.11) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00
21:59:21 [ 222s ] thds: 4 tps: 1069.01 qps: 21368.17 (r/w/o: 14960.12/4270.03/2138.02) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
21:59:22 [ 223s ] thds: 4 tps: 1095.96 qps: 21928.12 (r/w/o: 15349.39/4386.82/2191.91) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00

The kill second still finished 654 transactions, and all 4 threads reconnected (reconn/s 4.00). The next two seconds completed nothing. At 21:59:19 each thread recorded one error (err/s 4.03), and at 21:59:20, the second the hook published mysql-2, traffic was back at 859 tps. By 21:59:21 it was above the line from before the kill, and err/s stayed at 0 from there on.

That’s 4 failed transactions in total, one per connection.

Where everything ended up

ProxySQL after the hook. The first query is the runtime, what ProxySQL routes by: mysql-2 is the ONLINE writer, with the hook’s comment, and mysql-3 is the reader. mysql-1 is missing, because runtime_mysql_servers doesn’t list OFFLINE_HARD servers. The second query is the admin table, which does. There mysql-1 sits in hostgroup 30, OFFLINE_HARD, fenced by the hook. Nothing routes to hostgroup 30, and OFFLINE_HARD makes ProxySQL drop the connections it still holds to that server.

In Orchestrator the top node is now mysql-2, read/write, with mysql-3 under it. The dead mysql-1 isn’t in the graph, and the banner names the DeadMaster recovery that promoted mysql-2:

SHOW REPLICA STATUS on mysql-2 is empty, because it’s the source now, with read_only=0 and super_read_only=0. On mysql-3, Source_Host is mysql-2, both threads say Yes, Seconds_Behind_Source is 1 (it was still catching up on sysbench writes) and Auto_Position is 1. The GTID set still has the old source’s transactions (c5d38ec0-…:1-189376) plus the new source’s (cb080e17-…:1-25607). Orchestrator moved the replica with GTIDs. It didn’t rebuild mysql-1.

The ProxySQL connection graph across the same minute tells the same story from the proxy’s side. Client connections stay, since sysbench reconnects into the proxy, while server connections dip until mysql-2 is online:

The two hook bugs before this run

Here’s the part I’d most like you to take away. The first two runs used an earlier version of the hook, and their failover still worked. It just didn’t work because of the hook.

The old hook logged in to ProxySQL as admin. ProxySQL accepts that user only from its own localhost, so every statement was rejected with ERROR 1040 (42000): User 'admin' can only connect locally. The hook didn’t check, and logged “fenced” and “published” anyway. What actually moved mysql-2 into hostgroup 10 was ProxySQL’s own read_only monitor, and the health check shunned mysql-1. The lab now has a second admin user, radmin, for the hook and for port 16032, and the hook logs FAILED when a statement doesn’t apply.

With the login fixed, the next run went worse: 112 and then 240 errors per second, for two seconds. Why? deploy loads all three servers into hostgroup 10 and lets the read_only monitor sort them, but the monitor only changes the runtime. The admin table still said “everyone in hostgroup 10”. When the hook ran LOAD MYSQL SERVERS TO RUNTIME, ProxySQL loaded that stale table, made mysql-3 a writer for about a second, and writes failed on its super_read_only. The hook now runs SAVE MYSQL SERVERS FROM RUNTIME before it edits anything, and the run above is with that fix.

I think any script that edits ProxySQL during an incident has the same trap. Read what’s running before you write, and check that each write was applied.

What this lab doesn’t tell you

The two-second stall is my lab’s timers, not a promise. Orchestrator polls every 2 seconds and gives up on a connect after 1 second, and ProxySQL pings every second and shuns after two failures. A production Orchestrator poll is often 5–10 seconds, sometimes with several missed checks before a failover, so your users would probably wait longer.

Async replication can lose committed transactions. The client was told the commit succeeded before any replica had the change, and whatever the source committed that the replicas hadn’t applied is gone. I didn’t reopen the killed data directory, so I can’t tell you how many that was here. Semi-sync makes the source wait until at least one replica has received the transaction before the client gets its answer. It costs latency on every commit, and it doesn’t save you if that one replica dies together with the source.

The fence was never really tested either. The hook marks the old source OFFLINE_HARD in hostgroup 30, but in this lab the process was already dead, so the fence had nothing to stop. A network cut that leaves the old source alive and writable is a different failure, and could give you two writable servers. No run here tried it, so please don’t treat this lab as proof that the fence holds.

Then there’s the promoted replica. Orchestrator clears read_only in memory, but the config file still says read_only=ON and super_read_only=ON. If the new source’s container restarts, it comes back read-only and writes fail until somebody turns those variables off. Nobody rebuilds the old source for you, either. mysql-1 stays dead until you clone the new source and point it there, and Orchestrator won’t prefer it if it comes back.

One more thing if you repeat the experiment. Orchestrator won’t run a second automatic failover for one minute (FailureDetectionPeriodBlockMinutes=1), so wait that minute out.

Verdict

With this lab’s timers, losing the source cost the application about two seconds of zero throughput and one error per connection. It went back to writing without anyone changing its host or password, because the address it used was ProxySQL the whole time.

That part of the design works.

What I wouldn’t trust is the glue.

My hook reported success while doing nothing, and then did harm once it could log in. The safety net was ProxySQL’s own read_only monitor. If you run a failover hook, test it on its own, and make it read the current state before it writes. Then plan for the promoted server’s config file, because the next restart will make it read-only again.

In part 3 I go after the replicas instead, first with an application that doesn’t read from them and then with one that does.