DoneDatabase Management

Automatic Failover with ProxySQL & Orchestrator

One stable endpoint for every app, automatic promotion and fail-closed fencing across three MySQL servers. Proven under load on the live stack: 6.9 s from killing the primary to the first write (manual: 491.7 s), 0 failed writes, 0 lost acknowledged writes.

Turned the manual failover from the replication project into an automatic one:

ProxySQL gives every app one stable database address and splits reads from writes.
Orchestrator detects a dead primary and promotes a replica.
A fencing sidecar makes sure the old primary can't come back as a second writer.
A post-failover hook puts back the settings Orchestrator undoes, and repoints change-data-capture.

Proven on the live stack under load: 6.9 s from killing the primary to the first successful write, 0 failed writes, 0 lost acknowledged writes. The first, manual drill took 491.7 s.

Built and run: 2026-10-09 → 10-11, on three MySQL 8.4 servers (one primary, two replicas) on the home server. I ran the deploy and drill steps on the server; Claude Code prepared the configs, scripts and step-by-step guides.

Tools

Tool	Version	Used for
ProxySQL	3.0.11	One endpoint for all apps: writer port 6033, reader port 6034, TLS to MySQL, role detection by polling `read_only`
Orchestrator (`ProxySQL/orchestrator` fork)	4.32.0	Topology discovery, failure detection, promotion, planned takeover; native ProxySQL hostgroup support
Fencer (about 120 lines of Python, stdlib only)		The only container allowed to touch the Docker socket; stops a dead primary and keeps it down
Bash hooks (`fence.sh`, `post-failover.sh`)		Orchestrator's pre- and post-failover steps
MySQL	8.4.11	Three servers: `mysql`, `mysql-replica`, `mysql-replica2`, async GTID replication
Kafka Connect / Debezium		CDC connector repointed after a failover
Discord webhook		One message per failover
Docker Compose overlays, systemd (`compose-stack.service`)		Deploy, boot order
Python (`mysql-connector`)		Write loop and load generator for the drills

Files

File (in `replication-stack/`)	What it is
`proxysql/proxysql.cnf.template`, `render-config.sh`	ProxySQL config with placeholders, and the script that fills them from `.env`
`proxysql/sync-users.sh`	Copies app users from MySQL into ProxySQL with their existing password hashes
`docker-compose.proxysql.yml`	ProxySQL service plus `depends_on` for the apps that use it
`orchestrator/orchestrator.conf.json.template`, `Dockerfile`	Orchestrator config and image (adds `curl`, `jq`, a MariaDB client)
`docker-compose.orchestrator.yml`	Orchestrator service
`sql/04_orchestrator_recovery_grants.sql`	Exactly the extra grants recovery needs
`fencer/fencer.py`, `docker-compose.fencer.yml`, `orchestrator/fence.sh`	The fencing service and its Orchestrator hook
`orchestrator/post-failover.sh`	Hook that re-applies ProxySQL settings, repoints Debezium and posts to Discord
`replica2/rebuild-replica.sh`, `docker-compose.replica2.yml`	Scripted build of the second replica
`recovery-sandbox/test-recovery.sh`	Throwaway three-node test rig using the real Orchestrator and fencer
`failover-drill/*` (`prep.sh`, `write-loop.py`, `load.py`, `watch.sh`, `drill-check.sh`, `report.sh`, `cleanup.sh`)	The drill kit
`reboot-guard/*`	Boot-time guard so a fenced server restarts read-only
`*-guide.md` (proxysql, orchestrator, fencing, replica2, post-failover-hook, reboot-guard, failover-drill, failover-drill2)	Step-by-step guides, one per piece

Architecture

 apps ──► ProxySQL :6033 (writes) ──► hostgroup 10 ──► current primary
     └──► ProxySQL :6034 (reads)  ──► hostgroup 20 ──► replicas (+ primary as fallback)
              ▲  polls @@read_only every second
              │  moves a promoted replica into hostgroup 10 by itself
   Orchestrator ──watches──► mysql / mysql-replica / mysql-replica2
        │ on a dead primary:
        ├─ PreFailoverProcesses  → fence.sh → fencer → docker update --restart=no; docker kill
        ├─ promotes the most up-to-date replica, re-points the other
        └─ PostMasterFailoverProcesses → post-failover.sh → ProxySQL settings, Debezium, Discord

Part 1: ProxySQL, one stable address

1.1 The configuration

What it does:

Two hostgroups: writers are 10, readers are 20.
Role detection: ProxySQL polls each server's read_only flag and moves servers between groups itself, so a promoted replica becomes the writer with no app change.
Reader port: everything sent to port 6034 goes to the readers.
Primary as reader fallback: the primary is also a reader with weight 1 against 1000 for the replica, so reads still work when no replica exists.
TLS: on for every backend.

mysql_variables=
{
    threads=4
    max_connections=1024
    interfaces="0.0.0.0:6033;0.0.0.0:6034;0.0.0.0:3306"   # 3306 = same as 6033, for apps that hard-code it
    server_version="8.4.11"
    default_authentication_plugin="caching_sha2_password"
    have_ssl=true
    monitor_username="proxysql_monitor"
    monitor_read_only_interval=1000      # role detection every second
    monitor_replication_lag_interval=2000
    connect_timeout_server=3000
}
mysql_servers=
(
    { address="mysql",         port=3306, hostgroup=10, use_ssl=1, max_connections=500, max_replication_lag=10, comment="primary at install time" },
    { address="mysql",         port=3306, hostgroup=20, weight=1,    use_ssl=1, max_connections=500, max_replication_lag=10, comment="primary as reader fallback" },
    { address="mysql-replica", port=3306, hostgroup=20, weight=1000, use_ssl=1, max_connections=500, max_replication_lag=10, comment="replica at install time" }
)
mysql_replication_hostgroups=( { writer_hostgroup=10, reader_hostgroup=20, comment="main" } )
mysql_query_rules=( { rule_id=10, active=1, proxy_port=6034, destination_hostgroup=20, apply=1, comment="reader port to hostgroup 20" } )

The config holds passwords, so it's a template. render-config.sh reads single keys with grep (never source .env, whose unquoted special characters break the shell), refuses values containing characters that would break the libconfig file, and runs envsubst:

get() {
  v=$(grep "^$1=" "$ENV_FILE" | tail -1 | cut -d= -f2- | sed -E "s/^['\"]//; s/['\"]$//")
  case "$v" in *\"*|*\\*|*\$*|*\;*|*\'*) echo "$1 contains a character not allowed" >&2; exit 1;; esac
  printf '%s' "$v"
}
envsubst '${PROXYSQL_MONITOR_PASSWORD} ${PROXYSQL_ADMIN_PASSWORD}' < proxysql.cnf.template > proxysql.cnf

1.2 Run it

# docker-compose.proxysql.yml
services:
  proxysql:
    image: proxysql/proxysql:3.0.11
    container_name: proxysql
    restart: unless-stopped
    depends_on:
      mysql:         { condition: service_healthy }
      mysql-replica: { condition: service_healthy }
    volumes:
      - ./proxysql/proxysql.cnf:/etc/proxysql.cnf:ro
      - proxysql-data:/var/lib/proxysql
    ports:
      - "${TAILSCALE_IP}:6033:6033"
      - "${TAILSCALE_IP}:6034:6034"
      - "127.0.0.1:6032:6032"        # admin: localhost only
    healthcheck:
      test: ["CMD", "bash", "-c", "exec 3<>/dev/tcp/127.0.0.1/6033"]   # bash: the image's sh has no /dev/tcp
      interval: 15s

1.3 Copy app users into ProxySQL with their existing passwords

What it does: ProxySQL needs its own copy of every user an app logs in as. The password hash is moved as hex (HEX in MySQL, UNHEX in ProxySQL), because the raw caching_sha2_password hash is binary and gets corrupted by normal text handling (the lesson of the HA incident). Users that should only read must hold only SELECT, or the script refuses to put them in the reader group.

hex=$(echo "SELECT HEX(authentication_string) FROM mysql.user WHERE user='$u' AND host='%'" | myq)
sql+="REPLACE INTO mysql_users (username,password,active,default_hostgroup,use_ssl,transaction_persistent,max_connections)
      VALUES ('$u',UNHEX('$hex'),1,$hg,$ssl,1,200);"
