MySQL Point-in-Time Recovery Using Binary Logs

MySQL point-in-time recovery using binary logs lets you roll a database back to the exact second before a bad DELETE, UPDATE or DROP TABLE hit production. You restore the last full backup, then replay the binary logs from that backup up to just before the mistake, so everything after the incident is discarded.

This guide walks through the full workflow on a Linux VPS: confirming binlogs are enabled, taking a fresh backup, extracting the right position from the logs, and replaying them with mysqlbinlog. It assumes you already run MySQL 8.0/8.4 or MariaDB 10.x and have root or sudo access.

Before you start

You need:

• A Linux VPS running MySQL 8.0/8.4 or MariaDB 10.x, with sudo access.
• Binary logging enabled (log_bin = ON) and the log files still on disk.
• A recent full backup — ideally from Automating MySQL backups with mysqldump and cron. If you have no backup, binlogs alone cannot rebuild the data.
• Enough free disk space to hold a restored copy plus the replayed logs. Check with df -h and Monitor CPU, memory, disk and logs on a Linux server.
• The exact time of the incident (to the second) and the time zone the server uses.

Confirm binlogs are on and note the log directory:

sudo mysql -e "SHOW VARIABLES LIKE 'log_bin';"
sudo mysql -e "SHOW VARIABLES LIKE 'log_bin_basename';"
sudo mysql -e "SHOW BINARY LOGS;"

If log_bin is OFF, stop here — you cannot do point-in-time recovery. Enable it in my.cnf (see Where is my.cnf? MySQL and MariaDB config files explained) and restart MySQL, but note that only events after the restart are captured.

Step 1: Capture the current state and stop writes

First, dump the damaged database so you have a copy of whatever still exists:

sudo mysqldump --single-transaction --routines --triggers \
  --databases shop > /root/shop-damaged-$(date +%F-%H%M).sql

Then stop the application from writing. If you can, put the site into maintenance mode or stop the web/PHP service:

sudo systemctl stop nginx php8.2-fpm

You want no new writes hitting the database while you work. If other services connect, block them at the firewall or stop them too.

Step 2: Find the recovery target position

You need two positions:

1. The start position — where the last full backup ends.
2. The stop position — the moment just before the bad statement.

If your backup was taken with --master-data or --source-data, the start position is recorded in the dump header. Look for it:

grep -m1 -E "CHANGE MASTER|CHANGE REPLICATION SOURCE" /root/backups/shop-full.sql

You will see something like:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=157;

That is your starting file and offset. If you do not have it, use the oldest binlog that covers the backup time.

To find the stop position, scan the binlogs around the incident time. Convert the incident time to a Unix timestamp and search:

date -d "2026-01-01 14:32:00" +%s

Then decode the binlogs and look for the offending statement:

sudo mysqlbinlog --base64-output=DECODE-ROWS -v \
  /var/lib/mysql/mysql-bin.000043 | less

Search inside less for DROP TABLE, DELETE FROM or the table name. Note the end_log_pos of the event immediately before the bad one. That number is your stop position.

Step 3: Restore the full backup

Drop and recreate the database, then load the dump. If the dump used --databases, it will create the schema itself.

sudo mysql -e "DROP DATABASE IF EXISTS shop;"
sudo mysql < /root/backups/shop-full.sql

Verify the tables are back:

sudo mysql -e "SHOW TABLES FROM shop;"

At this point the database matches the moment the backup was taken. Everything after that is missing — that is what the binlogs will restore.

Step 4: Replay the binary logs

Replay from the start position to the stop position. Run this as the mysql user so file permissions match:

sudo mysqlbinlog \
  --start-position=157 \
  --stop-datetime="2026-01-01 14:31:59" \
  /var/lib/mysql/mysql-bin.000042 \
  /var/lib/mysql/mysql-bin.000043 \
  | sudo mysql

Key options:

• --start-position / --stop-position — exact byte offsets in the binlog.
• --start-datetime / --stop-datetime — wall-clock times, useful when you only know roughly when it happened.
• --database=shop — restrict replay to one database. Use this if the binlogs contain other databases you do not want to touch.

If you are replaying row-based events, add --skip-gtids when piping into a server that already has GTIDs enabled, or the replay may fail with ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set.

For a large replay, write the SQL to a file first so you can inspect it before applying:

sudo mysqlbinlog --start-position=157 --stop-datetime="2026-01-01 14:31:59" \
  /var/lib/mysql/mysql-bin.000042 /var/lib/mysql/mysql-bin.000043 \
  > /root/replay.sql

Then check the head of the file and apply it:

head -n 40 /root/replay.sql
sudo mysql < /root/replay.sql

Step 5: Recover the dropped table only

If the incident was a single DROP TABLE and you want to keep everything else, use a different approach:

1. Restore the full backup to a scratch database, e.g. shop_recover.
2. Replay binlogs into shop_recover up to just before the DROP.
3. Copy the table back into production:

sudo mysqldump --single-transaction shop_recover orders > /root/orders.sql
sudo mysql shop < /root/orders.sql

This avoids touching the live database except for the one table.

Check it works

Log in and confirm the data matches the target moment:

sudo mysql shop -e "SELECT COUNT(*) FROM orders;"
sudo mysql shop -e "SELECT MAX(created_at) FROM orders;"

The newest row should be from just before the incident. Check a few known rows by primary key, and confirm no rows from after the incident slipped in. If your application writes to multiple databases, verify each one.

Also confirm the binlog position you stopped at is where you expected:

sudo mysql -e "SHOW MASTER STATUS;"

Restart the application and watch the error log for a minute before declaring the recovery done:

sudo systemctl start nginx php8.2-fpm
sudo tail -f /var/log/mysql/error.log

Troubleshooting

• ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set — the binlog contains GTID events and the target server has GTIDs on. Add --skip-gtids to mysqlbinlog, or replay into a server with gtid_mode=OFF.
• ERROR 1062 (23000): Duplicate entry — you replayed a statement that was already applied. Your start position is too early. Move it forward to the end_log_pos of the last event already in the backup.
• Could not find first log file name in binary log index file — the binlog file was rotated or purged. Check SHOW BINARY LOGS and expire_logs_days / binlog_expire_logs_seconds. If the file is gone, you can only recover to the earliest surviving log.
• Replay finishes but data is still missing — the binlog format is STATEMENT and some statements were non-deterministic, or the binlog was truncated. Switch to binlog_format=ROW for future recoveries.
• Access denied when piping into mysql — you are running as your shell user, not root. Use sudo mysql or pass -u root -p. See Fix MySQL or MariaDB error 1045: Access denied for user.

Next steps

• Set up regular dumps so you always have a fresh starting point: Automating MySQL backups with mysqldump and cron.
• Learn the manual restore flow: How to back up and restore MySQL databases with mysqldump.
• Keep the clock accurate so timestamps in binlogs line up: Set the time zone and keep the clock in sync (NTP).