MySQL Binlog-Based Point-in-Time Recovery

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

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 accident

Enable and Configure Binary Logs

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # 7 days
max_binlog_size = 100M
sync_binlog = 1

List Binary Log Files

SHOW BINARY LOGS;
SHOW MASTER STATUS\G

Point-in-Time Recovery

# 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 -p

mysqlbinlog with GTID (MySQL 8.0)

# Exclude specific GTID range (e.g., the DROP TABLE transaction)
mysqlbinlog --include-gtids='server-uuid:1-1000' \
  mysql-bin.000042 | mysql -u root -p

Automate Binlog Backup to S3

#!/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.*'

A Recoverable PITR Runbook

PITR succeeds only when the full backup and binary-log stream form one continuous history. Record the backup's starting coordinates or GTID set inside the backup artifact, preserve every required log from that boundary onward, and record the server version, time zone, character set, and encryption state. A timestamp alone is not a durable recovery boundary: clocks can be wrong, multiple transactions can commit within the same second, and an event may span the apparent incident time. Use date filters to narrow the search, inspect decoded events, and choose an exact event position whenever possible.

Use Version-Correct Status Commands

On MySQL 8.4, use SHOW BINARY LOG STATUS; SHOW MASTER STATUS was removed. SHOW BINARY LOGS remains the inventory command. Verify log_bin, server_uuid, gtid_mode, binlog_row_image, retention, and the latest archived file before declaring a backup recoverable. MySQL 8.4 enables row-based binary logging by default and deprecates binlog_format, so configuration examples copied from older 8.0 releases must be checked against the deployed version.

SHOW VARIABLES WHERE Variable_name IN ('log_bin','gtid_mode','binlog_row_image','binlog_expire_logs_seconds');
SHOW BINARY LOG STATUS\G
SHOW BINARY LOGS;

Archive Logs as a Stream

Copying the database log directory on a timer is weaker than using the supported protocol and can capture an active file at an awkward boundary. MySQL documents mysqlbinlog --read-from-remote-server --raw --stop-never for a continuous binary-log backup. Give the process a unique --connection-server-id, supervise it, and alert when it disconnects because mysqlbinlog does not reconnect automatically. Replicate completed raw files to durable, encrypted object storage, retain checksums and file sizes, and monitor archive lag. Coordinate server-side expiration with the maximum expected replica lag, archive outage, and recovery window; never purge a file merely because its nominal age has passed.

Restore Away from Production First

Provision an isolated server at a compatible version, prevent applications from connecting, restore the full backup, and confirm its recorded boundary. Render the relevant logs with mysqlbinlog --base64-output=DECODE-ROWS --verbose for investigation. Replay from the backup position to a reviewed --stop-position, not directly onto the damaged production instance. If only a date is known, generate a candidate stream with --stop-datetime, inspect the final transaction, then convert the decision to positions. Preserve transaction boundaries: stopping in the middle of a multi-event transaction can produce an invalid recovery.

GTID Filtering Is Not an Undo Button

--include-gtids selects transactions in the named set; it does not mean exclude the damaging transaction. --exclude-gtids omits a set, but surgically removing a transaction can break dependencies in later transactions and alter GTID execution history. Use filtering only after reviewing the full causal chain and deciding how the recovered server will rejoin replication. Never issue RESET BINARY LOGS AND GTIDS as a routine restore step: MySQL warns that it deletes binary logs and resets GTID history.

Validation, Cutover, and Rollback

Before cutover, run application invariants, foreign-key and aggregate checks, row counts for the affected interval, and a sample of business transactions. Confirm that the unwanted operation is absent and that the last expected transaction is present. Record the restored GTID set and binary-log status, take a fresh backup, then switch traffic through a reversible endpoint or proxy change. Keep the damaged server read-only and isolated until sign-off so rollback is a routing change rather than another restore. Schedule recovery drills and measure actual recovery-point and recovery-time outcomes; a configured binlog is not evidence that the chain can be replayed.

Official MySQL References

• MySQL 8.4 point-in-time recovery
• Continuous binary-log backup with mysqlbinlog
• SHOW BINARY LOG STATUS in MySQL 8.4
• RESET BINARY LOGS AND GTIDS warnings