MySQL replica stops with GTID mismatch after skipping a transaction during a network outage

Environment

MySQL 8.0.33 primary (master) and asynchronous replica, GTID_MODE=ON, enforce_gtid_consistency=ON, network outage occurred between primary and replica during heavy writes (multi-statement transactions and some non-deterministic functions used).

Symptoms

After reconnecting, the replica IO/SQL threads error out with messages referring to GTID inconsistencies and 'Found event for a different server UUID' or statements that have already been executed. Attempts to SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 allow the slave to start briefly but the error returns when the next conflicting transaction is processed; replication cannot be resumed reliably.

Observations

SHOW SLAVE STATUS reports Last_SQL_Error referencing a specific GTID and 'Could not find event' style errors. The primary's binlog and GTID_PURGED/GTID_EXECUTED sets differ from the replica's executed set. There were no intentional writes on the replica, and the replica was read-only prior to the outage. No physical disk corruption observed.

Already attempted

Tried STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE to bypass single offending events (temporary relief). Examined relay logs and tried mysqlbinlog to extract problematic statements, but the GTID ordering and presence of non-deterministic statements made manual correction risky. Avoided using RESET SLAVE ALL due to worry about losing replication positioning.

Question

How can I safely recover a GTID-based replica that refuses to apply certain transactions after a network outage and a manual skip? Is a full reclone from the primary required, or are there safer reconciliation steps to rebuild GTID consistency without data loss?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely 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%

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.

Suggested Fixes

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.

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.

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.