High-Availability Replication + Failover — Full Technical Writeup

Project: A — High-Availability Replication + Automated Failover Timeline: 2026-08-19 (built) → 2026-08-20 (verified, first drill attempt, paused) → 2026-08-22 (full drill, then a real production incident) Environment: swe-2 (single Docker host, stack_default bridge network)

This is the complete technical record, in the order it actually happened: setting up GTID-based replication from scratch, the debugging that went into getting it monitored and healthy, a first failover attempt that surfaced real gaps and was deliberately paused rather than pushed through, and — a session later — the completed drill followed by a genuine multi-app production incident during a follow-up change. Every command and query below is one that was actually run; every issue is one that actually happened, not a hypothetical.

---

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

0.1 Plan

Primary → replica MySQL replication on swe-2 via a second mysql container (mysql-replica), GTID-based rather than classic binlog-position (survives topology changes without manually tracking log coordinates), seeded from a real backup/restore. Caveat agreed up front: this is one physical host running two containers, not two machines — it proves replication mechanics and failover procedure, not resilience to real hardware failure.

Files prepared: docker-compose.replication.yml (the mysql-replica service), sql/01_create_replication_user.sql, sql/02_start_replica.sql, failover-runbook.md.

0.2 Create the least-privilege replication user

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

Issue: docker exec -it ... -p < file doesn't work. stdin can't be both a redirected file and an interactive password prompt at once. Fix: docker cp the SQL file into the container, then run it via source, authenticating with MYSQL_PWD instead of -p:

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

Issue: interactive -p prompts were unreliable in this terminal all session — sometimes Access denied ... using password: NO even with a password typed, sometimes a docker exec -it ... | grep combination just hung with no visible prompt. Root-caused as a tty/pipe interaction problem specific to this session, not a MySQL issue. Fixed by standardizing every command on MYSQL_PWD (env var via docker exec -e) instead of -p, for the rest of the project — no prompt to lose, no shell-quoting risk from an inline -p'password', and it doesn't land in shell history.

Issue: a docker exec -it mysqldump | gzip appeared stuck for 5 minutes. Diagnosed via:

SHOW PROCESSLIST;

— no mysqldump connection at all, proof the command never reached the server; it was stuck at an invisible password prompt buried by the -it + piped-stdout interaction. Ctrl+C didn't respond in that terminal; recovered from a second terminal (containers share the host kernel, so ps aux sees the process):

ps aux | grep mysqldump
kill -9 <pid>

0.3 First seed attempt — scoped wrong

# 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

Replication broke immediately:

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

Root cause: the primary's binlog carries transactions from every database on the instance — budget_db, todo_db, metabase_app, etc. from real ongoing app traffic — not just the one the replica was scoped to. The SQL thread died the moment a transaction from any other database appeared.

Decision: replicate the whole server instead of one database — closer to genuine failover coverage for everything on the host, not just one pipeline's target table.

SHOW DATABASES;

Gave the real scope: analytics, bookstack_db, budget_db, metabase_app, movielens, portfolio, project1_jobs, project3_perf, sandbox_db, todo_db — MySQL's own mysql/sys/information_schema/ performance_schema system schemas deliberately excluded (copying mysql would overwrite the replica's own separately-generated root credentials).

0.4 Re-seed, whole-server

Issue: cleanup hit super_read_only. The earlier (aborted) run of 02_start_replica.sql had already locked the replica read-only as designed, which then blocked DROP DATABASE during cleanup:

SET GLOBAL super_read_only = OFF;

Issue: GTID conflict on restore.

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/config state, not gtid_executed history — the aborted first attempt's history survived. Fixed with a true clean slate (MySQL 8.4's renamed RESET MASTER):

RESET BINARY LOGS AND GTIDS;

Then the real, whole-server 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 CHANGE MASTER) — since SOURCE_AUTO_POSITION=1 is used next, GTID auto-positioning figures out the resume point automatically.

Note, not an issue: the restore took a while and that was expected — project3_perf alone is ~1,000,567 rows. Confirmed it was genuinely working, not hung, via:

SHOW PROCESSLIST;
-- active INSERT INTO jobs in progress

0.5 Start replication

-- on the REPLICA
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '<password>',
  SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;

