Body
MySQL HA Lab MySQL HA Lab · MySQL · InnoDB Cluster

MySQL HA Lab, Lesson 3: Failover, Quorum and Recovery in InnoDB Cluster

Kill the primary of a MySQL InnoDB Cluster and watch second by second what happens: router behaviour, election, quorum loss, and how a returning node catches up without losing data.

MySQL HA Lab Lesson 3: Failover, Quorum and Recovery
📘
This post is part of the MySQL HA Lab series: a hands-on MySQL 8.4 InnoDB Cluster on Docker. Code for this lesson: branch 01-cluster-setup on GitHub. Previous: Lesson 2: A self-bootstrapping InnoDB Cluster.

What happens when a node dies, how long writes are unavailable, and how a node that comes back catches up without losing data. All tests use the helpers from Lesson 1: run source scripts/helpers.sh in every terminal.


Before every test: three members ONLINE

members          # 3 rows, all ONLINE, one PRIMARY

If a node is missing or not ONLINE (left over from an earlier test), fix that first: docker start mysql1 mysql2 mysql3 and docker compose up -d cluster-init. With only two active members, killing the primary leaves 1 of 2: no majority, no new primary, and the "failover test" silently becomes a quorum test.

Failover test

Terminal 1, the transaction loop (Lesson 1):

source scripts/helpers.sh
txloop

Terminal 2, kill the primary and measure until a new one answers:

source scripts/helpers.sh
P=$(q 6446); echo "killing $P"; docker kill "$P"; s=$(date +%s)
until new=$(q 6446 2>/dev/null) && [ -n "$new" ]; do
  [ $(( $(date +%s) - s )) -ge 90 ] && { echo "no new primary after 90s, run: members mysql2"; break; }
  sleep 1
done
[ -n "$new" ] && echo "new primary $new after $(( $(date +%s) - s ))s"

docker start "$P"        # the old primary rejoins as a SECONDARY

Terminal 1 shows a few FAIL lines for 15–20 seconds, then OK with a different node=.

What happens, second by second

sequenceDiagram
    participant C as Client (txloop)
    participant R as Router
    participant P as mysql3 (old primary)
    participant G as mysql1 + mysql2
    Note over P: 0 s: docker kill
    R-xP: cannot reach the writable member
    R--xC: 1 s: stops accepting on 6446, ERROR 2003
    Note over G: ~5 s: mysql3 UNREACHABLE
    G->>G: ~10 s: expel mysql3, elect a new primary
    G-->>R: new primary (mode=RW)
    Note over R: ~17 s: accepts on 6446 again
    C->>R: INSERT
    R->>G: forward to the new primary
    G-->>C: OK node=new primary
A primary crash as each component sees it, from the kill to the first successful write on the new primary.

From a real run (router log times are UTC):

TimeEventWhere to see it
0sPrimary (mysql3) killeddocker kill
0sRouter can't reach the primary: "Could not connect to the writable cluster member"docker logs mysql-router1
1sRouter sees no primary (both survivors RO) and stops accepting connections on 6446. Clients get ERROR 2003 ... Can't connect to remote MySQL server at once instead of being sent somewhere wrong.router log, FAIL lines
~5sSurvivors mark the dead node unreachablemembers mysql2 shows it UNREACHABLE
~10sGroup expels it (group_replication_member_expel_timeout, default 5s) and elects a new primarymembers mysql2
~17sRouter sees the new primary (mode=RW) and starts accepting connections on 6446 againrouter log, OK node=<new>

docker kill is a hard kill (no clean shutdown), the worst case. A docker stop lets the node leave the group cleanly, so failover is almost instant.

Clients need timeouts

A call that starts in the first second can be handed a connection to the dead primary, which never answers. Without a time limit, that one client waits until HAProxy's idle timeout (1 hour), even though the cluster failed over long ago. That's why the helpers use --connect-timeout=5 and timeout 15 (Lesson 1): a hung call becomes FAIL ... timeout after 15s and the loop continues. Applications need the same: connect and socket timeouts in the connection pool.

Leftover stuck calls from before the timeouts can be removed with mclean.

After a full outage the primary can change

After docker compose stop / start (or a Docker or Mac restart), cluster-init reboots the cluster from the member with the highest GTID set, the most up-to-date one. The primary may be mysql2 or mysql3 afterwards. Never assume it's mysql1; ask with q 6446.

Quorum test

Quorum is lost only when members fail, not when they leave cleanly:

How two nodes go awayResult
docker stop (clean shutdown)They leave the group politely. The group shrinks to 1 member, which is a majority of 1, so writes continue.
docker kill (crash)They become unreachable but stay in the membership. 1 of 3 is not a majority, so writes block.
P=$(q 6446); echo "primary: $P"
docker kill $(for n in mysql1 mysql2 mysql3; do [ "$n" != "$P" ] && echo $n; done)
tx 1                     # hangs: the primary can't reach a majority (Ctrl+C to stop)

This is how Group Replication prevents split-brain: a minority never accepts writes.

Recover: start the killed nodes, then check the status. If it still shows NO_QUORUM, force the surviving partition to be the group and rejoin the others:

docker start mysql1 mysql2 mysql3
docker exec -it "$P" mysqlsh --js "$CLUSTER_ADMIN"@"$P":3306      # asks for the password
dba.getCluster().forceQuorumUsingPartitionOf('<primary>:3306')    // e.g. 'mysql2:3306'
docker compose up -d cluster-init      # rejoins the other members

How a returning node catches up

flowchart LR
    start(["docker start node"]) --> ro["OFFLINE<br/>super_read_only = ON"]
    ro --> join["Rejoin the group<br/>settings in mysqld-auto.cnf"]
    join --> gtid{"A donor still has the<br/>missing binary logs?"}
    gtid -- yes --> inc["Incremental recovery<br/>apply missing transactions"]
    gtid -- no --> clone["Clone<br/>full snapshot"]
    inc --> online(["ONLINE<br/>router sends it reads"])
    clone --> online
A returning node stays read-only until it has caught up, using the binary logs if a donor still has them, or a clone if not.

When a killed node is started again, it catches up on everything it missed before it serves traffic. No committed data is lost.

  1. Starts read-only. super_read_only=ON until it is a healthy member.
  2. Rejoins the group. Its Group Replication settings are in mysqld-auto.cnf in the data volume, so it rejoins automatically. State: OFFLINE → RECOVERING → ONLINE.
  3. Compares GTIDs with the group and requests the missing transactions.
  4. Catches up, one of two ways:
    • Incremental recovery (usual): copies the missing transactions from a donor's binary log (log_bin, log_replica_updates in common.cnf) and applies them.
    • Clone: if the donor no longer has those binary logs, copies a full snapshot instead.
  5. Goes ONLINE only when caught up. Only then does the router send it reads.

Writes made on the primary during recovery are queued and applied afterwards.

Why nothing committed is lost: a transaction commits only after a majority of the group agreed on it (certification). Anything the old primary confirmed already existed on at least one other node. Only a transaction in flight at the moment of the kill is lost, and that client got an error, not a commit, so it knows to retry.


Test: a secondary falls behind and catches up

# 1. kill a SECONDARY (check the primary first with: q 6446)
docker kill mysql3

# 2. write while it is down, so it falls behind
for i in $(seq 1 200); do tx $i; done

# 3. bring it back and watch it recover
docker start mysql3
while true; do clear; members mysql1; sleep 2; done      # Ctrl+C when mysql3 is ONLINE

mysql3 shows RECOVERING, then ONLINE. With monitoring (Lesson 4), the dashboard shows Members ONLINE at 2 and then 3, and mysql3's GR backlog rising and draining.

Test: all nodes hold the same data

Once all three are ONLINE:

# 1. same transactions everywhere: all three lines must be identical
for n in mysql1 mysql2 mysql3; do
  echo -n "$n: "; mroot -h $n -P 3306 -N -e "SELECT @@GLOBAL.gtid_executed" | tr -d '\n'; echo
done

# 2. same data everywhere: same row count and checksum
for n in mysql1 mysql2 mysql3; do
  echo -n "$n: "; mroot -h $n -P 3306 -N -e "SELECT COUNT(*) FROM app.t; CHECKSUM TABLE app.t" | tr '\n' ' '; echo
done
CheckProves
Identical gtid_executedEvery node applied exactly the same transactions
Identical row count and CHECKSUM TABLEThe table contents match

Test: the killed primary catches up

Repeat the first test, but kill the primary (P=$(q 6446); docker kill "$P") while txloop runs. Writes pause ~15–20s, then continue on the new primary. After docker start "$P" the old primary rejoins as a SECONDARY, and the consistency checks match on all three nodes.


Restarting after a test

What happenedCommand
One node killeddocker start <node>. Rejoins as a SECONDARY in 10–30s.
Node running but still MISSING/OFFLINEdocker compose up -d cluster-init (rejoins offline members)
Two or three nodes stoppeddocker start mysql1 mysql2 mysql3, then docker compose up -d cluster-init
Docker or the Mac restartedSee Operations

docker kill may count as a manual stop, so restart: unless-stopped doesn't always bring the node back by itself; start it explicitly.


The MySQL HA Lab series

  1. Series introduction
  2. Lesson 1: Environment and SQL helpers
  3. Lesson 2: A self-bootstrapping InnoDB Cluster
  4. Lesson 3: Failover, quorum and recovery (you are here)
  5. Lesson 4: Monitoring with Prometheus and Grafana
  6. Lesson 5: Highly available routing with HAProxy
  7. Lesson 6: Stale reads and read/write splitting
  8. Lesson 7: Failover tuning, switchover and partitions
  9. Lesson 8: A Spring Boot app that survives failover
  10. Lesson 9: The same app in Go
  11. Bonus: Operations and troubleshooting

All code is on GitHub: mytechbytes/mysql-ha-lab. Questions or a different result in your lab? Leave a comment below.

← Previous MySQL HA Lab, Lesson 2: A Self-Bootstrapping InnoDB Cluster in Docker Next → MySQL HA Lab, Lesson 4: Monitoring InnoDB Cluster with Prometheus and Grafana
Part 4 of 11 · Collection MySQL HA Lab Back to collection →
Membership

Go deeper.

Members get the full technical guides, production checklists and the source repos behind every article.

You may also like