MySQL binary logs and point-in-time recovery

A nightly dump only lets you go back to last night. If someone runs a bad DELETE at 15:42, you lose everything written between the dump and that moment unless you also have the binary log. Point-in-time recovery (PITR) means: restore the last full backup, then replay the binary log from the backup’s position up to just before the bad statement.

What the binary log is

The binary log (binlog) is a numbered series of files such as binlog.000041 plus an index file. It records every change to data and schema as events. Replication uses the same log: a replica reads the source’s binlog and applies it.

• Binary logging is on by default in MySQL 8.0 and later (log_bin=ON), except when the data directory was initialized manually with --initialize/--initialize-insecure.
• Without --log-bin=name, files are called binlog.NNNNNN. Setting a base name explicitly avoids surprises when the host name changes.
• sync_binlog=1 (the default) flushes the log at every write: safest, slowest.

SHOW VARIABLES LIKE 'log_bin%';
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;   -- current file and position (MySQL 8.4 name)

The recovery chain

PITR only works if nothing is missing between the backup and the incident. You need: a full backup that records its binlog coordinates, and every binlog file from that point on. mysqldump --source-data=2 writes those coordinates into the dump as a commented CHANGE REPLICATION SOURCE TO line; --flush-logs starts a fresh binlog file at the backup so the boundary is easy to find.

mysqldump -u root -p --all-databases --single-transaction \
  --flush-logs --source-data=2 > full.sql

grep -m1 'CHANGE REPLICATION SOURCE TO' full.sql

Keep binlogs on a different disk (or copy them off the host) for at least as long as your oldest backup you might restore from. Remove old files with PURGE BINARY LOGS TO 'binlog.000040' rather than deleting them by hand, because PURGE also updates the index file.

Finding the bad event

mysqlbinlog turns binlog files into readable SQL. Use a time window only to locate the event; the manual warns that replaying by datetime can miss events and recommends positions.

mysqlbinlog --start-datetime="2026-03-11 15:40:00" \
            --stop-datetime="2026-03-11 15:45:00" --verbose \
            /var/lib/mysql/binlog.000042 | grep -C 15 "DELETE FROM"

The output shows # at NNN markers. If the bad statement sits between # at 232 and # at 355, it starts after position 232 and the log is at 355 once it ends.

Replaying around it

Warning: do this on a restored copy or after taking the server out of service; replaying into a live database with ongoing writes produces a mix of old and new data. First restore the full backup, then apply the binlog in two parts that skip the bad event:

mysql -u root -p < full.sql

mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/binlog.000042 \
  | mysql -u root -p

mysqlbinlog --start-position=355 /var/lib/mysql/binlog.000042 \
  | mysql -u root -p

If several files are involved, pass them to one mysqlbinlog invocation (mysqlbinlog binlog.000042 binlog.000043 | mysql ...) so that temporary tables and multi-file transactions are handled in one session. If the output contains NUL bytes, run the mysql client with --binary-mode.

Things that trip people up

• The start position must be the position recorded in your backup, not the first event in the file.
• Do not rely on the position InnoDB prints at startup after a restore; the manual says to take it from your backup tool.
• GTID-enabled servers add GTID information to each event. Check the mysqlbinlog reference for GTID-related options before replaying into a server that already has those GTIDs.
• Practise the whole procedure on a test server before you need it.

Official documentation

• MySQL 8.4: The binary log
• MySQL 8.4: Point-in-time recovery using binary logs
• MySQL 8.4: Point-in-time recovery using event positions