MySQL Binary Log and Point-in-Time Recovery for WordPress

MySQL’s binary log records every data-modifying statement (INSERT, UPDATE, DELETE) or row change as it happens. It is the foundation of point-in-time recovery — the ability to restore a database to any second in the past, not just the last backup snapshot.

Problem: A WordPress server suffers a corrupted database or accidental mass deletion, and the most recent backup is hours old — without binary log-based point-in-time recovery, all data changes since the last backup are lost permanently.

Solution: Enable MySQL binary logging with log_bin = ON and binlog_format = ROW in my.cnf. After restoring from a full backup, replay subsequent changes up to the desired recovery point with mysqlbinlog --start-datetime and --stop-datetime piped into mysql. Schedule full backups daily and retain binary logs for 7 days.

The examples below enable and configure the binary log, demonstrate how to use mysqlbinlog to extract SQL from a specific time window, and walk through a complete point-in-time recovery procedure for a WordPress database.

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Enable binary logging
log_bin              = /var/log/mysql/mysql-bin.log  # log file prefix
server-id            = 1             # must be unique across all servers in a replication setup
binlog_format        = ROW           # ROW captures actual data changes (not statements)
                                     # safer for triggers, stored procs, non-deterministic queries
binlog_row_image     = MINIMAL       # only log changed columns (saves disk space)
expire_logs_days     = 14            # auto-purge logs older than 14 days
max_binlog_size      = 100M          # rotate log file when it reaches this size
sync_binlog          = 1             # flush to disk on every commit (safest; slight perf cost)

# Apply:
systemctl restart mysql

# Verify binary logging is active:
mysql -u root -e "SHOW VARIABLES LIKE 'log_bin';"
# log_bin  ON

# List current binary log files:
mysql -u root -e "SHOW BINARY LOGS;"

# Current binlog position (record before a major operation):
mysql -u root -e "SHOW MASTER STATUS\G"

Point-in-time recovery procedure after accidental data deletion:

# SCENARIO: Accidental DELETE on wp_posts at 2025-01-20 14:35:00
# Last full backup was from 2025-01-20 02:00:00

# STEP 1: Restore the full backup to a recovery instance
mysql -u root helloadmin < /backups/helloadmin-2025-01-20-02h.sql

# STEP 2: Find which binlog files cover the window 02:00 to 14:34
# (stop just BEFORE the accidental delete)
mysql -u root -e "SHOW BINARY LOGS;"
# mysql-bin.000042 ...
# mysql-bin.000043 ...

# STEP 3: Extract SQL from binlogs for the time window
mysqlbinlog \
    --start-datetime="2025-01-20 02:00:00" \
    --stop-datetime="2025-01-20 14:34:59" \
    --database=helloadmin \
    /var/log/mysql/mysql-bin.000042 \
    /var/log/mysql/mysql-bin.000043 \
    > /tmp/recovery.sql

# STEP 4: Inspect the generated SQL (look for the DELETE)
grep -n "DELETE FROM" /tmp/recovery.sql | head -20

# STEP 5: Apply the recovery SQL to the restored instance
mysql -u root helloadmin < /tmp/recovery.sql

# STEP 6: Verify data is correct, then promote or export
mysqldump -u root helloadmin > /backups/helloadmin-recovered.sql

# USEFUL: Decode a specific binlog file to readable SQL
mysqlbinlog --base64-output=DECODE-ROWS -v \
    /var/log/mysql/mysql-bin.000043 | less

# Purge binlogs manually (keep at least last 3 days)
mysql -u root -e "PURGE BINARY LOGS BEFORE '2025-01-17 00:00:00';"

NOTE: Binary logs only work if they were enabled before the data loss event — there is no retroactive recovery. Set binlog_format=ROW rather than STATEMENT for WordPress: WooCommerce uses non-deterministic functions and triggers that produce different results when statement-based logs are replayed. Keep binlogs on a separate disk from the data directory to survive a data volume failure.