DBAtlas · Database operations atlas

Recover from a bad DROP / DELETE with a delayed replica (SOURCE_DELAY + UNTIL)

MySQLReplication

Command

-- Setup: the replica applies events one hour behind
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
START REPLICA;

-- During the incident: stop the applier (the receiver keeps fetching binlogs)
STOP REPLICA SQL_THREAD;

-- Find the bad transaction's GTID in the source binlog, apply up to just before it
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
START REPLICA SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'af6bc361-c353-11f1-8f12-f69e63ad6c0e:17';
SELECT SERVICE_STATE FROM performance_schema.replication_applier_status_by_coordinator;

What it does

Makes the replica apply events a fixed time behind; when a bad DROP / DELETE hits the source, you roll the replica forward to just before that transaction and recover the lost data from it.

When to use it

As a fast recovery layer against human error on large databases where restoring from backup would take hours.

Caution

When the coordinator is OFF, the data is in the state right before the bad operation; dump the affected table with mysqldump and load it into the source. Afterwards put the replica back into delayed mode; if it applies the bad transaction too, the data is gone here as well. A delayed replica must not be a failover candidate (exclude it in Orchestrator / Router / ProxySQL). Relay logs accumulate for the whole delay; size the disk accordingly.

Open in DBAtlas →

Related