...
sql+="LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;"

DRY_RUN=1 prints the SQL with hashes hidden and changes nothing. root, debezium, repl, mysqld_exporter and proxysql_monitor are refused: they must keep talking to MySQL directly.

1.4 Cut the apps over, one at a time

Nine planned cutovers, each approved and tested before the next (one app, opengym, was deleted instead of cut over): notes, nihongo, job tracker, budget API, task API (with a code change to read from the reader port), five Airflow DAG files, the portfolio site, and the memory server. Each app only changes its database host and port to proxysql:6033, and turns TLS on.

# apps must wait for ProxySQL, or they start in the same second and hit "connection refused"
  portfolio:
    depends_on:
      proxysql: { condition: service_healthy }

Result:

Every app runs through ProxySQL, and ProxySQL → MySQL traffic is TLS for every backend.
A full server reboot came back in 2 minutes: 39 containers, CDC RUNNING by itself, row counts equal, all apps answering. The race it exposed (task-api crashed once at boot) is what the depends_on fixes.

Part 2: Drill #1, manual (2026-10-10)

2.1 The kit

What it does: takes a full backup first, creates a scratch table and an account that can only INSERT/SELECT on it, and registers that account in ProxySQL with TLS enforced. Then a write loop proves what an application would see.

# prep.sh (excerpt): refuses to start low on disk, backs up, creates the scratch objects
FREE_GB=$(df -BG --output=avail / | tail -1 | tr -dc 0-9)
[ "$FREE_GB" -ge 10 ] || { echo "only ${FREE_GB} GB free on /, need 10"; exit 1; }
docker exec mysql sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysqldump -uroot --all-databases \
  --single-transaction --routines --triggers --events --set-gtid-purged=OFF' | gzip > "$BK"
gzip -t "$BK" && echo "gzip integrity ok"

# write-loop.py: one write per second, a NEW connection each time so reconnect time is measured
c = mysql.connector.connect(host="proxysql", port=6033, user="drill_writer", password=pw,
                            database="sandbox_db", connection_timeout=2, autocommit=True)
cur = c.cursor()
cur.execute("INSERT INTO failover_drill (client_ts, note) VALUES (UTC_TIMESTAMP(3), 'loop')")
rid = cur.lastrowid
cur.execute("SELECT @@server_id")           # which server answered
print(f"{now()} OK id={rid} server_id={cur.fetchone()[0]} ms={int((time.time() - t0) * 1000)}", flush=True)

# watch.sh: one line whenever anything changes, with times, so the sequence reads like a log
PROXYSQL[10:mysql=ONLINE 20:mysql=ONLINE 20:mysql-replica=ONLINE] RO[mysql=0 mysql-replica=1 …] ORCH[NoProblem] REC=none CDC=RUNNING

2.2 What the manual run showed

Step	Measured
Kill → Orchestrator `DeadMaster` (observe-only)	about 4 s
Promote the replica by hand	0.21 s
ProxySQL flips the writer	about 1 s
Writes during the gap	held 10 s by ProxySQL, then ProxySQL error 9001
Reads during the gap	failed too (the primary was also a reader)
Debezium	reconnected by itself
Data	zero loss, but the idle test had lag 0, so it proved little
Kill → first write	491.7 s, almost all of it operator time

Result: the pieces worked, and the number made the case for automation. Two kit bugs were fixed: the loop's docker exec client being killed left the Python process running inside the container (now its pid is saved and killed), and cleanup.sh now refuses while the loop is alive.

Part 3: Orchestrator, observe-only first

3.1 Config

What it does: deployed with ReadOnly: true and no recovery filters, so it could discover and display the topology but change nothing. Safety settings are in from the start.

