MySQL HA Lab/MySQL HA Lab, Bonus: Operations and Troubleshooting Runbook for InnoDB Cluster on Docker
Body
MySQL HA LabMySQL HA Lab · MySQL · InnoDB Cluster
MySQL HA Lab, Bonus: Operations and Troubleshooting Runbook for InnoDB Cluster on Docker
Day-to-day commands for the MySQL HA lab: start, stop and reset, logs, reloading configs, restarting after tests or a Docker restart, and every problem hit while building the lab with its fix.
Day-to-day commands for the lab, recovering after tests or restarts, and fixes for every problem hit while building this lab. Commands using m, mroot, members, mstatus need source scripts/helpers.sh first.
Start, stop, remove
Monitoring services use a Compose profile; add --profile monitoring or commands skip them.
Goal
Command
Data kept?
Start everything
docker compose --profile monitoring up -d
Start cluster only
docker compose up -d
Pause
docker compose --profile monitoring stop
✅ containers and volumes
Resume
docker compose --profile monitoring up -d
Remove containers
docker compose --profile monitoring down
✅ volumes stay
Remove everything
docker compose --profile monitoring down -v --remove-orphans
❌ volumes deleted; cluster rebuilt next time
Re-run the bootstrap
docker compose up -d cluster-init
After down -v, the next up -d rebuilds the cluster, and cluster-init recreates the users, schemas and the app.t test table (Lesson 1).
Flag
Meaning
--profile monitoring
Include exporters, Prometheus, Grafana, monitoring-init
-v
Also delete named volumes (mysql1..3, prometheus_data, grafana_data)
--remove-orphans
Remove containers of services no longer in the compose file
docker compose --profile monitoring up -d (recreates changed services)
Restarting
sequenceDiagram
participant U as You
participant D as Docker
participant N as mysql1..3
participant I as cluster-init
participant R as Routers
participant H as HAProxy
Note over D,N: all nodes start at once, no running group to rejoin
U->>D: docker compose --profile monitoring up -d
D->>I: run cluster-init
I->>N: rebootClusterFromCompleteOutage()
N-->>I: group running, primary elected
U->>R: docker restart mysql-router1 mysql-router2
R->>N: reload the topology
H->>R: health checks pass, routers UP
Recovering after Docker or the Mac restarted: only cluster-init can reboot a cluster whose members all stopped at once.
What happened
Command
One node killed
docker start <node>. Rejoins as SECONDARY in 10–30s.
Node running but MISSING/OFFLINE
docker compose up -d cluster-init
Two or three nodes stopped
docker start mysql1 mysql2 mysql3, then docker compose up -d cluster-init
docker start mysql-router1 (HAProxy marks it UP after ~4s)
HAProxy stopped
docker compose up -d router-lb
Docker or the Mac restarted
docker compose --profile monitoring up -d, then docker restart mysql-router1 mysql-router2
Why the Docker-restart case needs up -d: all nodes restart at the same moment, so there's no running group to rejoin and the cluster stays offline. cluster-init reboots it, but it only runs on docker compose up, not when Docker restarts containers by itself. Routers started while there was no primary need a restart to reload the topology.
Troubleshooting
flowchart TD
s(["Writes fail through :6446"]) --> h{"HAProxy running?<br/>docker ps mysql-router"}
h -- no --> hf["docker logs mysql-router<br/>final newline, chmod a+rX<br/>docker compose up -d router-lb"]
h -- yes --> st{"Routers green on<br/>:8404/stats?"}
st -- no --> pr{"Cluster has a primary?<br/>members mysql2"}
pr -- no --> ci["docker compose up -d cluster-init<br/>docker restart mysql-router1 mysql-router2"]
pr -- yes --> rr["docker start / restart the routers"]
st -- yes --> q{"ERROR 1045 or 1146?"}
q -- yes --> users["docker compose up -d cluster-init<br/>re-syncs users and app.t"]
q -- no --> quorum["Check quorum: members, mstatus<br/>see Lesson 3"]
Where to start when writes through port 6446 fail.
Cluster
Symptom
Cause / fix
@@report_host is NULL
The .cnf files weren't read. chmod 644 conf/*.cnf, restart the nodes.
cluster-init: Can't connect ... mysqld.sock
mysqlsh tried a local socket. init-cluster.sh must pass --uri "root:...@mysql1:3306".
Instance is not managed by InnoDB cluster
addInstance was interrupted by the clone restart. Run cluster.rescan().
Errant GTIDs warning on a fresh build
Expected; clone overwrites the image's first-start transactions (Lesson 2).
Lost connection ... reading initial communication packet on 6446
HAProxy is up but no router is healthy, or no primary. Stats page, then group members.
Both routers red after a Docker restart
No primary when they started. docker compose up -d cluster-init, docker restart mysql-router1 mysql-router2.
Stale router in listRouters()
Container without hostname:. removeRouterMetadata('<name>').
haproxy -c: [NOTICE] could not resolve address
Not an error: ran outside the network, or a router isn't running. Check the exit code.
Monitoring
Symptom
Cause / fix
monitoring-init stuck on "Waiting for router..."
Lesson 4: debugging steps. If the whole chain works (mroot -e "SELECT 1" succeeds) but it keeps waiting, an attempt is hanging: docker restart monitoring-init.
Exporter Restarting (1)
Bad argument. command: must be a YAML list; no shell expands $VARS in the container.
mysql_up 0
Exporter can't log in. Variable must be MYSQLD_EXPORTER_PASSWORD (with D), and monitoring-init must have finished.
One node missing in Grafana, another doubled
Two exporters point at the same node. Check --mysqld.address and mysql_global_variables_server_id.
Grafana folder MySQL HA empty
Dashboard JSON in the wrong folder: it belongs in monitoring/grafana/dashboards/, not provisioning/dashboards/.
"metric might not be a counter"
Harmless; MySQL counters don't end in _total.
Group Replication panels "No data"
Search mysql_perf_schema_ in Prometheus; the two --collect.perf_schema.replication_group_* flags are required.
SQL and users
Symptom
Cause / fix
ERROR 1142 ... INSERT command denied to user 'clusteradmin'
By design: clusteradmin has no data privileges. Use the app user.
ERROR 1146 ... Table 'app.t' doesn't exist
docker compose up -d cluster-init creates it (Lesson 1).
Writes rejected on a table
Group Replication requires a primary key on every table.
"Using a password on the command line interface can be insecure"
Use client.cnf (m/mroot) or --passwords-from-stdin for mysqlsh.
m: command not found, or an empty user / --network error
New terminal: source scripts/helpers.sh.
A test call hangs; txloop stops printing
A connection to a killed node never answers. The helpers' timeout ends it after 15s; for calls started before that, mclean. Check that type m shows timeout.
Failover test never gets a new primary
Only two members were ONLINE before the kill, so quorum was lost. Run members before every test; see Lesson 3.
Access denied after changing .env
source scripts/helpers.sh regenerates client.cnf. For the app users, run docker compose up -d cluster-init, which re-syncs their passwords. Root's password only changes with the volumes (down -v).
Shell
Symptom
Fix
no such service: # / zsh: number expected
zsh treats # as text. setopt interactivecomments (helpers.sh sets it).
What's next: Debug this … with Gordon
Docker CLI hint. export DOCKER_CLI_HINTS=false.
Design notes
Config in files, not flags.common.cnf has the shared settings, each node file only its identity; 01-/02- prefixes set load order.
Group Replication settings live in mysqld-auto.cnf, written by AdminAPI with SET PERSIST, not in the .cnf files.
restart: unless-stopped is required for clone, which restarts mysqld.
Idempotent bootstrap.cluster-init creates, reboots, adds or rejoins depending on what it finds, so every up -d converges to a healthy cluster.
Single-primary mode. Multi-primary adds write conflicts and certification failures.
One exporter per node, connected directly, so each member reports its own view.
HAProxy keeps the name mysql-router, so nothing that connected to the single router had to change.
TCP health checks. Quick detection of a dead router; primary selection stays with MySQL Router.
Least-privilege users.clusteradmin manages, app owns the data, exporter only reads.
No passwords on command lines..env → scripts/helpers.sh → client.cnf / stdin.
Every test call has a timeout, like a real connection pool, so a dead connection can't freeze a test.
Monitoring as a Compose profile, so the base cluster stays light.
Everything provisioned from files, so a fresh up -d gives the same Grafana every time.
Limitations
All nodes run on one host; a host failure takes down everything.
Routers bootstrap through mysql1; if it's down when a router restarts, that bootstrap fails.
HAProxy is a single instance, so a single point of failure.
Credentials live in .env; use Docker secrets or a vault in production.
Next steps
Alerting on failovers and outages with Prometheus rules
Benchmark with sysbench: standard load, then failover under load
Backups and point-in-time recovery
Rolling upgrades without downtime
InnoDB ClusterSet for disaster recovery across data centers
Rebuild the failover-proof Spring Boot app in Go with database/sql: two pools, context deadlines, error classification, whole-transaction retry and the same Prometheus metrics.
Build a Spring Boot app on MySQL InnoDB Cluster that turns a primary crash into one slow request instead of errors: read/write pools, whole-transaction retry, idempotent writes and outage tracking.
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.