MySQL Failover Strategies Without Data Loss

1. Why failover planning is more than an emergency button

A MySQL failover is the process of a replica taking over the role of a failed or deliberately retired primary. Many teams treat failover as a binary event that either works or does not, yet the quality of a failover is really a matter of degree: how many transactions are lost, how long is the application down, and how many manual steps are needed before normal operation resumes.

These three dimensions, data loss, downtime, and manual effort, depend directly on preparation. A MySQL failover executed for the first time in a real emergency, without GTID, without semi-synchronous protection and without prior testing, is a high-risk gamble. The following sections show how to systematically prepare planned and unplanned failover so no improvisation is needed when it counts.

2. Planned failover: controlled role swap

Planned MySQL failover happens when the current primary is to be retired for known reasons, such as maintenance work, a hardware upgrade, or a data center move. The decisive advantage over unplanned failover: the old primary is still reachable at the time of the switch and can be put into a safe state in a controlled manner before the new primary takes over write traffic.

The correct procedure starts by putting the current primary into read-only mode, so no new writes are accepted. Then you wait until the chosen replica has fully caught up on its replication lag, which can be reliably verified by comparing GTID sets. Only once Retrieved_Gtid_Set and Executed_Gtid_Set on the target replica match the final state of the old primary is a switch guaranteed to happen without any data loss.

-- Planned failover: put the current primary into read-only mode
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

-- Wait until the target replica has caught up completely
-- Run on the target replica, using the primary's GTID set as reference
SELECT WAIT_FOR_EXECUTED_GTID_SET('a1b2c3d4-...:1-98421', 30) AS caught_up;
-- Returns 0 once fully caught up, or 1 on timeout

-- On the promoted replica: stop replication and open for writes
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- Repoint remaining replicas to the new primary using GTID auto-positioning
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db-new-primary.internal',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

3. Unplanned failover: failure without warning

Unplanned MySQL failover happens when the primary fails unexpectedly, for example due to hardware failure, a kernel panic, or a network outage. Here you lack the ability to put the primary into read-only mode beforehand, and in the worst case, transactions confirmed on the primary have not yet been transferred to any replica. This is exactly the scenario semi-synchronous replication protects against, by confirming commits only after at least one replica has acknowledged receipt of the transaction.

With unplanned failover, you must first determine which replica has the most advanced GTID position to minimize data loss. Comparing the Executed_Gtid_Set values of all remaining replicas shows which candidate was closest to the failed primary. That replica is promoted to the new primary, and all other replicas are then repointed to it. Performed manually under time pressure, this comparison is error prone, which is why tools like Orchestrator automate this step.

-- Run on each remaining replica to compare GTID progress after a primary outage
SELECT @@GLOBAL.gtid_executed AS executed_gtid_set;

-- Determine which replica is missing transactions relative to another
-- An empty result means the first set fully contains the second (more advanced)
SELECT GTID_SUBTRACT(
  'a1b2c3d4-...:1-98421',   -- gtid_executed on replica A
  'a1b2c3d4-...:1-98376'    -- gtid_executed on replica B
) AS missing_on_b;

-- The replica with the most complete GTID set becomes the promotion candidate

4. GTID-based failover in detail

GTID is the technical foundation that makes MySQL failover reliably automatable in the first place. Without GTID, after a failover you would have to manually determine the correct binlog position of the new primary for every remaining replica, an error-prone process that, if positioned incorrectly, either duplicates or skips transactions. With GTID enabled, CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1 is enough, and the server calculates the correct starting position itself based on the already-executed transaction set.

A special case in GTID-based failover is so-called errant transactions: transactions that were executed on a replica but never existed on the original primary, for example due to an accidental write to a supposedly read-only replica. If that replica is later promoted to the new primary, other replicas can enter an error state when trying to replicate this unknown transaction, because their GTID set does not include it. Regular checks with gtid_purged and strict read-only mode on all non-primary nodes reliably prevent this problem.

5. Orchestrator: automated failover detection

Orchestrator is a specialized tool for topology detection and automated execution of MySQL failover, continuously monitoring the state of all known MySQL instances. If Orchestrator detects that the primary is unreachable across multiple consecutive checks, while replicas simultaneously confirm the outage to rule out network problems on the Orchestrator host itself, it automatically starts the failover process and selects the most suitable replica as the new primary based on GTID progress, data freshness, and configured preferences.

Configuration lets you explicitly mark certain replicas as preferred failover candidates or exclude them from promotion, for example replicas in a different geographic region with higher latency. Orchestrator also supports hooks that run before and after a failover, for example to update DNS records, reconfigure MySQL Router, or send an alert to the operations team. This automation reduces the time between failure and restored write capability from potentially minutes of manual work to a few seconds.

