How would you automate MySQL failover so that promoting a replica to primary doesn't itself cause data loss or split-brain, and what does the automation have to check before it's allowed to act?

Think of a company where the CEO suddenly stops answering calls, and the leadership team needs to decide whether to promote a deputy. If only one assistant's phone call attempt failing is used as proof the CEO is unreachable, that assistant might just have a broken phone line, while the CEO is actually fine and everyone else can still reach them — promoting a deputy in that case creates two people simultaneously issuing directives to the company. The safe process requires several independent people confirming the CEO is truly unreachable, and once a deputy is promoted, actively revoking the original CEO's signing authority so they can't issue a conflicting order if they resurface unexpectedly.

MySQL replication automation tools like Orchestrator or MHA (Master High Availability) exist specifically to solve the failover decision safely, because a naive failover script that simply promotes 'a' replica the instant a health check fails is dangerous in exactly the way the analogy describes: a monitoring node's own network partition can make a perfectly healthy primary look unreachable, and if the automation acts on that single vantage point, it can promote a replica while the original primary is still alive and accepting writes from clients that haven't yet noticed the failover — this is split-brain, where two nodes both believe they're the authoritative primary.

Correctly designed failover automation requires multiple independent observers to agree the primary is actually down (cross-checking from several hosts in different network zones, not trusting a single health check), and uses GTID (Global Transaction Identifier, a unique ID assigned to every transaction that lets MySQL compare exactly how caught-up two servers are) to determine which replica has replicated the most complete set of transactions and is therefore the safest promotion candidate — promoting a replica that's behind means silently losing the delta of transactions the old primary had that never made it to the new primary. Before completing the promotion, the automation must fence the old primary: setting it read-only, revoking its ability to serve as a replication source, and ideally blocking it at the network or proxy layer, specifically so that if it comes back online after a transient issue (not an actual crash, just a network blip), it cannot accept writes that would silently diverge from the new primary's state.

In production, the fencing step is frequently the one teams skip when building a first version of failover automation, because it's the part with no positive-path benefit during normal operation — it only matters in the exact failure scenario it's designed for, so it's easy to under-test. Engineers validate failover automation with actual game-day exercises that simulate a network partition (not just killing the MySQL process, which is a cleaner and easier-to-handle failure than a network split), specifically because a hard process crash and a network partition produce very different symptoms to the monitoring system, and automation tuned only against the easy case (process crash) can behave dangerously against the harder, more realistic case (partition).

The gotcha that catches teams who built solid automation and still hit an incident: GTID-based promotion correctly picks the most caught-up replica, but if application-side connection routing (via a proxy, DNS, or service discovery) isn't updated atomically with the promotion, some application instances can keep writing to the old primary for a window after the database-level failover completed, because they cached a connection or resolved an old endpoint before the failover began — meaning the database layer did everything correctly, but the failure now happens one layer up, in connection routing, which many teams don't test as part of their failover drill because they consider the database automation itself to be 'the failover.'