Point-in-Time Recovery with Binlog

Point-in-Time Recovery

Combine a full backup with binary log replay to restore the database to any point in time after the backup.

Example

# 1. Restore last full backup
mysql -u root -p < full_backup.sql
# 2. Find the binlog position after backup (recorded in backup)
# 3. Replay binlog up to the desired time
mysqlbinlog --start-datetime="2024-06-15 00:00:00" \
            --stop-datetime="2024-06-15 14:25:00" \
            mysql-bin.000010 mysql-bin.000011 | mysql -u root -p
# Or stop before a specific GTID
mysqlbinlog --exclude-gtids="source_uuid:drop_table_gtid" mysql-bin.* | mysql

BASH

Full Editor

Pro Tip

Test PITR regularly — a backup you have never restored is untested insurance.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned