mysqlha

HA MySQL lab: primary + 2 replicas, automated failover, chaos-injected outages, and MTTR measurement end to end.

                        ┌─────────────────┐
        writes ────────▶│                 │
                        │    ProxySQL     │       hostgroup 10 = writer
        reads  ────────▶│  (6033 / 6032)  │       hostgroup 20 = readers
                        └───┬────┬────┬───┘       (auto-detected via
                            │    │    │             read_only polling)
                 ┌──────────┘    │    └──────────┐
                 ▼               ▼               ▼
          ┌────────────┐  ┌────────────┐  ┌────────────┐
          │mysql-primary│  │  replica1  │  │  replica2  │
          │  (writer)   │─▶│ (semi-sync)│  │ (semi-sync)│
          └────────────┘  └────────────┘  └────────────┘
                 ▲               ▲               ▲
                 └───────────────┴───────────────┘
                     GTID-based replication
                                 │
                        ┌────────────────┐
                        │ failover-ctrl  │  polls primary via a write-probe
                        │ (orchestrator) │  heartbeat, promotes + repoints
                        └────────────────┘  on failure, fences on recovery

Repo layout

cluster/       docker-compose cluster: MySQL primary + 2 replicas, ProxySQL,
               plus a docker-compose.diskfull.yml override for the disk-full scenario
orchestrator/  failover_ctrl.py -- the failover controller (also built as a
               container and run as part of the cluster)
chaos/         chaos scenarios + shared helpers (common.py), driven by the
               `mysqlha chaos` CLI
client/        load_client.py -- concurrent write/read load generator that
               reports error rate and downtime windows independent of the
               chaos scripts' own MTTR probes
report/        MTTR results + failover event logs land here (gitignored,
               regenerated by each run)
mysqlha        CLI entrypoint: `mysqlha chaos run <scenario>`

Quickstart

python3 -m venv .venv && .venv/bin/pip install -r requirements.txt

cd cluster && docker compose up -d --build
# wait for all 5 containers healthy/running
docker compose ps

Verify writes go to the primary and reads go to a replica:

docker exec proxysql mysql -h127.0.0.1 -P6033 -uappuser -papppass appdb \
  -e "INSERT INTO pings (writer_node) VALUES ('smoke-test');"
docker exec proxysql mysql -h127.0.0.1 -P6033 -uappuser -papppass appdb \
  -e "SELECT @@server_id;"   # should be a replica's server_id

Run a chaos scenario:

.venv/bin/python3 mysqlha chaos list
.venv/bin/python3 mysqlha chaos run kill_primary

Run the load client alongside a scenario (separate terminal) to see the outage from the app's point of view:

.venv/bin/python3 client/load_client.py --duration 30 --workers 4

How failover actually works

Replication: GTID-based, semi-sync (rpl_semi_sync_master_wait_for_slave_count=1, 1s timeout). The primary and both replicas run with log_bin + log_slave_updates so any replica can be promoted without rebuilding binlogs.

Routing: ProxySQL's mysql_replication_hostgroups does the actual work of "repointing app connections" -- it polls each backend's read_only flag and moves servers between the writer hostgroup (10) and reader hostgroup (20) automatically. The failover controller never talks to ProxySQL's admin interface; it only flips read_only on the right node at the right time, and ProxySQL picks that up within its monitor_read_only_interval (1s).

Health check: a plain SELECT 1 survives a disk-full or InnoDB-stalled primary just fine -- no I/O required. The controller instead upserts a heartbeat row (appdb.ha_heartbeat) on every poll, which exercises the same commit path a real write does. 3 consecutive failed heartbeats (1s apart) before it declares the primary dead.

Promotion: among surviving replicas, the controller picks the one whose GTID set isn't a strict subset of any other candidate's (i.e. most caught-up), stops its replica threads, and flips it read-write. Other survivors get CHANGE REPLICATION SOURCE repointed at the new primary.

Recovery: a node that comes back from the dead is fenced read-only (SET PERSIST read_only/super_read_only = 1) before being reattached as a replica of whoever is currently primary. This is deliberate -- an unfenced comeback is how you get two writers.

Chaos scenarios

Scenario	What it does
kill_primary	docker kill on whatever ProxySQL currently reports as the writer. Hard stop, mysqld process gone.
network_partition	100% packet loss injected on the primary's eth0 via a nicolaka/netshoot sidecar joining its network namespace, healed after a fixed duration. mysqld keeps running the whole time -- this is the split-brain-prone case (see Limitations).
disk_full	Resets the cluster with a 768MB tmpfs-capped primary datadir, floods it with 1MiB rows until genuine ENOSPC. Self-contained: resets the cluster before and after, don't chain it with other scenarios in one session.
replication_lag	Stops a replica's SQL thread, piles up writes on the primary, resumes it, and times the catch-up to 0s lag. Doesn't trigger failover -- reports "time to consistency" rather than "time to writable."

All scenarios except replication_lag measure MTTR as: first failed write through ProxySQL (6033) to first write that succeeds again. That's the number an application would actually experience, not an internal controller timestamp.

MTTR results

Single run on a local Docker Desktop VM (Apple Silicon), default health-check tuning (1s poll interval, 3-failure threshold). Numbers will vary with your hardware, Docker Desktop VM sizing, and the tuning constants in orchestrator/failover_ctrl.py.

Scenario	MTTR	Notes
kill_primary	4.51s	SIGKILL primary, promote replica1, repoint replica2
network_partition	8.99s	100% loss for 20s on the then-current primary
disk_full	8.78s	768MB tmpfs primary datadir filled with 1MiB rows
replication_lag	0.72s	catch-up time after a 15s SQL-thread stall, not a failover

Detection is bounded below by the 3-poll threshold (~2-3s at 1s intervals); the rest is GTID comparison, promotion, repointing, and ProxySQL's own monitor_read_only_interval catching the flip. Lowering the poll interval or failure threshold trades faster detection for more sensitivity to transient blips.

Raw results and the controller's event log are written to report/ on every run (mttr_summary.csv, mttr_results.jsonl, failover_events.jsonl) -- gitignored since they're regenerated, not source.

Known limitations

• Split-brain race on network partition. While the primary is partitioned, it still believes read_only=0. The controller detects this because it polls over the same (blocked) network and fences the node once it reappears -- but there's a real race window between the partition healing and the fence landing. Production HA needs power-level fencing (STONITH) or a quorum mechanism (e.g. Raft-backed consensus); an application-level read_only flag flip is a mitigation, not a guarantee.
• GTID divergence on abrupt kill. If a transaction commits on the primary in the sub-second window before a hard kill and never reaches a replica via semi-sync ack, the recovered node can end up with GTIDs the new primary doesn't have. CHANGE REPLICATION SOURCE ... SOURCE_AUTO_POSITION=1 will then fail to start replication on that node (logged as reattach_failed); recovering it requires wiping its datadir and re-cloning from the current primary.
• disk_full is destructive to the running cluster. It always resets cluster state before and after via docker compose down -v. Don't chain it with other scenarios in the same invocation of a shell script.