Understanding Recovery Point Objective (RPO)

Recovery Point Objective (RPO) defines the maximum acceptable amount of data loss measured in time. If your RPO is 1 hour, you can tolerate losing up to 1 hour of data during a disaster. If your RPO is 15 minutes, you need backup systems that capture changes at least every 15 minutes.

RPO vs RTO

Don't confuse RPO with RTO (Recovery Time Objective). RTO is how long it takes to restore service after a failure. RPO is how much data you might lose. You might have an RTO of 2 hours (acceptable downtime) but an RPO of 15 minutes (maximum data loss).

How MySQL Binary Logs Enable PITR

MySQL binary logs (binlogs) record every data modification in your database - INSERT, UPDATE, DELETE, and DDL statements. These logs are the foundation of point-in-time recovery.

Binary Log Basics

When binary logging is enabled, MySQL writes every change to sequential log files (mysql-bin.000001, mysql-bin.000002, etc.). Each entry includes the exact timestamp and position, allowing you to replay changes up to any specific moment.

-- Check if binary logging is enabled
SHOW VARIABLES LIKE 'log_bin';

-- View current binary log files
SHOW BINARY LOGS;

-- See binary log events
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

Enabling Binary Logs

Binary logging is configured in your MySQL configuration file (my.cnf or my.ini):

[mysqld]
log_bin = /var/log/mysql/mysql-bin
server_id = 1
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 100M

The PITR Recovery Process

Point-in-time recovery is a two-step process: first restore your base backup, then replay binary logs up to your target time.

Step 1: Restore Base Backup

Start by restoring your most recent full backup that occurred BEFORE your target recovery time. For physical backups, this means copying files back. For logical backups, this means importing the SQL dump.

# For physical backup (Mariabackup)
mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql

# For logical backup (mysqldump)
mysql -u root -p < backup.sql

Step 2: Replay Binary Logs

After restoring the base backup, use mysqlbinlog to replay binary logs from the backup's position to your target recovery time.

# Replay binary logs up to specific time
mysqlbinlog --stop-datetime="2025-01-15 15:46:00" \
  /var/log/mysql/mysql-bin.000042 \
  /var/log/mysql/mysql-bin.000043 \
  | mysql -u root -p

# Or stop at specific position
mysqlbinlog --stop-position=12345 \
  /var/log/mysql/mysql-bin.000043 \
  | mysql -u root -p

Identifying the Recovery Point

To recover to a specific point, you need to know what happened when. Use mysqlbinlog to examine log contents:

# View events around a specific time
mysqlbinlog --start-datetime="2025-01-15 15:40:00" \
  --stop-datetime="2025-01-15 15:50:00" \
  /var/log/mysql/mysql-bin.000043 | less

# Look for the problematic statement (e.g., DROP TABLE, DELETE)
# Note the position BEFORE that statement

Common PITR Scenarios

Scenario 1: Accidental Data Deletion

A developer runs DELETE FROM orders WHERE status = 'pending' without a WHERE clause at 2:30 PM, deleting all orders. Your last backup was at midnight.

1. Restore midnight backup to a recovery server (not production)
2. Use mysqlbinlog to find the exact position of the DELETE statement
3. Replay binary logs from midnight to just before the DELETE
4. Export the recovered orders table
5. Import into production or use INSERT...SELECT to restore data

Scenario 2: Database Corruption

Your production server experiences storage failure at 4:15 PM, corrupting the database. Last backup was at midnight.

1. Stop MySQL and preserve binary logs (copy to safe location)
2. Restore midnight backup
3. Replay binary logs from midnight to 4:14 PM (just before corruption)
4. Verify data integrity
5. Resume production

Scenario 3: Ransomware Attack

Malware encrypts your database at 11:20 AM. You detect it at 11:45 AM.

1. Isolate affected systems
2. Restore from backup prior to infection (ideally off-site backup)
3. Replay binary logs up to 11:19 AM (before encryption began)
4. Verify no malicious changes exist in the recovered data
5. Implement additional security before resuming production

Binary Log Management Best Practices

Binary logs are essential for PITR but require careful management. They grow continuously and can fill your disk if not maintained.

Retention Policy

Keep binary logs long enough to cover your backup retention plus recovery time. If you keep 7 days of backups, retain at least 8-10 days of binary logs.

-- MySQL 8.0+
SET GLOBAL binlog_expire_logs_seconds = 864000; -- 10 days

-- MySQL 5.7 and earlier
SET GLOBAL expire_logs_days = 10;

Binary Log Backup

• Copy binary logs to remote storage immediately after rotation
• Keep binary logs on different storage than your database
• Verify binary log backups are readable and complete
• Monitor binary log disk usage and set alerts

Monitoring Binary Log Health

-- Check binary log status
SHOW MASTER STATUS;

-- Check disk usage
SHOW BINARY LOGS;

-- Verify binary log writing is active
SHOW VARIABLES LIKE 'log_bin';

PITR with Physical Backups

Physical backup tools like Mariabackup and XtraBackup record the binary log position at backup time, making PITR integration seamless.

Capturing Binary Log Position

After preparing a physical backup, the binary log coordinates are stored in xtrabackup_binlog_info:

# After mariabackup --prepare
cat /backup/full/xtrabackup_binlog_info
# Output: mysql-bin.000042    12345

Complete PITR with Mariabackup

# 1. Restore physical backup
systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

# 2. Get binary log position from backup
cat /backup/full/xtrabackup_binlog_info
# Shows: mysql-bin.000042    12345

# 3. Replay binary logs from that position to target time
mysqlbinlog --start-position=12345 \
  --stop-datetime="2025-01-15 15:46:00" \
  /var/log/mysql/mysql-bin.000042 \
  /var/log/mysql/mysql-bin.000043 \
  | mysql -u root -p

How DBCalm Simplifies PITR

DBCalm automates point-in-time recovery by combining 15-minute incremental backups with simplified recovery workflows.

15-Minute Recovery Points

With incremental backups every 15 minutes, DBCalm ensures your maximum data loss is 15 minutes - without requiring manual binary log management. Each backup captures a consistent state that can be restored directly.

Simplified Recovery

• Select any 15-minute recovery point from the dashboard
• No manual binary log identification or replay required
• Automated backup preparation and verification
• Restore to original location or separate recovery server

This approach eliminates the complexity of traditional PITR while maintaining tight recovery point objectives. You get the benefits of point-in-time recovery without managing binary logs separately.

Frequently Asked Questions

Related Guides

• Physical vs Logical Backups
• Incremental Backup Explained
• MySQL Backup Best Practices