MySQL binary logs are the foundation of point-in-time recovery. Combined with a full backup, binlogs let you recover to any point between backups.
How Binlog PITR Works
text
Timeline:
Sun 02:00 Full backup taken
Mon 14:37 Accidental DROP TABLE
Recovery:
1. Restore Sunday full backup
2. Replay binlogs from Sun 02:00 → Mon 14:36
3. Database is back to 1 minute before the accidentEnable and Configure Binary Logs
ini
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800 # 7 days
max_binlog_size = 100M
sync_binlog = 1List Binary Log Files
sql
SHOW BINARY LOGS;
SHOW MASTER STATUS\GPoint-in-Time Recovery
bash
# Step 1: Restore full backup
mysql -u root -p < full_backup_sunday.sql
# Step 2: Find the binlog position of the DROP TABLE
mysqlbinlog --start-datetime='2025-07-07 02:00:00' \
mysql-bin.000042 mysql-bin.000043 | grep -i 'drop table'
# Step 3: Replay binlogs up to just before the accident
mysqlbinlog --start-datetime='2025-07-07 02:00:00' \
--stop-datetime='2025-07-07 14:36:00' \
mysql-bin.000042 mysql-bin.000043 | mysql -u root -pmysqlbinlog with GTID (MySQL 8.0)
bash
# Exclude specific GTID range (e.g., the DROP TABLE transaction)
mysqlbinlog --include-gtids='server-uuid:1-1000' \
mysql-bin.000042 | mysql -u root -pAutomate Binlog Backup to S3
bash
#!/bin/bash
# Run every 15 minutes via cron
BINLOG_DIR=/var/log/mysql
S3_BUCKET=s3://my-db-backups/binlogs
# Sync new binlogs to S3
aws s3 sync $BINLOG_DIR $S3_BUCKET \
--exclude '*' --include 'mysql-bin.*'Key Takeaways
- Enable
sync_binlog = 1for durability — without it binlogs may be lost on crash - Sync binlogs to S3 every 15 minutes to achieve RPO < 15 minutes
- Use
--stop-datetimewithmysqlbinlogto replay up to just before an accident - GTID mode makes PITR more precise — you can exclude specific transactions by GTID range
JusDB Can Help
Binlog-based PITR is a critical but often untested recovery capability. JusDB can implement and validate your MySQL point-in-time recovery process.