Recuperarse de un DROP / DELETE erróneo con una réplica diferida (SOURCE_DELAY + UNTIL)

Comando

-- 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;

Qué hace

Hace que la réplica aplique los eventos con un retraso fijo; cuando un DROP / DELETE erróneo afecta al origen, se avanza la réplica hasta justo antes de esa transacción y se recuperan desde ella los datos perdidos.

Cuándo usarlo

Como capa de recuperación rápida frente a errores humanos en bases de datos grandes donde restaurar desde un backup llevaría horas.

Precaución

Cuando el coordinator está en OFF, los datos están en el estado justo anterior a la operación errónea; vuelque la tabla afectada con mysqldump y cárguela en el origen. Después vuelva a poner la réplica en modo diferido; si aplica también la transacción errónea, los datos se pierden aquí también. Una réplica diferida no debe ser candidata a failover (exclúyala en Orchestrator / Router / ProxySQL). Los relay logs se acumulan durante todo el retraso; dimensione el disco en consecuencia.