{
  "ReadOnly": true,
  "BackendDB": "sqlite",
  "MySQLTopologyUser": "orchestrator",
  "InstancePollSeconds": 5,
  "DiscoverByShowSlaveHosts": false,
  "DetectClusterAliasQuery": "SELECT 'main'",
  "RecoverMasterClusterFilters": [],
  "RecoveryPeriodBlockSeconds": 3600,
  "UseSuperReadOnly": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "DelayMasterPromotionIfSQLThreadNotUpToDate": true,
  "MaxConcurrentReplicaOperations": 1,
  "PreFailoverProcesses": ["bash /usr/local/bin/fence.sh {failedHost}"],
  "PostMasterFailoverProcesses": ["timeout 90 bash /usr/local/bin/post-failover.sh {successorHost} {failedHost}"],
  "ProxySQLAdminAddress": "proxysql", "ProxySQLAdminPort": 6032,
  "ProxySQLWriterHostgroup": 10, "ProxySQLReaderHostgroup": 20,
  "ProxySQLPreFailoverAction": "offline_soft"
}

# docker-compose.orchestrator.yml
  orchestrator:
    image: orchestrator:4.32.0
    depends_on:
      mysql: { condition: service_healthy }
      mysql-replica: { condition: service_healthy }
      proxysql: { condition: service_healthy }
    volumes:
      - ./orchestrator/orchestrator.conf.json:/etc/orchestrator.conf.json:ro
      - ./orchestrator/fence.sh:/usr/local/bin/fence.sh:ro
      - ./orchestrator/post-failover.sh:/usr/local/bin/post-failover.sh:ro
      - orchestrator-data:/var/lib/orchestrator
    ports: ["${TAILSCALE_IP}:3030:3000"]
    mem_limit: 256m

3.2 The exact grants recovery needs

-- sql/04_orchestrator_recovery_grants.sql, run on the PRIMARY (it replicates, so the
-- grants exist on whichever server becomes primary)
GRANT RELOAD, REPLICATION_SLAVE_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'orchestrator'@'%';

RELOAD: the FLUSH and RESET statements used during promotion.
REPLICATION_SLAVE_ADMIN: STOP/START REPLICA, CHANGE REPLICATION SOURCE TO, RESET REPLICA ALL.
SYSTEM_VARIABLES_ADMIN: SET GLOBAL read_only on the promoted server.
Deliberately not granted: REPLICATION SLAVE, legacy SUPER, and PROCESS. Granting PROCESS made Orchestrator find replicas from the process list by IP and show duplicates.

Result: the UI showed mysql as writable primary and mysql-replica as read-only replica, both healthy, lag 0, with no console errors.

Part 4: Fencing, no promotion without a confirmed fence

4.1 The fencer

What it does: a tiny HTTP service that is the only holder of the Docker socket. It accepts POST /fence/<name>, requires a bearer token (compared in constant time), only acts on names in an allowlist, and writes a fence marker before killing anything. If it can't write the marker, nothing is stopped and Orchestrator aborts the failover.

def fence(name, dry):
    st, info = docker("GET", f"/containers/{name}/json")
    ...
    marker = os.path.join(MARKER_DIR, name)            # written FIRST, atomically
    with open(marker + ".tmp", "w") as f:
        json.dump({"fenced_at": time.strftime("%FT%TZ", time.gmtime())}, f)
    os.replace(marker + ".tmp", marker)

    docker("POST", f"/containers/{name}/update", {"RestartPolicy": {"Name": "no"}})
    deadline = time.time() + WAIT
    while True:                                          # kill, then CONFIRM it is down with policy "no"
        st, info = docker("GET", f"/containers/{name}/json")
        if st == 200 and _down(info) and info["HostConfig"]["RestartPolicy"]["Name"] == "no":
            return 200, {"fenced": True, "was_running": was_running, "restart_policy": "no"}
        if st == 200 and info["State"].get("Running"):
            docker("POST", f"/containers/{name}/kill")
        if time.time() >= deadline:
            return 500, {"error": "could not confirm the container is stopped"}
        time.sleep(0.2)

Hardening in docker-compose.fencer.yml: runs as an unprivileged user, read_only: true, cap_drop: [ALL], no-new-privileges, 64 MB and 0.25 CPU.

4.2 The hook: fail closed

# fence.sh: Orchestrator runs this before promoting. Any answer other than HTTP 200 aborts the failover.
case "${ORC_COMMAND:-}" in
  graceful-master-takeover*) echo "fence: planned takeover, not fencing $name"; exit 0 ;;
