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

MySQL HA Lab, Lesson 7: Failover from 25 s to 6 s, Switchover and Network Partitions

Where do the seconds of a MySQL InnoDB Cluster failover go? Measure them from the logs, cut them from 25 s to 6 s, use planned switchover, and test split brain with network partitions.

MySQL HA Lab Lesson 7: Failover from 25 s to 6 s, Switchover and Network Partitions
📘
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 06-failover-tuning on GitHub. Previous: Lesson 6: Stale reads and read/write splitting.

Lesson 3 measured an unplanned failover: about 17 seconds without writes. This lesson shows the planned alternative (switchover), how to decide which node becomes the next primary, where the failover seconds go, and which settings change them.

What changes in this lesson

FileChange
scripts/helpers.shmprimary (switchover), mjs (any AdminAPI command), mgrvars (failover settings per node), mevents (failover timeline from all logs), mnode (SQL inside a node's container), mcut / mjoin (network partition); docker exec calls set LC_ALL=C.UTF-8
scripts/init-cluster.jsApplies expelTimeout, autoRejoinTries and unreachable_majority_timeout on every run (7.5)
haproxy/haproxy.cfgHealth checks inter 1s fall 2 rise 1 instead of inter 2s fall 2 rise 2 (7.3c)
Runtime only (mjs)memberWeight 80 on mysql3 during 7.2; reset at the end
source scripts/helpers.sh
mgrvars
node    expel  rejoin exit_action     weight unreachable_majority
mysql1  5      3      OFFLINE_MODE    50     0
mysql2  5      3      OFFLINE_MODE    50     0
mysql3  5      3      OFFLINE_MODE    50     0
ColumnVariableAdminAPI optionMeaning
expelgroup_replication_member_expel_timeoutexpelTimeoutExtra seconds to wait, after a member is suspected (5 s of silence), before expelling it
rejoingroup_replication_autorejoin_triesautoRejoinTriesHow many times an expelled member tries to rejoin by itself
exit_actiongroup_replication_exit_state_actionexitStateActionWhat a member that drops out does: OFFLINE_MODE (refuse clients), READ_ONLY, ABORT_SERVER
weightgroup_replication_member_weightmemberWeightElection priority, 0–100; highest wins
unreachable_majoritygroup_replication_unreachable_majority_timeoutnone (SQL)Seconds a member cut off in a minority waits before leaving; 0 = forever

mjs runs any AdminAPI command with cluster already defined:

mjs "print(cluster.options())"
mjs "cluster.setOption('expelTimeout', 0)"                         # every member
mjs "cluster.setInstanceOption('mysql3:3306', 'memberWeight', 80)" # one member

AdminAPI writes these with SET PERSIST, so they survive a restart (they live in mysqld-auto.cnf in the data volume), but not docker compose down -v.

7.1 Planned switchover

sequenceDiagram
    participant A as Admin (mprimary)
    participant O as Old primary
    participant N as New primary
    participant R as Routers
    participant C as Client
    A->>O: setPrimaryInstance('mysql2:3306')
    O->>O: finish open transactions
    O->>O: super_read_only = ON
    N->>N: apply backlog, super_read_only = OFF
    O-->>R: topology change notification
    C->>O: INSERT on a just-opened connection
    O--xC: ERROR 1290 (read-only), retryable
    R->>N: port 6446 now leads here
    C->>N: retry INSERT
    N-->>C: OK
A planned switchover: at most one write hits the old primary just after it turned read-only, gets a retryable 1290, and succeeds on retry.

When you know the primary has to go (maintenance, upgrade, moving load), don't stop it: hand the primary role over first.

# terminal 1
txloop

# terminal 2
mprimary mysql2          # cluster.setPrimaryInstance('mysql2:3306')
q 6446                   # mysql2
mprimary mysql1          # and back

What happens: the old primary finishes its open transactions, turns super_read_only on, the new one applies any backlog and turns it off, and the routers get notified and move port 6446.

Result in this lab:

17:45:36  FAIL  #23  ERROR 1290 (HY000) ... running with the --read-only option
17:45:37  OK    #24  node=mysql2
17:45:38  FAIL  #26  ERROR 1290 (HY000) ... running with the --read-only option
17:45:38  OK    #27  node=mysql1

One failed transaction per switch, under a second. 1290 means a new connection reached the old primary just after it became read-only and before the router moved. Nothing is lost; retrying works. Applications should treat 1290 as retryable (Lesson 8).

Compare: a crash of the primary costs about 17–25 seconds (7.2). So for any planned work, switch over first, then stop the node.

7.2 Choosing the next primary: memberWeight

On failover the group elects the member with the highest memberWeight; ties are broken by the lowest server_uuid, which is effectively random. Use weights to prefer a node (bigger machine, same zone as the app) and to keep a node from becoming primary (weight 0, for example a reporting replica).

# terminal 1: txloop running
mjs "cluster.setInstanceOption('mysql3:3306', 'memberWeight', 80)"
mgrvars                          # mysql3 weight 80
docker kill mysql1               # crash the primary
sleep 20; q 6446                 # mysql3
docker start mysql1; sleep 20; members mysql2   # mysql1 rejoins as SECONDARY
mprimary mysql1 mysql2           # mysql1 primary again (run via mysql2)

A restarted member does not take the primary role back on its own, even with a higher weight. Election only happens when the primary leaves; the switch back is a deliberate mprimary.

Result in this lab: the new primary was mysql3, and writes stopped for 25 seconds:

TimeWhat happenedWhat the client saw
17:45:47docker kill mysql12013 Lost connection ... reading authorization packet
17:45:47–48Router still sends to mysql1, which is gone2003 Can't connect to remote MySQL server
17:45:48–53One connection attempt waits for the 5 s --connect-timeout2013 ... system error: 110
17:45:53–46:11No primary, so both routers close port 6446 and HAProxy marks both routers down. With no backend, HAProxy accepts and closes at once2013 ... reading initial communication packet
17:46:12First commit on mysql3OK node=mysql3
17:46:29mprimary mysql12 × 1290, then OK node=mysql1

Lesson 3 measured about 17 s. 7.3 takes these 25 s apart using the logs.

Reset the weight afterwards:

mjs "cluster.setInstanceOption('mysql3:3306', 'memberWeight', 50)"

7.3 Where the failover seconds go

flowchart LR
    k(["Primary crashes"]) --> d["Detection<br/>5 s, fixed"]
    d --> e["Suspicion, expel, election<br/>16 s default · 1 s with expelTimeout 0"]
    e --> r["Routers see the new primary<br/>~0.2 s"]
    r --> h["HAProxy marks routers UP<br/>~3 s (inter 2s rise 2) · under 1 s (inter 1s rise 1)"]
    h --> ok(["Writes resume<br/>25 s default · ~6 s tuned"])
Where the seconds of a crash failover go, with default and tuned settings.

mevents collects the important lines from the logs of the three nodes, both routers and HAProxy, and sorts them by time. Docker log times are UTC, so add 5:30 to compare them with tx.log (IST):

mevents 3m                         # last 3 minutes
mevents 2026-10-17T12:15:45Z       # from a fixed time

Default settings (the crash from 7.2)

UTC+sEventLogged by
12:15:47.80Both routers stop accepting on 6446: resolve(mysql1) failed (Docker removed the dead container's DNS name)routers
12:15:50.1 / 51.72–4HAProxy: router2, router1 DOWN (fall 2 × inter 2s), "no server available"HAProxy
12:15:52.95"mysql1 has become unreachable": 5 s without messagesmysql3
12:16:08.921"Members removed" → "new primary mysql3 elected" → "working as primary", within 3 msmysql3
12:16:08.9 / 09.121Both routers start accepting on 6446routers
12:16:11.7 / 12.224HAProxy: router1, router2 UP (rise 2 × inter 2s)HAProxy
12:16:1225First commit on mysql3tx.log
PhaseSecondsControlled by
Detection5Fixed: a member is suspected after 5 s of silence
Suspicion → expel → election16expelTimeout (5 s), plus waiting for the next suspicion check
Routers notice the new primary0.2Group Replication notifies the routers immediately
HAProxy sees the routers again3inter 2s rise 2 in haproxy.cfg

Phase 2 took 16 s, although expelTimeout is only 5 s. Group Replication's messaging layer processes suspicions periodically, so the expel happens at the first check after the timeout has passed. This is why failover time varies between runs (17 s in Lesson 3, 25 s here).

Two things this shows:

  • The routers are not the bottleneck. They react within 200 ms, in both directions.
  • In a Docker crash the router fails fast because the DNS name disappears. When a real host dies its address still resolves, so new connections wait for the connect timeout first. That is why clients need timeouts.

7.3b Expel immediately: expelTimeout 0

mjs "cluster.setOption('expelTimeout', 0)"
mgrvars                                    # expel 0 on all three
# terminal 1: txloop     terminal 2:
docker kill mysql1
sleep 30; mevents 1m
docker start mysql1; sleep 20; members mysql2
mprimary mysql1 mysql2

Three runs:

PhaseDefaultexpelTimeout 0 run 1run 2
Detection (unreachable)5.0 s5.2 s5.1 s
Detection → expel + election16.0 s1.0 s1.0 s
Election → routers accept on 64460.2 s0.1–0.4 s0.2–0.3 s
Routers accept → HAProxy UP2.8–3.3 s2.3–3.6 s3.2–3.9 s
Writes down (tx.log)25 s9 s10 s

The suspicion phase drops from 16 s to 1 s, which cuts the outage to about a third. The trade-off: a member that is only slow (a network blip, a long stall) is now expelled after 5 s of silence instead of being given extra time. It then has to rejoin (7.4). On a stable network, expelTimeout 0 to 2 is reasonable; on a shaky one, keep the default or raise it.

7.3c Faster HAProxy checks

With the expel wait gone, HAProxy's rise 2 × inter 2s is now a third of the outage. In haproxy/haproxy.cfg, all three MySQL backends change to:

default-server check inter 1s fall 2 rise 1 resolvers docker init-addr last,libc,none on-marked-down shutdown-sessions
SettingBeforeAfterEffect
inter2s1sCheck every second
fall22Down after 2 failed checks (now 2 s instead of 4 s)
rise21Up after one good check: at most 1 s after the router opens 6446

rise 1 is safe here because a router only opens 6446 when it knows a primary; a single good check means it is ready.

docker compose run --rm --no-deps --entrypoint haproxy router-lb -c -f /usr/local/etc/haproxy/haproxy.cfg
docker compose up -d --force-recreate router-lb
# then the same kill test as 7.3b
UTC+sEvent
13:32:38.90Routers stop accepting on 6446 (mysql1 killed)
13:32:40.8 / 40.92HAProxy: both routers DOWN (fall 2 × 1 s)
13:32:43.24.3mysql1 unreachable
13:32:44.25.3Expelled, mysql3 elected, working as primary
13:32:44.35.4Both routers accept on 6446
13:32:44.8 / 44.95.9HAProxy: both routers UP

tx.log: last OK on mysql1 at 19:02:38, first OK on mysql3 at 19:02:45.

Summary

SettingsWrites down
Defaults (expelTimeout 5, inter 2s rise 2)25 s
expelTimeout 09–10 s
expelTimeout 0 + inter 1s fall 2 rise 1~6 s
Planned switchover (mprimary)< 1 s, one retryable error

What is left is the 5 s detection, which Group Replication does not let you change. Below that, use switchover.

7.4 Network partitions

A crash (docker kill) stops the node. A partition leaves it running but unable to talk to the others: the dangerous case, because both sides are alive and could each think they are in charge.

mcut mysql2      # docker network disconnect: mysql2 keeps running, alone
mjoin mysql2     # docker network connect --alias mysql2: plug it back in
mnode mysql2 "SQL"   # SQL inside the container, works while it is cut off

7.4a Cut off a secondary

# terminal 1: txloop     terminal 2:
mcut mysql2
sleep 15; members mysql1
mnode mysql2 "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members"
mnode mysql2 "SELECT COUNT(*) FROM app.t"
sleep 30
mnode mysql2 "SELECT COUNT(*) FROM app.t"
mjoin mysql2
sleep 30; members mysql1; mevents 3m

The same group, seen from both sides:

members mysql1 (majority)              mnode mysql2 (minority)
mysql1  ONLINE  PRIMARY                mysql2  ONLINE       SECONDARY
mysql3  ONLINE  SECONDARY              mysql1  UNREACHABLE  PRIMARY
                                       mysql3  UNREACHABLE  SECONDARY
UTCEvent
13:39:44mcut mysql2
13:39:50.4mysql1, mysql3: "mysql2 has become unreachable"
13:39:50.5mysql2: "not able to reach a majority … will now block all updates"
13:39:51.5mysql1, mysql3: "Members removed: mysql2" (expelTimeout 0)
13:40:30mjoin mysql2
13:40:47mysql2: "resumed contact with a majority"

tx.log: 344 OK, 0 FAIL during the whole test. Losing a secondary does not affect writes.

What this shows:

  • The minority protects itself. mysql2 cannot reach a majority, so it blocks every update. Two primaries (split brain) cannot happen.
  • The minority still serves stale reads. COUNT(*) on mysql2 stayed at 29768 for 30 s while the loop kept inserting on mysql1. A client connected directly to mysql2 gets old data and no error. Router only routes to members of the majority, so clients going through Router are safe; direct connections are not.
  • With unreachable_majority_timeout = 0, the minority member waits like this forever.

7.4b Cut off the primary

The split-brain case: the old primary keeps running, alone, while the majority elects a new one.

# terminal 1: txloop     terminal 2:
mcut mysql1
sleep 10; members mysql2
mnode mysql1 "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members"
MH_TIMEOUT=10 mnode mysql1 "INSERT INTO app.t (v) VALUES (-1)"; echo "exit=$?"
mnode mysql1 "SELECT @@super_read_only, @@offline_mode"
mjoin mysql1
sleep 180; members mysql2           # reconnecting can take minutes, see below
w "SELECT COUNT(*) FROM app.t WHERE v=-1"
docker logs -t --since 10m mysql1 2>&1 | grep -iE 'expel|rejoin|unreachable|majority|rollback|read only'

Two primaries, seen from each side:

members mysql2 (majority)              mnode mysql1 (minority)
mysql2  ONLINE  SECONDARY              mysql1  ONLINE       PRIMARY
mysql3  ONLINE  PRIMARY                mysql2  UNREACHABLE  SECONDARY
                                       mysql3  UNREACHABLE  SECONDARY
UTCEventWhere
13:55:57mcut mysql1
13:55:58.2Routers stop accepting on 6446 at once (they can no longer reach the primary)routers
13:56:03.7mysql1: "not able to reach a majority … will now block all updates"mysql1
13:56:04.7mysql1 expelled, mysql3 electedmysql2, mysql3
13:56:05.4 / 05.7Routers accept again, HAProxy UProuters, HAProxy
13:56:18mjoin mysql1
13:58:23mysql1: "resumed contact with a majority" (2 min after the network came back)mysql1
13:58:46.7mysql1: "expelled … status to ERROR", "transactions … will now rollback", "set into read only mode"mysql1
13:58:46.7Session 7600 fails in before_commit: the test INSERT (-1)mysql1
13:58:46.7"Started auto-rejoin procedure attempt 1 of 3"mysql1
13:58:51.2"Member was able to join the group": mysql1 is ONLINE SECONDARYmysql1

Results:

QuestionAnswer
Writes through RouterDown 7 s (19:25:58 → 19:26:05 IST), then on mysql3
Does the cut-off primary know it lost?No. It still calls itself ONLINE PRIMARY, with super_read_only 0 and offline_mode 0
A write sent straight to itHangs, no error (exit=124 from the client timeout). The server kept that transaction waiting for 2.5 minutes, until it learned it was expelled and rolled it back
Did that write survive?No: COUNT(*) WHERE v=-1 is 0. No split brain
RecoveryAutomatic: resumed contact → expelled → rollback → auto-rejoin, 5 s from contact to ONLINE

Lessons:

  • Group Replication never lets two primaries commit. The minority blocks. But "blocks" means hangs: an application connected directly to the old primary freezes instead of failing over. Connect through Router, and give every client a timeout.
  • Reconnecting is slow. After the network came back it took 17 s for mysql2 (7.4a) and 2 min for mysql1; the longer a member is cut off, the longer it seems to take. Do not conclude "rejoin failed" too early.
  • With unreachable_majority_timeout = 0 the zombie primary waits like this for as long as the partition lasts. 7.4c makes it give up.

If a member does not come back on its own:

MH_TIMEOUT=60 mnode mysql1 "STOP GROUP_REPLICATION; START GROUP_REPLICATION"
# or
docker compose up -d cluster-init   # rejoins any member that is not ONLINE

7.4c Make a cut-off member give up

group_replication_unreachable_majority_timeout is not an AdminAPI option; set it with SQL on every member:

for n in mysql1 mysql2 mysql3; do
  mroot -h $n -P 3306 -e "SET PERSIST group_replication_unreachable_majority_timeout = 10"
done
mgrvars                                         # unreachable_majority 10

# terminal 1: txloop     terminal 2:
mcut mysql1
sleep 20
mnode mysql1 "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members"
mnode mysql1 "SELECT @@super_read_only, @@offline_mode"
MH_TIMEOUT=10 mnode mysql1 "INSERT INTO app.t (v) VALUES (-2)"; echo "exit=$?"
mjoin mysql1
sleep 180; members mysql2
docker logs -t --since 10m mysql1 2>&1 | grep -iE 'expel|rejoin|unreachable|majority|rollback|read only|offline'
UTCEvent on mysql1
14:06:34mcut mysql1
14:06:39.3"not able to reach a majority … will now block all updates … for the next 10 seconds. Unless contact with the majority is restored, after this time the member will error out and leave the group"
14:06:49.3"could not reach a majority … for more than 10 seconds. The member will now leave the group", "set into read only mode"
14:06:54mjoin mysql1
14:06:58.1"Started auto-rejoin procedure attempt 1 of 3"
14:07:03.4"attempt 1 of 3 finished. Member was able to join the group"
7.4b (unreachable_majority 0)7.4c (unreachable_majority 10)
Old primary after the cutONLINE PRIMARY, foreverERROR after 10 s
super_read_only / offline_mode0 / 01 / 0
Direct write to itHangs; held on the server for 2.5 minFails at once: ERROR 1290 … --super-read-only
Back in the group after the network returns~2.5 min9 s (auto-rejoin)

offline_mode stays 0 although exitStateAction is OFFLINE_MODE. Our reading: while auto-rejoin attempts remain, the member is only made read-only; the exit state action applies once all autoRejoinTries attempts have failed.

Recommendation: set unreachable_majority_timeout. A member that cannot reach a majority is useless as a primary; failing fast is better for every client than hanging.

7.5 Making the tuning permanent

Everything set with mjs or SET PERSIST lives in the data volumes (mysqld-auto.cnf) and is lost with docker compose down -v. So scripts/init-cluster.js now applies the tuning at the end of every run:

// 4. Failover tuning (Lesson 7)
const EXPEL_TIMEOUT        = 0;   // expel 0 s after a member is suspected (default 5)
const UNREACHABLE_MAJORITY = 10;  // a cut-off minority leaves after 10 s (default 0 = never)
const AUTO_REJOIN_TRIES    = 3;   // expelled members try to rejoin by themselves (default 3)

cluster.setOption('expelTimeout', EXPEL_TIMEOUT);
cluster.setOption('autoRejoinTries', AUTO_REJOIN_TRIES);
// unreachable_majority_timeout is not an AdminAPI option: SQL on each node
SET PERSIST group_replication_unreachable_majority_timeout = 10
SettingLab valueWhy
expelTimeout025 s → 9 s failover (7.3b). Raise it on a less stable network
unreachable_majority_timeout10A cut-off primary fails fast instead of hanging (7.4c)
autoRejoinTries3Default; members come back after a partition by themselves
memberWeightnot set (50)Equal nodes in this lab; set it per instance when one should be preferred

It is wrapped in try/catch: if a member is not ONLINE, the cluster still comes up and the log says Tuning NOT applied. Run cluster-init again once all members are back.

Test without losing data: undo the settings by hand, then let cluster-init put them back:

mjs "cluster.setOption('expelTimeout', 5)"
for n in mysql1 mysql2 mysql3; do
  mroot -h $n -P 3306 -e "SET PERSIST group_replication_unreachable_majority_timeout = 0"
done
mgrvars                                   # expel 5, unreachable_majority 0
docker compose up -d cluster-init
docker logs --tail 100 -f cluster-init    # "Tuning applied: ..."
mgrvars                                   # expel 0, unreachable_majority 10

Reset after the lesson

The weight from 7.2 is not part of the script:

mjs "cluster.setInstanceOption('mysql3:3306', 'memberWeight', 50)"
mgrvars

What you learned

  • Planned work: switch over first (setPrimaryInstance). It costs one retryable 1290 error, against 6–25 s for a crash.
  • memberWeight decides the next primary; a returning node does not take the role back on its own.
  • A crash costs: 5 s detection (fixed) + expel wait (expelTimeout, plus the suspicion check interval) + router (≈0.2 s) + HAProxy checks. Measured: 25 s with defaults, about 6 s tuned.
  • mevents builds the failover timeline from all logs; the routers are never the slow part.
  • A partitioned minority blocks updates (no split brain) but still answers reads with stale data; only Router keeps clients away from it.
  • A cut-off primary does not know it lost: direct writes to it hang until the partition heals. unreachable_majority_timeout makes it leave after N seconds and refuse writes, and it rejoins in seconds afterwards.
  • Runtime tuning is lost with down -v; init-cluster.js re-applies it.

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
  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 (you are here)
  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 6: Stale Reads, Read/Write Splitting and group_replication_consistency Next → MySQL HA Lab, Lesson 8: A Spring Boot App That Survives a MySQL Failover
Part 8 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