Sev1: MySQL split brain. Two servers accepted writes after a failover

#Symptoms

• Both the old and the new primary have read_only = OFF (or super_read_only = OFF).
• Replication from the new primary to the old one fails, or GTID sets do not line up.
• Users report missing or "flip-flopping" data: some orders visible only on one side.
• Often after a network partition: the failover manager promoted a replica while the old primary was still alive for some clients.

#Step 1: fence the old primary now (2 minutes)

Stop the bleeding first. On the old primary:

SET GLOBAL super_read_only = ON;     -- also sets read_only

If you cannot reach it over MySQL, stop the service or isolate it at the network level (security group, firewall). Then make sure the proxy or DNS points only at the new primary:

-- ProxySQL admin
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE hostname='db-old';
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Caution

Do not restart replication between the two servers yet. Starting the old primary as a replica of the new one while it has errant transactions will either fail or, worse, apply changes in a way that hides conflicts.

#Step 2: measure the divergence with GTIDs

On both servers:

SELECT @@server_uuid, @@global.gtid_executed\G

Errant transactions on the old primary (executed there, not on the new primary):

-- run on either server, pasting both sets
SELECT GTID_SUBTRACT('<old_primary_gtid_executed>', '<new_primary_gtid_executed>') AS only_on_old;
SELECT GTID_SUBTRACT('<new_primary_gtid_executed>', '<old_primary_gtid_executed>') AS only_on_new;

only_on_old lists the transactions written to the old primary after failover (and possibly a few that never replicated before it). Note the time window.

#Step 3: extract what was written on the old primary

Find the binlogs covering that window and decode the errant transactions:

mysql -h db-old -e "SHOW BINARY LOGS;"
mysqlbinlog --read-from-remote-server -h db-old -u repl -p \
  --include-gtids='3e11fa47-71ca-11e1-9e33-c80aa9429562:1001-1450' \
  --base64-output=DECODE-ROWS --verbose binlog.000231 binlog.000232 > errant_changes.txt

--verbose with row-based logging shows each changed row as pseudo-SQL (### INSERT INTO shop.orders SET @1=...). Count by table:

grep -E "^### (INSERT|UPDATE|DELETE)" errant_changes.txt | awk '{print $2, $4}' | sort | uniq -c | sort -rn

#Step 4: reconcile

There is no automatic merge. Decide per table with the application owners:

Load the old-side rows into staging tables on the new primary for review, rather than replaying binlogs blindly:

mysqldump -h db-old --single-transaction --no-create-info --where="created_at >= '2026-10-29 10:02:00'" \
  shop orders order_lines > old_side_rows.sql

Load into a recovery schema on the new primary, compare, then INSERT ... SELECT what you decide to keep.

#Step 5: rebuild the old primary as a replica

Its data has diverged; do not try to "fix" it in place. Rebuild from the new primary:

-- on the old primary, after taking a backup of it for the investigation
-- using the clone plugin (MySQL 8.0.17+)
SET GLOBAL clone_valid_donor_list = 'db-new:3306';
CLONE INSTANCE FROM 'clone_user'@'db-new':3306 IDENTIFIED BY '...';

After the clone restarts the server:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='db-new', SOURCE_USER='repl', SOURCE_PASSWORD='...',
  SOURCE_AUTO_POSITION=1, SOURCE_SSL=1;
START REPLICA;
SHOW REPLICA STATUS\G

Or restore from a fresh backup of the new primary (XtraBackup / MySQL Enterprise Backup).

#Step 6: confirm

• Only one server has super_read_only = OFF.
• Rebuilt node replicating with Seconds_Behind_Source near 0 and no errors.
• Reconciled data signed off by application owners; list of unrecoverable conflicts documented.

#Prevent it next time

• super_read_only = ON in my.cnf on every server; the failover tool turns it off only on the promoted node. A restarted old primary then comes back read-only.
• Fencing in the failover procedure: block the old primary at the proxy and network before promoting.
• Avoid relying on DNS changes alone for writers; use ProxySQL, a VIP with fencing, or a managed service's endpoint.
• Consider MySQL InnoDB Cluster / Group Replication in single-primary mode, which prevents writes on a minority partition.
• Alert when more than one server reports read_only = 0 in the same replication topology.