A nightly backup restores your database to last night. But if someone runs DELETE FROM orders without a WHERE at 3 p.m., you want everything up to 2:59 p.m., not a day of lost orders. Point-in-time recovery (PITR) combines a full backup with MySQL’s binary logs to replay changes up to any moment you choose.

How it works

  1. A full backup captures the database at time T0, together with the binary log position at that moment.
  2. The binary log (binlog) records every change after T0.
  3. To recover: restore the full backup, then replay the binlogs from that position up to just before the mistake.

1. Enable and keep binary logs

MySQL 8.0 and later enable binary logging by default. Check it, and set the retention:

SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOGS;

In /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
server_id                  = 1
log_bin                    = /var/lib/mysql/binlog
binlog_format              = ROW
binlog_expire_logs_seconds = 1209600     # 14 days
sync_binlog                = 1
sudo systemctl restart mysql

Keep binlogs at least as long as the interval between full backups, and preferably longer. On MariaDB, the equivalent retention setting is expire_logs_days (or binlog_expire_logs_seconds in newer versions). Binlogs need disk space, so monitor it. See the df command.

2. Take full backups that record the binlog position

With mysqldump (InnoDB, consistent without locking):

mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 --all-databases | gzip > /backup/full-$(date +%F).sql.gz

--source-data=2 (called --master-data=2 in older versions) writes the binlog file and position as a comment at the top of the dump:

zcat /backup/full-2026-11-04.sql.gz | grep -m1 'CHANGE REPLICATION SOURCE\|CHANGE MASTER'
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000042', SOURCE_LOG_POS=157;

For large databases, physical backups with Percona XtraBackup are much faster, and record the position in xtrabackup_binlog_info. See mysqldump backup and restore and XtraBackup.

3. Copy binlogs off the server

If the server dies, binlogs on its disk die with it. Copy them regularly (or stream them continuously):

# continuous streaming to a backup host
mysqlbinlog --read-from-remote-server --host=db.example.com --user=backup -p \
  --raw --stop-never binlog.000042 --result-file=/backup/binlogs/

Or sync the binlog files with rsync or restic every few minutes. See restic backups.

4. Find the moment to stop

Decode the binlogs around the incident:

sudo mysqlbinlog --base64-output=decode-rows -v \
  --start-datetime="2026-11-04 14:50:00" --stop-datetime="2026-11-04 15:10:00" \
  /var/lib/mysql/binlog.000045 | less

Search for the bad statement (DELETE FROM orders), and note the position (# at 81234) just before it. Positions are more precise than timestamps, since many transactions can share one second.

5. Recover

Work on a separate server or instance if possible, so you can check the result before touching production.

# 1. restore the full backup
zcat /backup/full-2026-11-04.sql.gz | mysql

# 2. replay binlogs from the backup position up to just before the mistake
mysqlbinlog --start-position=157 /var/lib/mysql/binlog.000042 \
  /var/lib/mysql/binlog.000043 /var/lib/mysql/binlog.000044 | mysql
mysqlbinlog --stop-position=81234 /var/lib/mysql/binlog.000045 | mysql

Note that --start-position applies to the first file listed. You can also skip the single bad transaction and replay everything after it (--start-position after the bad event, on the last file), keeping later valid changes.

For GTID-based setups, use --skip-gtids when replaying into a fresh server, or --exclude-gtids to skip a specific transaction.

6. Verify and switch over

  • Check row counts and recent records in the affected tables.
  • If you recovered on a separate instance, export just the affected tables back to production, or switch the application to the recovered server.
  • Write down what happened, and how long recovery took.

Test it regularly

A recovery procedure you’ve never practiced will fail when you need it. Every few months, restore last night’s backup plus binlogs onto a test VPS, to a chosen time, and time the whole process.

Quick reference

Step Command
Check binlogs SHOW BINARY LOGS;
Retention binlog_expire_logs_seconds
Full backup with position mysqldump --single-transaction --source-data=2
Inspect events mysqlbinlog --base64-output=decode-rows -v
Replay to a point mysqlbinlog --stop-position=N … | mysql
Off-server copies mysqlbinlog --read-from-remote-server --raw --stop-never

Related: database backup automation and MySQL replication.

  • MySQL
  • Backup
  • Database