GET_SOURCE_PUBLIC_KEY=1 up front here — already known from an earlier, unrelated issue (Metabase's JDBC driver needed the equivalent allowPublicKeyRetrieval=true against this same MySQL 8.4 instance) that caching_sha2_password without TLS needs an explicit RSA public-key exchange. This foresight didn't carry over to every later tool that talked to these servers — see §2.3, where the same failure mode reappeared for the monitoring exporters and had to be re-diagnosed.

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

0.6 Replication lag had no metric at all

mysql_slave_status_seconds_behind_master — nothing, not even zero. Root cause: the existing mysqld-exporter only connects to the primary, and SHOW REPLICA STATUS run against a primary is empty (a primary isn't a replica of anything) — lag is a property of the replica's own connection state, so it needs its own dedicated exporter instance. Added mysqld-exporter-replica (port 9105) plus a matching least-privilege user script and a new Prometheus scrape job (mysql_replica) — prepared, not yet deployed at this point.

Session paused here: seed/replication start not yet confirmed healthy, replica exporter not yet deployed, no failover drill attempted yet.

---

Part 0.5 — Verification, monitoring fixes, first drill attempt (2026-08-20)

0.5.1 Deploying the replica exporter — four real bugs in a row

• Never actually deployed. Prometheus had no mysql_replica job at all — the service was prepared on disk on 2026-08-19 but never copied to swe-2 or brought up. Deployed it.
• Bind-mount became a directory. ./mysqld-exporter-replica/.my.cnf didn't exist as a file on the host yet, so Docker silently created it as an empty directory on first container start — a classic Docker gotcha. Fixed by creating the real file with actual content first, then recreating the container.
• Password mismatch after fixing the mount. The exporter's password contained a backslash (\d8); CREATE USER ... IDENTIFIED BY silently stripped it as an unrecognized escape sequence server-side, while .my.cnf kept the literal backslash — the two no longer matched. Reset to a password without backslashes/quotes.
• Real version-compatibility bug. mysqld_exporter v0.15.1 (the version already pinned for the primary's exporter) issues the old, now-removed SHOW SLAVE STATUS syntax — MySQL 8.4 rejects it outright. Confirmed as a known upstream issue, fixed in v0.16.0. Bumped the image tag. v0.16.0 also renamed the metric: mysql_slave_status_seconds_behind_master → ..._behind_source.

0.5.2 Grafana alert rule — two passes

First pass: fixed just the renamed metric. Still showed No Data. Second pass, structural: the query had > 30 baked directly into the PromQL (unlike the other 6 rules in the group), so a genuinely healthy 0 lag returned an empty result — and Grafana reports an empty result as No Data, not Normal. Restructured to match the other rules: raw metric in the query, Is above 30 as a separate Threshold expression. Confirmed Normal afterward with real data (0).

Replication itself had been healthy the entire time (replica_io_running=1, replica_sql_running=1, last_errno=0) — every issue in this section was monitoring-side, not replication-side.

0.5.3 First failover attempt — steps 1–3 clean, step 4 blocked

docker kill mysql   # hard failure, on purpose

-- on the replica
STOP REPLICA;
RESET REPLICA ALL;
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 pre-kill state

Steps 1–3 worked cleanly. Step 4 (repoint the application) is where it stopped:

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 this session paused (queued but not yet run): whether pipeline_writer/portfolio_web actually existed on the replica (they hadn't been seeded — the whole-server restore in §0.3–0.4 deliberately excluded MySQL's own mysql system schema), and whether the DAG's host value came from an env var or was hardcoded in arbeitnow_pipeline_dag.py.

0.5.4 Restore to pre-drill state

Since the DAG write failed, no new data had actually landed on the promoted replica — primary and replica had stayed in sync the whole time, which made restoration simple:

docker start mysql

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

GTID position survived RESET REPLICA ALL (it clears connection config, not gtid_executed), so replication resumed cleanly with no reseed needed. Confirmed Replica_IO_Running: Yes / Replica_SQL_Running: Yes / Seconds_Behind_Source: 0 — back to the exact pre-drill topology. Explicitly paused here, not abandoned: "let's pending the failover for now."

---

Part 1 — Completing the drill (2026-08-22)

Picked up exactly where §0.5.3 left off — the two open questions from that session, checked properly this time, before the drill instead of during it.

1.1 Precondition check, done right this time

SELECT user, host FROM mysql.user WHERE user IN ('pipeline_writer','portfolio_web');

Confirmed: neither existed on the replica — exactly the gap flagged in §0.5.3. Fixed directly this time, before touching the primary:

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 copied from wrong env var — see §3.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;

For the second open question — DAG host config:

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

Confirmed hardcoded, not env-driven. This meant no amount of config repointing would fix step 4 — the fix has to happen at the network layer instead (see §1.3).

1.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,

Confirms the replica detected the outage correctly and gives the exact data-loss boundary (GTID :1-12).

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

1.3 Repoint via network alias, not app config

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

1.4 Verify a real write lands

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

docker exec airflow-scheduler airflow dags trigger arbeitnow_pipeline

SELECT MAX(date_posted), COUNT(*) FROM project1_jobs.jobs;
-- 2026-08-22, 4150

Drill result: passed. 175 new rows landed on the promoted server with zero application code changes — the fix lived entirely at the infrastructure layer, which is the right place for it under real time-pressure.

---

Part 2 — Making the promotion permanent (where the incident started)

Follow-up decision: rather than reverting to the pre-drill topology like §0.5.4, swap roles permanently — mysql-replica stays primary, the original mysql rejoins as the new replica. This is where nearly every subsequent issue originated.

2.1 Backup first

Direct host filesystem access to the bind-mounted data directory needed elevated permissions not available in this session (sudo required a TTY). Worked around it with a throwaway container mounting 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 later (§3.3).

2.2 Wipe and reseed the old primary as a 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 reinit

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
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY '<generated>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- on the old primary (now the new 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;

Verified Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: 0 — looked clean. It wasn't; the real gap (§3) hadn't surfaced yet because no app had tried to connect since the swap.

2.3 Re-point monitoring to match

Swapped the two exporters' .my.cnf host= values so job labels (mysqld-exporter = "primary", mysqld-exporter-replica = "replica") stayed semantically correct after the role swap.

Issue: caching_sha2_password RSA-key-retrieval failure, again. After swapping targets, both exporters authenticated inconsistently — plain mysql CLI worked, 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 of issue as §0.5 (Metabase's JDBC driver, and the GET_SOURCE_PUBLIC_KEY=1 already used for replication itself) — recognized immediately from prior history rather than re-diagnosed from scratch. Fix: add get-server-public-key=1 to both .my.cnf files, restart both exporters.

Issue: DNS collision from the container rename. docker rename mysql mysql-secondary doesn't stop Docker's embedded DNS from momentarily resolving mysql to both the new primary and the renamed container:

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

Fixed by forcing a network re-registration:

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

Issue: replica exporter pointed at the wrong host after the rename. Its config said host=mysql, which — after the fix above — now correctly means "the primary," not "the replica" as originally intended. Repointed explicitly to host=mysql-secondary.

---

Part 3 — The incident: five apps down

3.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.

3.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 — and with the wrong password. During the §1.1 precondition fix I'd sourced its password from an environment variable that looked right but wasn't:

MYSQL_PASSWORD=dc0724bc7bb209b5823be4e04eb4fc2b6a0a0406   # this is analyst's password
MYSQL_USER=analyst                                         # (MySQL's own container-init pair)

The real value, found in the portfolio app's actual deployed env:

BLOG_DB_USER=portfolio_web
BLOG_DB_PASSWORD=f8dfd690906878362f5c8a839458f2957a6363c4

Every other app's user (taskapp, budgetapp, bookstack, metabase_app, metabase_reader, opengym_app, portfolio_admin) had never been created on the new primary at all — the §1.1 fix only covered the two users the failover drill itself needed, not the full set of ~12 real app users, since the rest weren't relevant to that specific test.

3.3 Recovering the authoritative credentials

Manually retyping passwords wasn't an option — most weren't known, and even the one hash I could pull via SELECT and paste into a new CREATE USER ... IDENTIFIED WITH ... AS '<hash>' got corrupted every time it passed through another layer of shell quoting (it contained tabs, quotes, and multi-byte Unicode characters):

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

Switched strategy: recovered the real original data instead of transcribing it, using the §2.1 backup.

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 recovery 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 reading and retyping each authentication_string, dumped the actual grant-table rows — byte-exact, no transcription:

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

3.4 Replication broke — twice — while fixing this

First break. Loading the dump directly onto the primary got binlogged normally and replicated down to mysql-secondary — which already had its own auto-created analyst user, from the fresh container's own MYSQL_USER/MYSQL_PASSWORD init env vars:

[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

First instinct was to skip the failed GTID transaction:

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

This was a mistake. The dump loaded all 9 users in one multi-row INSERT — one transaction, one GTID. Skipping it skipped the other 8 users' inserts too, not just the conflicting analyst row, which surfaced as a second break a few statements later:

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

(portfolio_web no longer existed locally to alter — it had been part of the skipped transaction.)

Correct fix, once understood: don't skip — remove the specific conflicting local row so the real transaction can apply cleanly:

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

Further db/tables_priv mismatches kept surfacing incrementally after this. After the second round of whack-a-mole, switched strategy entirely rather than keep patching:

3.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 CURRENT primary state — 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-together history.

Also needed: portfolio_admin's grants are per-table (mysql.tables_priv), not per-database — the first grants dump only covered user/db and silently missed them:

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

Re-dumped tables_priv specifically and reapplied to both servers.

3.6 Verify before restarting anything

Rather than restart the app containers and hope, tested every recovered credential 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

All five apps confirmed working; replication confirmed healthy; monitoring confirmed correctly labeled for the new roles.

---

Issues encountered — full list, chronological

#	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 bug 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 first data write from another DB	Seed scoped to project1_jobs only via --replicate-do-db, but binlog carries every DB's traffic	Removed the filter, replicated whole server
5	08-19	DROP DATABASE blocked during cleanup	Replica already locked super_read_only from 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 history	RESET BINARY LOGS AND GTIDS for a true clean slate
7	08-20	No replica-lag metric at all	Primary's own exporter can't see SHOW REPLICA STATUS (it's not a replica)	Dedicated mysqld-exporter-replica instance
8	08-20	Replica exporter service never came up	Prepared on disk, never deployed to swe-2	Actually deployed it
9	08-20	Exporter's .my.cnf was an empty directory	File didn't exist on host before first bind-mount	Created the real file first, recreated container
10	08-20	Exporter password mismatch	Backslash in password silently stripped by CREATE USER, kept literal in .my.cnf	Reset to a password without \/'/"
11	08-20	Exporter couldn't read MySQL 8.4 replica status	mysqld_exporter v0.15.1 issues removed SHOW SLAVE STATUS syntax	Bumped to v0.16.0 (also renamed the metric)
12	08-20	Grafana rule showed No Data despite healthy 0 lag	Threshold baked into PromQL meant a 0 returned an empty result	Moved threshold to a separate expression
13	08-20	Drill stalled at step 4	DAG couldn't reach mysql after promotion; app users not seeded on replica	Paused deliberately rather than force through
14	08-22	Failover stalled again — for the documented reason this time	Precondition never actually checked before this run	Checked and fixed before the drill
15	08-22	DAG couldn't reconnect even after promotion	MYSQL_HOST hardcoded as a literal string, ignoring the DB_HOST env var that was actually set	Repointed at the network layer (DNS alias), not app config
16	08-22	Exporters Access Denied intermittently after role swap	caching_sha2_password needs RSA public-key exchange without TLS	get-server-public-key=1 in .my.cnf
17	08-22	mysql hostname resolved to two IPs after rename	Docker auto-registers a container's own name; rename doesn't clear the old registration	Network disconnect/reconnect to force re-registration
18	08-22	5 apps down with Access Denied	Only 2 of ~12 real app users ever created on the promoted primary	Recovered exact users from pre-wipe backup
19	08-22	portfolio_web denied even after being "fixed"	Password sourced from the wrong same-shaped env var (analyst's, not portfolio_web's)	Traced real value from the app's actual deployed env
20	08-22	Manual hash reconstruction rejected by MySQL	Special characters corrupted across shell-quoting layers	Dumped real grant-table rows instead of retyping hashes
21	08-22	Replication broke (Error 1062, duplicate analyst)	Container-init auto-created a colliding local user before the replicated INSERT arrived	Removed the conflicting local row, let the real transaction apply
22	08-22	GTID-skip made it worse	Skipping a multi-row-INSERT transaction skips all rows in it	Switched to full clean resync instead of incremental patching
23	08-22	portfolio_admin grants silently missing	Table-level grants live in mysql.tables_priv, not mysql.db	Dumped tables_priv explicitly

---

Lessons learned

1. A failed drill that gets paused instead of forced through is a success, not a failure. §0.5.3 stopped at the first sign it wasn't safe to keep going, and that decision is exactly what made the 08-22 restart clean — the two open questions were known and specific, not rediscovered from scratch.
2. A precondition fix scoped to "what this specific test needs" is not the same as "what the system needs." The drill's fix (2 users) was correct for the drill; treating it as sufficient for a permanent role swap (which needs all ~12) is what caused the outage.
3. Never hand-transcribe a password hash through multiple shell layers. The moment a hash contains anything beyond [A-Za-z0-9], dump the real row instead of reading-and-retyping it.
4. GTID-skip is not a generic "unstick replication" button. It skips the entire transaction, which may bundle far more than the one row that actually conflicted.
5. A fresh container's own init env vars will collide with anything replicated in afterward that touches the same username. Drop or account for these before loading external grants onto a freshly initialized instance.
6. Verify credentials against the database directly before restarting app containers. A 5-second SELECT 1 check confirms a fix landed before touching anything user-facing.
7. A previously-documented failure signature (the RSA public-key issue, first hit with Metabase, deliberately worked around during the original 02_start_replica.sql setup) reappeared on an unrelated system three days later and was recognized immediately — the value of writing things down the first time.