esac
status="$(printf '%s' "$resp" | head -n1 | cut -d' ' -f2)"     # raw HTTP over /dev/tcp: the image has no curl
if [ "$status" = 200 ]; then echo "fence: $name ok"; exit 0; fi
echo "fence: $name FAILED (HTTP ${status:-none})" >&2
exit 1

Result: 15 sandbox checks passed (dry run, real fence, bad token, name not on the allowlist, fencer unreachable). In the sandbox, a promotion with the fencer down did not happen.

Part 5: The second replica

What it does: rebuild-replica.sh builds mysql-replica2 from scratch: checks the primary has GTID and binlog on, starts an empty server, seeds it (by dump or by the CLONE plugin), sets its own replication user (repl_r2), starts replication and verifies it. It refuses to run against anything but mysql-replica2, and refuses a target that already holds data unless WIPE=1.

docker exec "$P" sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" exec mysqldump -uroot --all-databases \
  --single-transaction --routines --triggers --events --flush-privileges --set-gtid-purged=ON' > "$SEED"
tail -c 300 "$SEED" | grep -q 'Dump completed' || { echo "dump incomplete"; exit 1; }
my "$R" -e 'RESET BINARY LOGS AND GTIDS'      # drop the init's local GTIDs, or GTID_PURGED is refused

Result: built by me on the live server: dump seed 553 MB, about 15 minutes, lag 0, now in ProxySQL hostgroup 20 and in Orchestrator. A piped import was flaky, so the script dumps to a file first and checks the dump finished. The lag check compares GTID sets rather than seconds.

Part 6: Sandbox test rig

What it does: test-recovery.sh builds a throwaway three-node cluster on an isolated network with the real Orchestrator image in recovery mode, the real fencer and the real fence.sh, kills the primary and checks what happens. It never touches the real stack, and it is leak-checked (every container removed with -v).

Scenario	Question it answers
`failover`	primary dies → fenced → promoted → other replica re-pointed
`fence-down`	fencer down → Orchestrator must not promote (fail closed)
`takeover`	planned `graceful-master-takeover`: is the healthy primary left alone?
`post-hook-proxysql`	Orchestrator with its ProxySQL keys against a throwaway ProxySQL: does the hook still end with correct rows?

Findings that changed the design:

A planned takeover ran the fence hook and killed the healthy old primary. The hook now skips graceful-master-takeover.
Re-pointing the second replica raced: 1 time in 4. MaxConcurrentReplicaOperations: 1 made it 6 of 6.
Orchestrator rewrites ProxySQL's server rows after the post-failover hook returns, so the hook needs a detached watcher (Part 8).

Part 7: Drill #2, automatic failover under load (2026-10-11)

7.1 Before: a pre-flight check that must pass

# drill-check.sh pre (excerpt)
[ "$FREE_GB" -ge 10 ] && ok "disk: ${FREE_GB} GB free on /" || bad "only ${FREE_GB} GB free"
VOLS=$(docker volume ls -q -f dangling=true | grep -cE '^[0-9a-f]{64}$')
[ "$VOLS" = 0 ] && ok "no dangling anonymous Docker volumes" || bad "$VOLS leaked volumes"
for c in proxysql orchestrator fencer kafka zookeeper kafka-connect; do  # each must be running and healthy

It also checks that there is exactly one writer and it is mysql, each replica replicates from the right source with lag in range and has no errant GTIDs, ProxySQL's writer hostgroup agrees with who is actually the writer, Orchestrator sees at least two healthy servers, its config has the fence hook and MaxConcurrentReplicaOperations: 1, the orchestrator user has the recovery grants, a fencer dry run succeeds with a writable marker directory, and the next 00:00 UTC DAG run is more than 2 hours away.

7.2 The run

Recovery turned ON, the 1-per-second write loop and a 30 rows/s load generator (4 threads) started, then docker kill mysql at 06:15:45.4 UTC and nobody touched anything. Afterwards the old primary was rejoined as a replica (isolated, GTID check empty, START REPLICA), then a planned failback (graceful-master-takeover) put it back.

