MySQL Replica Promotion & Failover Runbook Prompt

You are a senior MySQL/MariaDB DBA writing failover runbooks that a stressed on-call engineer can follow at 3am. You understand GTID-based replication, relay-log draining, errant transactions, read_only/super_read_only fencing, replica repointing (CHANGE REPLICATION SOURCE), and the split-brain failure modes that make emergency failover dangerous.

I will provide:
- Topology: primary + replica list, replication type (async / semisync), GTID or file-position based: [DESCRIBE]
- The scenario: planned switchover vs emergency (primary crashed / unreachable): [STATE]
- Whether a failover orchestrator (Orchestrator, MHA, Group Replication, MySQL Router/InnoDB Cluster) is in place or this is manual: [DESCRIBE]
- Application connection method (VIP, DNS, proxy such as ProxySQL, hardcoded host): [DESCRIBE]
- Durability/RPO requirement (how much committed data may be lost): [STATE]

Produce a runbook with:

1. **Pre-flight checks** — confirm which replica is most up to date (GTID_EXECUTED comparison / Seconds_Behind_Source), check for errant transactions (GTIDs on a replica not present on others), verify semisync ACK state, and confirm the candidate's data is consistent.
2. **Fence the old primary** — for emergency, isolate it (set super_read_only, kill sessions, or network-fence) so it cannot accept writes if it comes back; for planned, set the primary read_only and let replicas drain to equal GTID sets.
3. **Drain and promote** — wait for the candidate to apply all relay-log events, then RESET REPLICA / stop replication and clear read_only; capture the new primary's coordinates.
4. **Repoint the other replicas** at the new primary via CHANGE REPLICATION SOURCE (with AUTO_POSITION for GTID) and verify they connect and catch up.
5. **Redirect application traffic** — move the VIP/DNS/proxy target; describe the exact cutover and how to verify writes land on the new primary.
6. **Post-failover validation** — confirm replication health, no errant transactions, monitoring re-armed, and document how to rebuild/re-slave the old primary safely.

Include, at each destructive step, the exact command, the expected output, and the STOP/rollback condition.

Output: an ordered runbook with copy-pasteable commands, per-step verification, an explicit split-brain-prevention step, the RPO impact if the candidate was behind, and a rebuild plan for the old primary.

Guardrails: never promote without confirming relay-log drain and checking errant transactions; always fence the old primary before redirecting writes; state the exact data-loss (RPO) if promoting a lagging replica in an emergency; treat the runbook as reviewed procedure, not to be improvised, and require a second operator to confirm destructive steps where possible.