Recovering just before an accidental deletion requires a consistent backup and an intact binary-log chain. Select an event boundary, replay on an isolated instance and validate before returning anything to production.
Scope and prerequisites
This guide targets MySQL 8.4; older versions can use different status statements. Read the backup’s associated binlog coordinates or GTID metadata. PITR is not guaranteed without a consistent base, complete logs and any required decryption keys. Preserve a protected copy of the damaged state first.
1. Collect evidence
Inventory log files in order and confirm there are no gaps. Normalize the incident timestamp and inspect events using DECODE-ROWS with verbose output. Time narrows the search; the final stop boundary should be the destructive transaction’s starting position so recovery does not replay a partial transaction.
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;
-- Inspect events offline, not on the production destination:
-- mysqlbinlog --base64-output=DECODE-ROWS -vv binlog.000123 | less
2. Make a controlled change
Restore the full backup onto an isolated, access-restricted instance. The replay example is valid only if positions 120 and 4567 come from your backup and event analysis. Do not replay DECODE-ROWS output; use normal mysqlbinlog output. Preserve file ordering and a single replay connection when multiple files are involved.
# EXAMPLE ONLY: substitute verified transaction-boundary positions.
# recovery.example.test must be an isolated recovery server.
mysqlbinlog --start-position=120 --stop-position=4567 binlog.000123 > replay.sql
mysql --host=recovery.example.test --user=recovery --password --binary-mode < replay.sql
3. Verify the outcome
Validate row counts, recent business transactions, referential integrity and application workflows. Absence of the deletion is insufficient; earlier legitimate changes must also exist. Prevent external application writes during recovery. Website files and database must match a consistent point; database-only recovery cannot restore missing uploaded files.
-- On the isolated recovered instance, validate known business records.
SELECT COUNT(*) FROM example_db.orders;
SELECT id, created_at FROM example_db.orders ORDER BY id DESC LIMIT 10;
Rollback and prevention
Do not overwrite the production origin before testing. Cutover requires a write freeze, connection plan, rollback path and measured RPO/RTO. Retain logs longer than the backup interval and keep off-host copies. A real restoration drill must prove that the entire backup-and-log chain is usable.
Technical references
For related checks in this workflow, see Read MySQL EXPLAIN ANALYZE and Choose Useful Indexes and Handle Linux Read-Only Filesystems Without Risking Data.
