ERROR

MySQL GTID Replication: Fix ER_GTID_PURGED Error During Security Hardening

Quick Fix Summary

TL;DR

Stop replica, reset master, set GTID_PURGED to match source, restart replication.

The replica is trying to fetch binary logs that have been purged from the source, breaking the GTID-based replication chain.

Diagnosis & Causes

Binary logs were purged on the source after a security audit or cleanup.

Replica was offline for an extended period, missing transactions that were later purged.

Recovery Steps

Step 1: Verify GTID State on Source and Replica

Check the current GTID_EXECUTED and GTID_PURGED sets on both servers to confirm the mismatch.

sql

# On the SOURCE server:
SHOW MASTER STATUS\G
SELECT @@GLOBAL.GTID_EXECUTED;
SELECT @@GLOBAL.GTID_PURGED;
# On the REPLICA server:
SHOW SLAVE STATUS\G
SELECT @@GLOBAL.GTID_EXECUTED;
SELECT @@GLOBAL.GTID_PURGED;

Step 2: Stop Replication and Reset Master on Replica

Halt replication and clear the replica's binary logs and GTID state. This is a destructive step; ensure you have the correct GTID_PURGED value from the source ready.

sql

STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;

Step 3: Set GTID_PURGED on Replica to Match Source

Manually set the replica's GTID_PURGED to the GTID_EXECUTED value from the source. This tells the replica which transactions it should consider as already applied.

sql

SET @@GLOBAL.GTID_PURGED = '<source_gtid_executed_value>';

Step 4: Reconfigure and Start Replication

Re-point the replica to the source using the correct GTID auto-positioning and restart replication.

sql

CHANGE MASTER TO MASTER_HOST='<source_host>', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_AUTO_POSITION=1;
START SLAVE;

Step 5: Verify Replication Health

Confirm that replication is running without errors and that the replica's GTID_EXECUTED is catching up to the source.

sql

SHOW SLAVE STATUS\G
# Check for 'Slave_IO_Running: Yes', 'Slave_SQL_Running: Yes', and an empty 'Last_Error'.
SELECT @@GLOBAL.GTID_EXECUTED; # Compare with source.

Architect's Pro Tip

"This error often surfaces after a security hardening script runs 'PURGE BINARY LOGS' or 'RESET MASTER' on the source without checking replica lag. Always check `SHOW SLAVE HOSTS;` on the source before purging logs."

Frequently Asked Questions

Will this procedure cause data loss on the replica?

No. The procedure resets the replica's *replication metadata*, not its user data. The `SET GTID_PURGED` command ensures it knows which transactions are already in its dataset.

What if I don't know the exact GTID_EXECUTED value from the source when the replica stopped?

You must use the source's *current* GTID_EXECUTED. This means the replica will be missing all transactions that occurred between its stop time and the purge event. You may need to restore the replica from a recent backup of the source to regain consistency.

Related MySQL Guides

ER_CON_COUNT_ERROR