1
Diagnostic step 1
Do not attempt ad-hoc GTID manipulations (like changing GTID_EXECUTED sets) unless you have a tested rollback plan and a recent backup. Those operations are high risk for silent data corruption.
FULL AI ANALYSIS
MySQL replica stops with GTID mismatch after skipping a transaction during a network outage
Generated Oct 3, 2026, 12:35:03 AM
Analysis summary
GTID replication enforces a strict transaction identity and ordering. Skipping transactions or applying manual changes on the replica can cause divergence: the replica's GTID_EXECUTED set no longer matches the primary's; non-deterministic or partially-applied transactions can lead to errors that are unsafe to skip without risking data inconsistency. In most cases the safest, least-risky path to restore a clean, consistent replica is to reinitialize the replica from a fresh backup of the primary (logical or physical) or to use a physical copy tool (xtrabackup) to resync state while preserving GTIDs.
Possible causes
Manual skipping of transactions created a GTID gap and the replica's executed GTID set no longer matches primary, causing subsequent GTID-based validation failures.
85%Non-deterministic or non-transactional statements executed on the primary produced different effects or were lost during outage and cannot be reapplied deterministically on replica.
75%Relay log corruption or partial writes during the outage left the relay in an inconsistent state.
30%Replica was briefly writable and received changes, producing conflicting data/GUIDs before failing back to slave mode.
10%Suggested fixes
2
Diagnostic step 2
If the replication divergence is small and you can identify the exact missing GTIDs on the replica, you can use mysqlbinlog on the primary to extract the missing transactions and apply them manually in a transaction on the replica. This requires careful verification that those statements are deterministic and safe to replay. Keep full backups first.
3
Diagnostic step 3
The most reliable remediation is to provision a fresh replica: take a new backup from the primary (mysqldump --single-transaction --master-data=2 or a physical backup with Percona XtraBackup preserving GTIDs), restore it to the replica, and configure replication afresh. This avoids manual GTID surgery and guarantees consistency.
4
Diagnostic step 4
If full re-cloning is not possible immediately, capture the current state (file system snapshot or logical export) as a backup, and then use mysqlbinlog/SHOW BINLOG EVENTS to find and confirm the offending GTID(s). Get DBA assistance before applying manual fixes.
5
Diagnostic step 5
To prevent recurrence: ensure replicas are set read_only, monitor replication status and network health, and consider semi-synchronous replication for critical setups. If using GTIDs, avoid manual skips and prepare automated procedures for controlled failover. If you are not comfortable performing a fresh restore or manual GTID repair, escalate to a DBA with experience in GTID-based recovery—these steps can cause irreversible data divergence if done incorrectly.