Point-in-time recovery with binlogs: find and skip the bad transaction

Command

# 1) Find the bad event's GTID and position (narrow by time range)
mysqlbinlog --base64-output=DECODE-ROWS -vv --start-datetime='2026-10-08 20:00:00' --stop-datetime='2026-10-08 20:30:00' /var/lib/mysql/binlog.000003 | grep -B12 -iE '^(DROP|TRUNCATE)' | grep -E 'GTID_NEXT|^# at|^#[0-9]{6}|^DROP|^TRUNCATE'

# 2) Restore the backup on a separate server (the dump sets GTID_PURGED)
zcat /backup/full_2026-10-08_0200.sql.gz | mysql -h restore-db -u root -p

# 3a) Replay all binlogs skipping only the bad transaction
mysqlbinlog --exclude-gtids='af6bc361-c353-11f1-8f12-f69e63ad6c0e:23' /binlogs/binlog.000003 /binlogs/binlog.000004 | mysql -h restore-db -u root -p
# 3b) Or stop right before the bad event's GTID event
mysqlbinlog --stop-position=537205 /binlogs/binlog.000003 | mysql -h restore-db -u root -p

What it does

After restoring the backup, replays binlogs in order to bring the database to the desired point. With GTID on, transactions already in the backup are skipped automatically; --exclude-gtids removes just the bad operation and keeps every change after it.

When to use it

After a bad DROP / DELETE / UPDATE, to recover without losing changes made between the backup and the incident.

Caution

Restore on a separate server, not production; verify, then move only the affected table. --start/--stop-datetime are interpreted in the time zone of the machine running mysqlbinlog. --stop-position applies only to the last file given; for multiple files replay the earlier files fully in a separate command. --exclude-gtids is safe only for an independent operation; if later transactions write to the dropped table, they fail too. The required binlogs must not be purged on the source; keep binlog retention longer than at least one backup interval.