DoneDatabase Management

High-Availability MySQL Replication & Failover Drill

GTID replication built from scratch, a working lag alert, a failover drill run twice, and a real five-app outage from making the failover permanent: how it was diagnosed and recovered without retyping a single password.

The complete record of building MySQL high availability on the home server, in the order it happened:

GTID replication built from scratch,
a lag alert that actually notifies,
a failover drill that was paused once and completed the next time,
and a real five-app outage caused by making that failover permanent, with the diagnosis and recovery.

Every command and error on this page was actually run and hit. The follow-up project, Automatic Failover with ProxySQL & Orchestrator, replaces the manual promotion shown here.

Timeline: 2026-08-19 built → 08-20 verified, first drill paused → 08-22 full drill, then a real incident. Environment: one Docker host (swe-2), stack_default bridge network.

Honest scope: this is two containers on one physical host. It proves replication mechanics and failover procedure. It does not prove survival of a hardware failure.

Tools

Tool	Version / where	Used for
MySQL	8.4, `mysql` (primary) and `mysql-replica`	GTID-based asynchronous replication
`mysqldump` + `gzip`	in the MySQL image	Consistent seed of the replica, with the GTID position embedded
Docker, Docker Compose overlays, Docker networks and aliases	Docker on Ubuntu	Run the replica, rename containers, repoint apps without touching their config
`mysqld_exporter`	v0.16.0 (v0.15.1 on the primary's exporter)	Replication lag and thread state as metrics
Prometheus + Grafana	existing monitoring stack	Scrape the replica exporter, evaluate and send the lag alert
Airflow (`arbeitnow_pipeline` DAG)	existing	A real write workload to prove the promoted server accepts writes
Bash (`failover-watchdog.sh`)	repo	Scripted fallback failover (later superseded by Orchestrator)

Files created for this project

File (in `replication-stack/`)	What it is
`docker-compose.replication.yml`	Overlay that adds `mysql-replica` and `mysqld-exporter-replica` without editing the base compose file
`sql/01_create_replication_user.sql`	Least-privilege `repl` account on the primary
`sql/02_start_replica.sql`	`CHANGE REPLICATION SOURCE TO … START REPLICA`, then lock the replica read-only
`sql/03_create_replica_exporter_user.sql`	Least-privilege exporter account on the replica
`mysqld-exporter-replica/.my.cnf`	Exporter credentials (a `.example` is committed; the real file is not)
`failover-runbook.md`	The drill procedure with fill-in-the-result blanks, and the real-incident checklist
`failover-watchdog.sh`	Cron script that probes the primary and, if it is confirmed dead, fences and promotes
`README.md`	Setup steps and the gotchas carried over from earlier projects

Part 0: Building replication (2026-08-19)

0.1 Plan and the overlay file

What it does: adds a second MySQL container as a replica using a Compose overlay, so "what replication added" is one file and the primary's own definition is untouched. The replica shares the project network, so it reaches the primary by service name.

services:
  mysql-replica:
    image: mysql:8.4
    container_name: mysql-replica
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_REPLICA_ROOT_PASSWORD}
    command:
      - "--server-id=2"
      - "--log-bin=mysql-bin"
      - "--gtid-mode=ON"
      - "--enforce-gtid-consistency=ON"
      - "--relay-log=mysql-relay-bin"
      # no --replicate-do-db filter: replicate every database (see 0.3)
    volumes:
      - mysql-replica-data:/var/lib/mysql
    ports:
      - "${TAILSCALE_IP}:3307:3306"
    depends_on:
      - mysql
volumes:
  mysql-replica-data:

# readiness check on the primary first (it needs server_id and GTID mode ON)
docker exec -e MYSQL_PWD="$PRIMARY_ROOT_PW" mysql mysql -uroot \
  -e "SHOW VARIABLES WHERE Variable_name IN ('log_bin','server_id','gtid_mode','enforce_gtid_consistency');"

docker compose -f docker-compose.yml -f docker-compose.replication.yml up -d

Result: the primary already had GTID mode on, so it needed no restart. The replica came up empty and writable.

0.2 Create the least-privilege replication user

What it does: the replica logs in with an account that can only read the binlog, with no access to table data.

-- sql/01_create_replication_user.sql, run on the PRIMARY
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Running a script without a password prompt (a prompt and a redirected file cannot share stdin):

docker cp sql/01_create_replication_user.sql mysql:/tmp/01_create_replication_user.sql
docker exec -e MYSQL_PWD='<primary root password>' mysql mysql -uroot -e "source /tmp/01_create_replication_user.sql"
docker exec mysql rm /tmp/01_create_replication_user.sql

Result: account created. From here on, every command used MYSQL_PWD (via docker exec -e) instead of -p: no prompt to lose, no inline password in shell history.

0.3 First seed attempt: scoped wrong

What it does: dump only project1_jobs and load it on the replica.

# WRONG: scoped to one database
docker exec -e MYSQL_PWD='<primary root password>' mysql mysqldump \
  --single-transaction --source-data=2 -uroot --replicate-do-db=project1_jobs project1_jobs \
  | gzip > seed.sql.gz

Result: replication broke immediately.

SHOW REPLICA STATUS\G
-- Replica_SQL_Running: No
-- Last_SQL_Error: Errno 1049 "Unknown database"

Cause: the primary's binlog carries transactions from every database on the instance (budget_db, todo_db, metabase_app…) from real app traffic, so the SQL thread died the moment one of those appeared.

Decision: replicate the whole server. That's closer to genuine failover coverage for everything on the host.

SHOW DATABASES;
-- analytics, bookstack_db, budget_db, metabase_app, movielens, portfolio,
-- project1_jobs, project3_perf, sandbox_db, todo_db
-- (mysql, sys, information_schema, performance_schema deliberately excluded:
--  copying `mysql` would overwrite the replica's own root credentials)

0.4 Re-seed, whole server

What it does: clears the half-started state, then dumps and loads everything.

The aborted attempt had already locked the replica, which blocked cleanup:

SET GLOBAL super_read_only = OFF;    -- needed before DROP DATABASE could run

and left GTID history behind:

ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid
set must not overlap with @@GLOBAL.GTID_EXECUTED

RESET REPLICA ALL only clears connection state, not gtid_executed. A true clean slate (MySQL 8.4's name for RESET MASTER):

RESET BINARY LOGS AND GTIDS;

Then the real dump and restore:

docker exec -e MYSQL_PWD='<primary root password>' mysql mysqldump \
  --single-transaction --source-data=2 -uroot \
  --databases analytics bookstack_db budget_db metabase_app movielens portfolio \
              project1_jobs project3_perf sandbox_db todo_db \
  | gzip > full_seed.sql.gz

gunzip -c full_seed.sql.gz \
  | docker exec -i -e MYSQL_PWD='<replica root password>' mysql-replica mysql --default-character-set=utf8mb4 -uroot

--source-data=2 embeds the primary's GTID position as a comment, not an active command. Since SOURCE_AUTO_POSITION=1 is used next, GTID auto-positioning works out where to resume.

Result: the restore ran for a while, which was expected: project3_perf alone is about 1,000,567 rows. Confirming it was working and not hung:

SHOW PROCESSLIST;
-- an active INSERT INTO jobs in progress

A "hung" dump that wasn't a MySQL problem. An earlier docker exec -it … mysqldump | gzip looked stuck for 5 minutes. SHOW PROCESSLIST showed no mysqldump connection at all: the command never reached the server. It was stuck at an invisible password prompt buried by -it plus a piped stdout. Ctrl+C didn't respond, so it was killed from a second terminal (containers share the host kernel, so ps sees the process):

ps aux | grep mysqldump
kill -9 <pid>

0.5 Start replication

What it does: points the replica at the primary using GTID auto-positioning, then locks the replica against accidental writes.

-- sql/02_start_replica.sql, run on the REPLICA
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '<password>',
  SOURCE_AUTO_POSITION = 1,        -- GTID: no binlog file/position to track
  GET_SOURCE_PUBLIC_KEY = 1;       -- caching_sha2_password without TLS needs the RSA key

START REPLICA;

-- once healthy: lock the replica (only the replication applier may write)
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

GET_SOURCE_PUBLIC_KEY=1 was used up front because the same failure had already hit Metabase's JDBC driver against this MySQL 8.4 instance (allowPublicKeyRetrieval=true).

Result: Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: 0.

0.6 Replication lag had no metric at all

mysql_slave_status_seconds_behind_master returned nothing, not even 0.

Cause: the existing exporter only connects to the primary, and SHOW REPLICA STATUS on a primary is empty (it's not a replica of anything). Lag is a property of the replica's own connection state, so it needs its own exporter instance. That's added in Part 1.

Session paused here: replication seeded, replica exporter prepared on disk but not deployed, no failover attempted.

Part 1: Verification and monitoring (2026-08-20)

1.1 Deploying the replica exporter: four bugs in a row

What it does: a second mysqld_exporter pointed at the replica, a least-privilege account for it, and a new Prometheus scrape job.

-- sql/03_create_replica_exporter_user.sql, run on the REPLICA
CREATE USER IF NOT EXISTS 'mysqld_exporter'@'%' IDENTIFIED BY '<password>';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'mysqld_exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'%';
FLUSH PRIVILEGES;

# docker-compose.replication.yml (second service)
  mysqld-exporter-replica:
    image: prom/mysqld-exporter:v0.16.0       # v0.15.1 sends the removed SHOW SLAVE STATUS
    container_name: mysqld-exporter-replica
    restart: unless-stopped
    command:
      - "--config.my-cnf=/etc/mysqld-exporter/.my.cnf"
      - "--collect.global_status"
      - "--collect.global_variables"
      - "--collect.slave_status"
    volumes:
      - ./mysqld-exporter-replica/.my.cnf:/etc/mysqld-exporter/.my.cnf:ro
    ports:
      - "${TAILSCALE_IP}:9105:9104"

# mysqld-exporter-replica/.my.cnf
[client]
user=mysqld_exporter
password=<password>
host=mysql-replica
port=3306

# monitoring-stack/prometheus/prometheus.yml
  - job_name: mysql_replica
    static_configs:
      - targets: ["mysqld-exporter-replica:9104"]

The four bugs:

Never deployed. Prometheus had no mysql_replica job: the service was prepared on disk the day before but never copied to the server. Deployed it.
Bind-mount became a directory. ./mysqld-exporter-replica/.my.cnf didn't exist yet, so Docker silently created it as an empty directory on first start. Created the real file first, then recreated the container.
Password mismatch. The password contained a backslash. CREATE USER … IDENTIFIED BY stripped it as an unrecognized escape server-side while .my.cnf kept it. Reset to a password with no \, ' or ".
Version incompatibility. mysqld_exporter v0.15.1 (pinned for the primary) sends the removed SHOW SLAVE STATUS and MySQL 8.4 rejects it with Error 1064 … near 'SLAVE STATUS'. Fixed upstream in v0.16.0, which also renamed the metric to mysql_slave_status_seconds_behind_source.

Result: mysql_slave_status_seconds_behind_source = 0, scraped from the replica every 15 s.

1.2 The Grafana lag alert: two passes

What it does: fires when replica lag stays above 30 s.

First pass: fixed just the renamed metric. Still No Data.
Second pass: the query had > 30 baked into the PromQL, so a healthy lag of 0 returned an empty result, and Grafana reports an empty result as No Data, not Normal. Restructured to match the other six rules: raw metric in the query, Is above 30 as a separate Threshold expression.

Result: Normal with real data (0). Replication had been healthy the whole time; every issue in this part was monitoring-side.

1.3 Scripts and the pre-drill check

# are both replication threads running, and how far behind?
docker exec -e MYSQL_PWD="$REPLICA_ROOT_PW" mysql-replica mysql -uroot -e "SHOW REPLICA STATUS\G" \
  | grep -E "Running|Behind"
# Replica_IO_Running: Yes   Replica_SQL_Running: Yes   Seconds_Behind_Source: 0

A drill against a replica that was already silently lagging isn't a valid test.

Part 2: First failover attempt, paused on purpose (2026-08-20)

2.1 Steps 1–3 worked cleanly

docker kill mysql   # hard failure on purpose, not a graceful docker stop

-- on the replica
STOP REPLICA;
RESET REPLICA ALL;                 -- clears replication config entirely: now a standalone primary
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

SELECT COUNT(*), MAX(date_posted) FROM project1_jobs.jobs;
-- 3,625 rows, 2026-08-18: matches the pre-kill state

2.2 Step 4 (repoint the application) blocked

docker exec airflow-scheduler airflow dags trigger arbeitnow_pipeline

mysql.connector.errors.DatabaseError: 2005 (HY000): Unknown MySQL server host 'mysql' (-3)

Two things were still unconfirmed when the session paused: whether pipeline_writer and portfolio_web existed on the replica (they hadn't been seeded, because the whole-server restore deliberately excluded the mysql system schema), and whether the DAG's host came from an env var or was hardcoded.

2.3 Restore to the pre-drill state

The DAG write failed, so no new data landed on the promoted replica and the two servers stayed in sync, which made restoring simple:

docker start mysql

-- re-run sql/02_start_replica.sql against mysql-replica

GTID position survived RESET REPLICA ALL, so replication resumed with no re-seed. Explicitly paused, not abandoned.

Part 3: Completing the drill (2026-08-22)

3.1 Precondition check, done right

What it does: answers the two open questions before touching the primary.

SELECT user, host FROM mysql.user WHERE user IN ('pipeline_writer','portfolio_web');
-- neither existed on the replica

-- on the replica
SET GLOBAL super_read_only = OFF;
CREATE USER IF NOT EXISTS 'pipeline_writer'@'%' IDENTIFIED BY '<pipeline_writer password>';
GRANT SELECT, INSERT, UPDATE ON project1_jobs.* TO 'pipeline_writer'@'%';
CREATE USER IF NOT EXISTS 'portfolio_web'@'%' IDENTIFIED BY '<value, see 5.2>';
GRANT SELECT ON portfolio.blog_posts    TO 'portfolio_web'@'%';
GRANT SELECT ON portfolio.project_links TO 'portfolio_web'@'%';
GRANT SELECT ON portfolio.projects      TO 'portfolio_web'@'%';
FLUSH PRIVILEGES;
SET GLOBAL super_read_only = ON;

# project2-data-pipeline/arbeitnow_pipeline_dag.py, line 30
MYSQL_HOST = "mysql"   # a literal string, NOT os.environ.get("DB_HOST"),
                       # even though the container DOES set DB_HOST

Result: hardcoded. No amount of config repointing would fix step 4, so the fix had to happen at the network layer.

3.2 Kill and promote

docker kill mysql

SHOW REPLICA STATUS\G
-- Replica_IO_Running: Connecting
-- Last_IO_Error: Can't connect to MySQL server on 'mysql:3306' (111)
-- Executed_Gtid_Set: 518ccd6b-9bdc-11f1-849d-eec305a1821a:1-12      <- the exact data-loss boundary

STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

3.3 Repoint with a network alias, not app config

What it does: gives the promoted replica the name mysql on the Docker network, so the hardcoded hostname resolves to it.

docker network disconnect stack_default mysql --force
docker network disconnect stack_default mysql-replica
docker network connect --alias mysql --alias mysql-replica stack_default mysql-replica

docker exec airflow-scheduler getent hosts mysql
# 172.18.0.11   mysql                         <- the promoted replica

3.4 Verify a real write lands

SELECT MAX(date_posted), COUNT(*) FROM project1_jobs.jobs;
-- 2026-08-21, 3975        (before)

docker exec airflow-scheduler airflow dags trigger arbeitnow_pipeline

SELECT MAX(date_posted), COUNT(*) FROM project1_jobs.jobs;
-- 2026-08-22, 4150        (after: 175 new rows)

Result: drill passed. 175 new rows landed on the promoted server with zero application code changes. The fix lived at the infrastructure layer.

Part 4: Making the promotion permanent (where the incident started)

The decision: instead of reverting like Part 2, swap roles for good. mysql-replica stays primary and the original mysql rejoins as the replica.

4.1 Backup first

Direct access to the bind-mounted data directory needed elevated rights not available in that session, so a throwaway container mounted the same path:

docker run --rm -v /var/lib/docker/mysql-data:/data:ro -v /home/swe:/backup alpine \
  sh -c 'tar czf /backup/mysql-data-backup-pre-swap-20260822-2329.tar.gz -C /data .'

This backup turned out to be essential.

4.2 Wipe and reseed the old primary as the new replica

docker stop mysql; docker rm mysql
docker run --rm -v /var/lib/docker/mysql-data:/data alpine sh -c 'rm -rf /data/* /data/.[!.]*'
cd /home/swe/stack && docker compose up -d mysql      # fresh re-init

docker exec -e MYSQL_PWD='<new primary root pw>' mysql-replica mysqldump \
  --single-transaction --source-data=2 -uroot \
  --databases analytics bookstack_db budget_db metabase_app movielens portfolio \
              project1_jobs project3_perf sandbox_db todo_db \
  | gzip > /tmp/full_seed_swap.sql.gz
gunzip -c /tmp/full_seed_swap.sql.gz \
  | docker exec -i -e MYSQL_PWD='<old primary root pw>' mysql mysql --default-character-set=utf8mb4 -uroot

-- new repl user, this direction (on the new primary)
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY '<generated>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- on the old primary (now the replica)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql-replica', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '<generated>',
  SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

Result: Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: 0. It looked clean. It wasn't: no app had tried to connect since the swap.

4.3 Repoint monitoring to match the new roles

The two exporters' .my.cnf host= values were swapped so the labels (mysqld-exporter = primary, mysqld-exporter-replica = replica) stayed correct.

RSA key failure again. Right after the swap the exporters authenticated inconsistently: plain mysql worked, but mysqladmin and the exporter itself didn't.

Error 1045 (28000): Access denied for user 'mysqld_exporter'@'172.18.0.22' (using password: YES)

Same class as the Metabase JDBC issue and GET_SOURCE_PUBLIC_KEY=1, so it was recognized immediately. Fix: get-server-public-key=1 in both .my.cnf files, then restart both exporters.

DNS collision from the rename. docker rename mysql mysql-secondary doesn't clear Docker's embedded DNS registration, so mysql resolved to both servers:

docker exec airflow-scheduler getent hosts mysql
# 172.18.0.11   mysql
# 172.18.0.23   mysql     <- stale, from the just-renamed container

docker network disconnect stack_default mysql-secondary
docker network connect    stack_default mysql-secondary

The replica exporter was also repointed to host=mysql-secondary, since host=mysql now correctly meant "the primary".

Part 5: The incident, five apps down

5.1 Symptom

Error: Access denied for user 'portfolio_web'@'172.18.0.3' (using password: YES)
mysql.connector.errors.InterfaceError: Can not reconnect to MySQL after 1 attempt(s): 1045 (28000):
  Access denied for user 'taskapp'@'172.18.0.16' (using password: YES)

portfolio, task-api, budget-api, bookstack and metabase all lost their database connections.

5.2 Root cause

-- on the new primary (mysql-replica)
SELECT user, host, plugin FROM mysql.user WHERE user IN ('portfolio_web','taskapp','budget_user','budgetapp');

Only portfolio_web existed, with the wrong password. In step 3.1 it had been set from an env var that looked right but wasn't:

MYSQL_PASSWORD=<…>          # this was analyst's password (MySQL's own container-init pair)
MYSQL_USER=analyst

The real value was in the portfolio app's own deployed env (BLOG_DB_USER=portfolio_web, BLOG_DB_PASSWORD=<…>). Every other app user (taskapp, budgetapp, bookstack, metabase_app, metabase_reader, opengym_app, portfolio_admin) had never been created on the new primary. The step 3.1 fix only covered the two users the drill needed, not the roughly 12 real app users.

5.3 Recovering the real credentials

Retyping wasn't an option. Most passwords weren't known, and the one hash that could be selected and pasted into CREATE USER … AS '<hash>' got corrupted by each shell layer (tabs, quotes, multi-byte characters):

ERROR 1827 (HY000): The password hash doesn't have the expected format.

So the real data was recovered from the step 4.1 backup instead:

mkdir -p /home/swe/mysql-recovery-data
docker run --rm \
  -v /home/swe/mysql-data-backup-pre-swap-20260822-2329.tar.gz:/backup.tar.gz:ro \
  -v /home/swe/mysql-recovery-data:/data alpine \
  sh -c 'tar xzf /backup.tar.gz -C /data'
docker run -d --name mysql-recovery -v /home/swe/mysql-recovery-data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=temp mysql:8.4

-- against the throwaway container: the ORIGINAL pre-wipe data
SELECT user, host, plugin FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user NOT IN ('root');

12 real app users found. Rather than retype each hash, the actual grant-table rows were dumped, byte-exact:

docker exec -e MYSQL_PWD='<recovery root pw>' mysql-recovery mysqldump \
  --no-create-info --skip-triggers --complete-insert -uroot \
  mysql user db --where="user in ('analyst','bookstack','budgetapp','metabase_app','metabase_reader','opengym_app','portfolio_admin','taskapp','portfolio_web')" \
  > /tmp/user_grants.sql

5.4 Replication broke twice while fixing it

First break. Loading the dump on the primary replicated down to mysql-secondary, which already had its own analyst user, auto-created by the fresh container's own MYSQL_USER/MYSQL_PASSWORD init variables:

[ERROR] Worker 1 failed executing transaction '...:23'; Could not execute
Write_rows event on table mysql.user; Duplicate entry '%-analyst' for
key 'user.PRIMARY', Error_code: 1062

The first instinct was to skip the failed GTID:

STOP REPLICA;
SET GTID_NEXT='518ccd6b-9bdc-11f1-849d-eec305a1821a:23';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;

That was a mistake. The dump loaded all 9 users in one multi-row INSERT, which is one transaction with one GTID. Skipping it skipped the other 8 users too, which surfaced as a second break a few statements later:

Error 'Operation ALTER USER failed for 'portfolio_web'@'%'' ... Error_code: MY-001396

Correct fix, once understood: don't skip. Remove the specific conflicting local row so the real transaction applies cleanly:

STOP REPLICA;
SET GLOBAL super_read_only = OFF;
DROP USER 'analyst'@'%';
SET GLOBAL super_read_only = ON;
START REPLICA;

More db/tables_priv mismatches kept appearing. After the second round of whack-a-mole the approach changed entirely.

5.5 Full clean resync (the actual fix)

docker exec -e MYSQL_PWD='<pw>' mysql-secondary mysql -uroot -e "STOP REPLICA; RESET REPLICA ALL;"

# authoritative snapshot of the CURRENT primary: app data + all real grants
docker exec -e MYSQL_PWD='<pw>' mysql-replica mysqldump --single-transaction --source-data=2 -uroot \
  --databases analytics bookstack_db budget_db metabase_app movielens opengym portfolio project1_jobs project3_perf sandbox_db todo_db \
  | gzip > /tmp/full_seed_final.sql.gz

docker exec -e MYSQL_PWD='<pw>' mysql-replica mysqldump --no-create-info --skip-triggers \
  --complete-insert --set-gtid-purged=OFF -uroot mysql user db tables_priv \
  --where="user in ('analyst','bookstack','budgetapp','metabase_app','metabase_reader','opengym_app','portfolio_admin','portfolio_web','taskapp','pipeline_writer','mysqld_exporter','repl')" \
  > /tmp/full_grants_final.sql

docker stop mysql-secondary; docker rm mysql-secondary
docker run --rm -v /var/lib/docker/mysql-data:/data alpine sh -c 'rm -rf /data/* /data/.[!.]*'
cd /home/swe/stack && docker compose up -d mysql
docker rename mysql mysql-secondary
docker network disconnect stack_default mysql-secondary
docker network connect    stack_default mysql-secondary

gunzip -c /tmp/full_seed_final.sql.gz | docker exec -i -e MYSQL_PWD='<pw>' mysql-secondary mysql --default-character-set=utf8mb4 -uroot

# remove the container's own auto-created analyst user BEFORE loading grants this time
docker exec -e MYSQL_PWD='<pw>' mysql-secondary mysql -uroot -e "DROP USER IF EXISTS 'analyst'@'%'; FLUSH PRIVILEGES;"
docker exec -i -e MYSQL_PWD='<pw>' mysql-secondary mysql -uroot mysql < /tmp/full_grants_final.sql
docker exec -e MYSQL_PWD='<pw>' mysql-secondary mysql -uroot -e "FLUSH PRIVILEGES;"

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql-replica', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '<generated>',
  SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

Result: Last_Errno: 0, Seconds_Behind_Source: 0, clean from a fresh baseline instead of patched history.

One more silent gap. portfolio_admin still had no table grants:

SHOW GRANTS FOR 'portfolio_admin'@'%';
-- GRANT USAGE ON *.* TO `portfolio_admin`@`%`      <- no actual table grants

Its grants are per-table (mysql.tables_priv), not per-database. The first grants dump only covered user and db. Re-dumped tables_priv and reapplied to both servers.

5.6 Verify before restarting anything

Rather than restart the containers and hope, every recovered credential was tested directly first:

docker run --rm --network stack_default -e MYSQL_PWD='<pw>' mysql:8.4 \
  mysql --get-server-public-key -h mysql -u portfolio_web -e 'SELECT 1;'
# repeated for portfolio_admin, taskapp, budgetapp, bookstack, metabase_app: all passed

Only then:

docker restart portfolio task-api budget-api bookstack metabase opengym-web-1 opengym-api-1

curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:3000/                # portfolio: 200
curl -s http://100.124.72.42:8000/health                                       # task-api: {"status":"ok"}
curl -s http://100.124.72.42:8001/health                                       # budget-api: {"status":"ok"}
curl -s http://127.0.0.1:3000/ | grep -o '<title>[^<]*</title>'                # real content, not an error page

Result: all five apps confirmed working, replication healthy, monitoring correctly labeled for the new roles.

Part 6: The scripted fallback

The manual drill above is a runbook. Two artifacts turn it into something that can run unattended.

6.1 The runbook (`failover-runbook.md`)

The five-step drill, with "tested result" blanks to fill from the run:

Stop the primary with docker kill, not a graceful stop.
Measure staleness at the moment of failure: Seconds_Behind_Source and Executed_Gtid_Set are the real data-loss window, not a theoretical one.
Promote: STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only = OFF; SET GLOBAL super_read_only = OFF;.
Repoint the application: check the app users exist on the replica before the drill.
Confirm a real write lands by running a real pipeline run, not only SELECT 1.

Plus a real-incident checklist: confirm the primary is actually down, not slow; check lag before promoting; promote and verify; communicate the cutover; and bring the old primary back only as a fresh replica, never "resume where it left off".

6.2 The watchdog (`failover-watchdog.sh`)

What it does: runs from cron. It probes the primary 5 times; if all fail, it lets the replica apply its relay log, fences the old primary, then promotes. It is dry-run by default (PROMOTE=0), and a lock stops overlapping runs.

# 1. replica must be alive and actually replicating, otherwise nothing to promote
rep_status=$(q "$REPLICA" "$REPLICA_PW" 'SHOW REPLICA STATUS\G' 2>&1) || { log "ALERT replica unreachable"; exit 1; }

# 2. probe the primary several times; one success means it is alive
for i in $(seq 1 "$CHECKS"); do
  if T=5 q "$PRIMARY" "$PRIMARY_PW" 'SELECT 1' >/dev/null 2>&1; then log "ok primary up"; exit 0; fi
  [ "$i" -lt "$CHECKS" ] && sleep "$INTERVAL"
done
log "ALERT primary $PRIMARY failed $CHECKS/$CHECKS probes"

# 3. let the replica apply what it already received; abort on timeout (a human decides)
retrieved=$(q "$REPLICA" "$REPLICA_PW" "SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status")
res=$(q "$REPLICA" "$REPLICA_PW" "SELECT WAIT_FOR_EXECUTED_GTID_SET('$retrieved', $APPLY_TIMEOUT)")
[ "$res" != "0" ] && { log "ALERT relay log not applied in ${APPLY_TIMEOUT}s, NOT promoting"; exit 2; }

[ "$PROMOTE" != "1" ] && { log "DRY RUN: would fence $PRIMARY and promote $REPLICA"; exit 0; }

# 4. fence the old primary (restart=no first, otherwise unless-stopped revives it)
docker update --restart=no "$PRIMARY" >/dev/null 2>&1
docker kill "$PRIMARY" >/dev/null 2>&1

# 5. promote; SET PERSIST so the writable state survives a restart
q "$REPLICA" "$REPLICA_PW" "STOP REPLICA; RESET REPLICA ALL; SET PERSIST super_read_only=OFF; SET PERSIST read_only=OFF;"

Limits, stated up front: worst-case outage about 5.5 minutes (5-minute cron plus about 30 s of probes). Fencing only works because both servers share one Docker host. Transactions the primary committed but the replica never received are lost; the script logs Executed_Gtid_Set at failure so the gap is measurable.

Result: superseded in 2026-10 by Orchestrator, which detects in about 5 s and promotes in under 1 s, with fail-closed fencing. The watchdog remains as the documented fallback.

Issues encountered, in order

#	When	Issue	Root cause	Fix
1	08-19	`docker exec -it … -p < file` fails	stdin can't be both a redirected file and an interactive prompt	`docker cp` + `source`, `MYSQL_PWD` instead of `-p`
2	08-19	Interactive prompts unreliable all session	tty/pipe interaction in this terminal	standardized on `MYSQL_PWD` everywhere
3	08-19	`mysqldump \| gzip` looked hung for 5 min	stuck at an invisible password prompt	`SHOW PROCESSLIST` to confirm, `kill -9` from a second terminal
4	08-19	Replication broke on the first write from another DB	seed scoped to one database, but the binlog carries every database's traffic	removed the filter, replicated the whole server
5	08-19	`DROP DATABASE` blocked during cleanup	replica already `super_read_only` from the earlier attempt	`SET GLOBAL super_read_only = OFF` first
6	08-19	`GTID_PURGED` conflict on restore	`RESET REPLICA ALL` doesn't clear `gtid_executed`	`RESET BINARY LOGS AND GTIDS`
7	08-20	No replica-lag metric at all	the primary's exporter can't see replica status	dedicated `mysqld-exporter-replica`
8	08-20	Replica exporter never came up	prepared on disk, never deployed	actually deployed it
9	08-20	Exporter `.my.cnf` was an empty directory	a missing bind-mount source becomes a directory	created the real file first, recreated the container
10	08-20	Exporter password mismatch	backslash stripped by `CREATE USER`, kept in `.my.cnf`	password without `\ ' "`
11	08-20	Exporter couldn't read MySQL 8.4 replica status	v0.15.1 sends the removed `SHOW SLAVE STATUS`	v0.16.0 (metric renamed `…_behind_source`)
12	08-20	Grafana rule showed No Data with healthy lag 0	threshold baked into the PromQL returned an empty result	threshold as a separate expression
13	08-20	First drill stalled at the repoint step	app users not on the replica; pipeline host hard-coded	paused deliberately instead of forcing it
14	08-22	Stalled again, for the documented reason	precondition never actually checked before that run	checked and fixed before the drill
15	08-22	Pipeline couldn't reconnect after promotion	`MYSQL_HOST = "mysql"` is a literal, ignoring the `DB_HOST` env var	repointed at the network layer (alias)
16	08-22	Exporters `Access denied` intermittently after the role swap	`caching_sha2_password` needs the RSA public key without TLS	`get-server-public-key=1` in `.my.cnf`
17	08-22	`mysql` resolved to two IPs after a rename	Docker keeps the old name registered	disconnect/reconnect from the network
18	08-22	Five apps down with `Access denied`	only 2 of ~12 app users existed on the promoted primary	recovered the exact users from the pre-wipe backup
19	08-22	`portfolio_web` denied even after "fixing" it	password taken from a different, same-looking env var	traced the real value from the app's deployed environment
20	08-22	Hand-built password hash rejected (`ERROR 1827`)	special characters mangled through shell-quoting layers	dumped the real grant rows instead of retyping
21	08-22	Replication broke (`Error 1062`, duplicate `analyst`)	container init had auto-created the same user	removed the conflicting local row
22	08-22	Skipping the GTID made it worse	the multi-row `INSERT` was one transaction, so 8 other users were skipped too	full clean resync instead of patching
23	08-22	`portfolio_admin` silently had no table grants	table-level grants live in `mysql.tables_priv`, not `mysql.db`	dumped `tables_priv` too

Lessons learned

Pausing a failed drill is a success, not a failure. Stopping at the first unsafe step is what made the 08-22 restart clean: the two open questions were specific, not rediscovered.
A fix scoped to "what this test needs" isn't "what the system needs". The drill needed 2 users; the permanent swap needed all 12, and that gap caused the outage.
Never hand-transcribe a password hash through shell layers. Dump the real row.
GTID-skip is not a generic unstick button. It skips the whole transaction, which may bundle far more than the one conflicting row.
A fresh container's init variables collide with replicated grants. Drop or account for them before loading external grants.
Verify credentials against the database before restarting apps. A 5-second SELECT 1 beats a second outage.
Write failures down the first time. The RSA-key error came back three days later on an unrelated system and was recognized immediately.
Test the real write path. SELECT 1 passing is not the same as the pipeline writing rows.