# orchestrator.conf.json (excerpt): key failover-relevant settings
{
  "RecoveryPeriodBlockSeconds": 3600,
  "PreventCrossDataCenterMasterFailover": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "PostMasterFailoverProcesses": [
    "/usr/local/bin/update-dns.sh {failureClusterName} {successorHost}",
    "/usr/local/bin/notify-slack.sh 'Failover completed: {successorHost} is now primary'"
  ]
}

# Manual graceful failover trigger via orchestrator CLI
orchestrator -c graceful-master-takeover \
  -alias production-cluster \
  -d db-replica-2.internal:3306

6. MHA as an alternative for older topologies

MHA, Master High Availability Manager, was for a long time the standard tool for automated MySQL failover, before GTID and Orchestrator saw wide adoption. MHA also works with position-based replication without GTID, by reconciling binlog position differences between replicas over SSH during a failover and replaying missing events directly between replicas before the new primary goes active.

For new setups, MHA is in most cases no longer the first choice, because its development is less active than Orchestrator's and because GTID-based approaches are more robust and simpler to operate. In existing environments with older MySQL versions or without a GTID migration, however, MHA remains a functioning, well-documented option, especially if a team already has operational experience with the tool and no GTID migration is planned in the near term.

; app1.cnf: MHA application configuration (excerpt)
[server default]
user = mha
password = strong-password-here
ssh_user = root
repl_user = repl
repl_password = strong-password-here
ping_interval = 3

[server1]
hostname = db-primary.internal
candidate_master = 1

[server2]
hostname = db-replica-1.internal
candidate_master = 1

[server3]
hostname = db-replica-2.internal
no_master = 1

; Trigger a manual failover check
; masterha_check_repl --conf=/etc/masterha/app1.cnf
; masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=dead

7. Minimizing data loss: semi-sync as the foundation

No failover tool, whether Orchestrator or MHA, can restore data that never arrived on a replica. That is why semi-synchronous replication is the actual foundation that makes MySQL failover without data loss possible in the first place. With rpl_semi_sync_source_enabled turned on and a reasonably low timeout, the primary guarantees that every confirmed transaction is already in the relay log of at least one replica before the client receives a success response.

For maximum safety, rpl_semi_sync_source_wait_for_replica_count can be configured on newer MySQL versions to require more than one replica to confirm before a commit is considered complete. That increases safety against the simultaneous failure of the primary and the single confirming replica, but comes at the cost of higher commit latency. The balance between data safety and latency must be deliberately set per application, there is no universally correct setting.

8. Safely testing failover without endangering production

The most reliable way to validate a MySQL failover setup is to run it regularly under controlled conditions, instead of hoping it works the first time a real failure occurs. A proven practice is a monthly or quarterly planned failover in a staging environment built to resemble production, including realistic write load from load generators during the test.

For production-like tests without a real outage, a controlled planned failover during a less critical time window, for example outside peak usage hours, combined with full monitoring of downtime and the number of affected requests, is a good fit. Chaos engineering approaches that deliberately cut network connections to the primary, for example via iptables rules instead of an actual server crash, let you realistically simulate unplanned failover without damaging physical hardware or irrecoverably losing data.

#!/usr/bin/env bash
# simulate-primary-outage.sh: cut network traffic to the primary for a chaos test
set -euo pipefail

PRIMARY_IP="10.0.0.1"

echo "[CHAOS] Blocking all traffic to/from $PRIMARY_IP"
iptables -A INPUT -s "$PRIMARY_IP" -j DROP
iptables -A OUTPUT -d "$PRIMARY_IP" -j DROP

echo "[CHAOS] Failure injected, watch Orchestrator detect and promote a replica"
sleep 60

echo "[CHAOS] Restoring network access to $PRIMARY_IP"
iptables -D INPUT -s "$PRIMARY_IP" -j DROP
iptables -D OUTPUT -d "$PRIMARY_IP" -j DROP

9. Orchestrator vs. MHA compared

Both tools automate MySQL failover, but differ considerably in architecture, prerequisites, and maintenance effort. The table below compares the key differences.

For new projects, Orchestrator combined with GTID is the clearly recommended foundation for automated MySQL failover. MHA remains relevant for legacy environments where a GTID migration is not realistic in the near term, or where established operational knowledge of MHA already exists that should not be discarded without good reason.

10. Summary

A robust MySQL failover strategy rests on three pillars: semi-synchronous replication to prevent data loss at the source, GTID to make the switchover technically clean and automatable, and a tool like Orchestrator to speed up detection and execution when it matters. Planned failover with prior read-only mode guarantees zero loss, unplanned failover minimizes loss through careful selection of the most advanced replica.

The difference between a failover plan on paper and one that actually works in a real emergency lies in regular testing. Chaos engineering approaches and planned failover drills in staging environments expose gaps before they become a problem in production. Combining these elements consistently turns failover from a black box into a plannable, repeatable operational process.

11. FAQ: MySQL Failover