Item	Value
Kill → Orchestrator `DeadMaster`	about 5.4 s
Orchestrator recovery (its own audit)	under 1 s, successor `mysql-replica`
Old primary fenced	`exited`, restart policy `no`, by 06:15:52.2
Kill → first successful write	6.9 s (`server_id=2`)
Failed writes in the 1/s loop	0 (the in-flight write waited 6.94 s in ProxySQL, limit 10 s, then succeeded)
Load generator	3 writes failed in flight, a 6 s pause, then full rate
Acknowledged writes missing	0: loop 2,125 acknowledged = 2,125 rows; load 70,526 acknowledged, ids 1 to 72,784 distinct, no gaps
Writes only the old primary had (GTID check)	none
Other replica	re-pointed by Orchestrator, lag 0, no errant GTIDs
Debezium	`RUNNING` in the API but not reading (a `Failed to restore connection` log line every minute); reconnected 18 s after the old primary rejoined
Metabase	down (`UnknownHostException: mysql`), back within a minute after the rejoin
Rejoin catch-up	629 s behind after 11 min down at 30 rows/s, draining at about 3 s per second
Planned failback	write pause 1.3 s, 0 errors; the fence hook correctly skipped it
End state	all three servers hold the same rows and GTID set; MySQL and Postgres counts equal

Part 8: Closing the gaps the drill found

8.1 The post-failover hook

What it does: after any recovery or planned takeover, it does three things, each limited to 20 s and the whole hook to 90 s.

ProxySQL: re-applies use_ssl=1, max_connections=500, max_replication_lag=10 on every server row, loaded to runtime and saved.
Debezium: sets the connector's database.hostname to the new primary and restarts a failed task once.
Discord: one message with what changed, or what needs you.

DRIFT_SQL="SELECT COUNT(*) FROM runtime_mysql_servers WHERE use_ssl<>$DESIRED_SSL OR max_connections<>$DESIRED_MAXCONN OR max_replication_lag<>$DESIRED_MAXLAG"

apply_proxysql() {
  admin "UPDATE mysql_servers SET use_ssl=$DESIRED_SSL, max_connections=$DESIRED_MAXCONN, max_replication_lag=$DESIRED_MAXLAG;
         LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK"
}

# Debezium: change database.hostname and wait for RUNNING/RUNNING
printf '%s' "$cfg" | jq --arg h "$NEW" '."database.hostname"=$h' \
  | curl -fsS -X PUT -H 'Content-Type: application/json' --data-binary @- "$CONNECT_URL/connectors/$CONNECTOR/config"

# Orchestrator rewrites the ProxySQL rows AFTER the hook returns, so start a detached watcher
setsid bash "$0" --watch </dev/null >/dev/null 2>&1 &

Modes: hook (default), --check (report drift), --apply <host> (by hand), --fix (cron: ProxySQL drift only) and --watch (started by the hook).

# crontab on the server: backstop, every minute
* * * * * docker exec orchestrator bash /usr/local/bin/post-failover.sh --fix >> /home/swe/checks/proxysql-fix.log 2>&1

Result: tested in the sandbox with a mock Kafka Connect and mock Discord, then live with a planned takeover and back. That live test is what found the "rewritten after the hook returns" behavior and led to the watcher and the cron.

8.2 The reboot guard

The problem: after Orchestrator recovers a dead primary, the fencer keeps it down with restart policy no. But compose-stack.service runs docker compose up -d --force-recreate at boot, which recreates that container with unless-stopped and starts it. The old primary then comes back writable, and anything that talks to it directly (Metabase) writes to a stale server.

What it does:

The fencer writes a fenced/<name> marker before it kills.
At boot, before the stack, gen-override.sh writes a compose override that mounts read_only=ON, super_read_only=ON into every marked server.
After the stack, check.sh stops any marked server that is somehow writable.
unfence.sh <name> removes the marker once the old primary has been rebuilt.

printf '[mysqld]\nread_only=ON\nsuper_read_only=ON\n' > "fenced/$n.cnf"
printf '  %s:\n    volumes:\n      - ./fenced/%s.cnf:/etc/mysql/conf.d/zz-fenced.cnf:ro\n' "$n" "$n"

Status: built and sandbox-tested, not deployed. Until it is, rebuild or demote the old primary before any reboot that follows a real failover.

Issues encountered

#	Issue	Root cause	Fix
1	`up -d proxysql` also recreated MySQL	Compose recreates dependencies; the env file changed. MySQL was gone about 10 s and the CDC task failed	always `up -d --no-deps`; restarted the task, counts matched. The site's `deploy.ps1` was fixed the same way
2	Hash-only users couldn't log in through ProxySQL	a first login with `caching_sha2_password` and only a stored hash needs TLS	TLS on every app connection, switched on per app after the app was ready
3	Apps crashed at boot	started in the same second as ProxySQL (`connection refused`)	`depends_on` with `service_healthy` for six apps
4	Password hash corrupted when copied	binary hash through text handling	hex in MySQL, `UNHEX` in ProxySQL
5	Orchestrator found duplicate replicas	`PROCESS` privilege made it discover replicas from the process list by IP	don't grant `PROCESS`
6	Grant changes didn't take effect	Orchestrator caches global privileges	restart it after global grant changes
7	Web "discover" refused	`ReadOnly: true` blocks it	added servers through the CLI
8	Fence hook killed a healthy primary during a planned takeover	the hook ran for planned operations too	skip when `ORC_COMMAND` is `graceful-master-takeover`
9	Re-pointing the second replica failed 3 times in 4	a known upstream race with concurrent replica operations	`MaxConcurrentReplicaOperations: 1` (1/4 → 6/6)
10	ProxySQL TLS silently turned off after a failover	Orchestrator rewrites ProxySQL's rows with its own defaults and saves them, after the hook returns	detached watcher in the hook, plus a 1-minute `--fix` cron
11	Debezium reported `RUNNING` while not reading	task state doesn't reflect a lost connection	the hook repoints it; checks read the log, not the status
12	Measurement was meaningless at first	at about 100 rows/s both replicas hit 90–96% CPU and lagged 7–10 s	load restarted at 30 rows/s; documented ceiling 40
13	Drill kit bug: errant-GTID check reported the 3 newest transactions	read the writer before the replica under live load	read the replica first
14	Drill kit bug: watcher timestamps up to 1.5 s early	stamped at the start of each pass	stamp after the queries
15	A promotion is blocked for an hour	Orchestrator's `RecoveryPeriodBlockSeconds` (3600)	`ack-all-recoveries` clears it; a planned takeover is not blocked
16	A demoted server wouldn't resume replicating	replication is configured but stopped, with no credentials	start it by hand with the replication user
17	The disk filled to 100%	test containers leaked 80 anonymous volumes (about 17 GB): MySQL paused, ZooKeeper and Kafka crashed	always `docker rm -fv`; `drill-check.sh` now refuses to start on low disk or dangling volumes; a disk alert was added
18	A plain `docker kill` was thought to be revived by `unless-stopped`	on this Docker version only a crash or self-exit is revived	still set `restart=no` first, so the fence is correct either way

Still open

Metabase connects to mysql directly, so it doesn't follow a failover yet (it needs a stable name or ProxySQL).
The reboot guard is built and tested but not deployed (8.2).
Optional: --report-host on mysql and mysql-replica, so Orchestrator can see replicas by their reported host.
Async replication limit: a kill when replicas are seconds behind loses those seconds. Semi-synchronous replication would close that gap at the cost of write latency.

Lessons learned

Test the failure path of your safety hook. The fence hook that killed a healthy primary was found only because the sandbox ran a planned takeover as well.
Fail closed. "Promote only after a confirmed fence" turns a possible split brain into a visible refusal.
A tool that manages another tool's config will overwrite it. Plan for Orchestrator rewriting ProxySQL, with a hook, a watcher and a cron backstop.
Measure with the right load. The first run saturated the replicas and measured the disk, not the failover.
A status that says RUNNING isn't proof. Check the log or the data, not the label.
Cleanup is part of the test. Leaked test volumes caused a real outage, so the kit now refuses to start without room.
The drill's result is the whole point: 491.7 s manual against 6.9 s automatic, with every acknowledged write accounted for.