All technical articles

MySQL Point-in-Time Recovery with Binary Logs

A full backup does not contain changes made after it was taken.

MySQL Point-in-Time Recovery with Binary Logs

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.