✏️ Explanatory Question
Level: Hard — The ultimate disaster-recovery skill; combines a backup with the binary log to roll forward to any exact moment.
Scenario: At 3:15 PM, a developer runs DROP TABLE orders; on production by mistake. Your last full backup is from 2:00 AM. You cannot lose the 13 hours of orders since then. Walk through exactly how you recover the database to 3:14 PM — the instant before the drop.
Point-in-Time Recovery = restore the last full backup, then replay the binary log on top of it, stopping just before the bad statement. The backup gives you the baseline; the binlog rolls you forward event-by-event to any chosen moment.
$$ \text{Recovered state} = \text{Backup (2 AM)} + \text{Binlog events (2 AM} \rightarrow \text{3:14 PM)} $$
Prerequisites (must be true BEFORE disaster): You need (1) a recent full backup, and (2) binary logging enabled and preserved since that backup. PITR is impossible if binlogs were purged or logging was off. This is why binlog retention is a DBA lifeline.
DROP TABLE in the binlog.# Restore the logical backup taken at 2:00 AM
mysql -u root -p < full_backup_0200.sql
# The backup was taken with --master-data=2, so it recorded the
# binlog file + position where the backup ended, e.g.:
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=1547;# Scan the binlog to locate the accidental DROP and note its position/time
mysqlbinlog mysql-bin.000042 mysql-bin.000043 | grep -n -i "DROP TABLE"
# Example finding:
# # at 998234
# #260802 15:15:07 server id 1 ...
# DROP TABLE orders
# -> the bad statement starts at position 998234 (3:15:07 PM)# Replay all events from the backup's position up to JUST BEFORE the DROP.
# Option A: stop at the exact position of the DROP (position-based)
mysqlbinlog \
--start-position=1547 \
--stop-position=998234 \
mysql-bin.000042 mysql-bin.000043 \
| mysql -u root -p
# Option B: stop by timestamp (recover up to 3:14:59 PM)
mysqlbinlog \
--start-datetime="2026-08-02 02:00:00" \
--stop-datetime="2026-08-02 15:15:06" \
mysql-bin.000042 mysql-bin.000043 \
| mysql -u root -p Position-based is safer than time-based: Multiple statements can share the same second, so a timestamp cutoff might include or exclude one you did not intend. Using the exact --stop-position of the bad statement guarantees you replay everything up to — but not including — the DROP.
-- Confirm the orders table is back and has the latest rows
SELECT COUNT(*) FROM orders;
SELECT MAX(created_at) FROM orders; -- should be ~3:14 PM, not 2 AM# With GTIDs enabled, you can exclude the exact bad transaction by its GTID
mysqlbinlog --exclude-gtids='SERVER_UUID:1234' \
mysql-bin.000042 mysql-bin.000043 | mysql -u root -p
# Cleaner than positions when using GTID replicationInterviewer follow-up: "How do you reduce the recovery time (RTO) next time?" → Take more frequent backups (shrinks the binlog replay window), use incremental physical backups, keep a delayed replica (e.g., 1 hour behind) so you can promote it instead of a full restore, and practice restore drills so PITR is routine, not a panicked first attempt during a real outage.