How to do point-in-time recovery for MySQL with binary logs

How it works

The binary log records every change in order, with a byte position for each event. A dump made with --source-data notes the binlog file and position it matches. Recovery is two steps: load the dump, then re-run the logged changes from that position, stopping before the one you want to undo.

A worked example. Sunday's 01:00 dump records binlog.001002, position 27284. On Wednesday at 14:32 someone runs a DELETE without a WHERE. The server has since filled binlog.001003 and is writing binlog.001004, where the bad transaction starts at position 18432 and the next one at 19012. You load Sunday's dump, replay from binlog.001002 position 27284 to binlog.001004 position 18432, and the data is as it was just before 14:32. Replay from 19012 onwards too, and you also keep everything written after the mistake.

Check that binary logging is on

MySQL 8.4 turns the binary log on by default, but check, because a config file can turn it off:

SHOW VARIABLES LIKE 'log_bin';

ON means it is running. List the log files, then see which one is being written now:

SHOW BINARY LOGS;

SHOW BINARY LOG STATUS;

MySQL 8.0 calls the second statement SHOW MASTER STATUS. Both need the REPLICATION CLIENT privilege.

Configure the binary log

[mysqld]
server_id                  = 1
log_bin                    = /var/lib/mysql/binlog
binlog_expire_logs_seconds = 1209600
sync_binlog                = 1

• server_id defaults to 1, but MySQL logs a notice when binary logging is on and you haven't set it. Every server in a replication setup needs its own value.
• log_bin is the path and base name of the log files. The default is binlog in the data directory. Logs on a different disk from the data survive a dead data disk.
• binlog_expire_logs_seconds is how long MySQL keeps log files before deleting them, here 14 days. The default is 2592000, 30 days. Keep logs longer than the gap between full dumps, or you'll have a dump you can't roll forward.
• sync_binlog = 1 is the default: the log reaches disk before each commit completes, so a crash can't lose a committed transaction from it.

Leave binlog_format alone. It defaults to ROW, which logs the rows each statement changed, and MySQL has deprecated the setting and the other formats with it. Changing log_bin needs a restart. The expiry can change live, and SET PERSIST also saves it for the next start:

SET PERSIST binlog_expire_logs_seconds = 1209600;

Take a full dump that records its position

The backup account needs RELOAD and REPLICATION CLIENT on top of the usual read privileges, because --source-data reads the binlog position under a brief global lock:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

Put its credentials in an option file as in the mysqldump guide, then dump every database:

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --flush-logs --source-data=2 --routines --events --triggers --all-databases | gzip > /var/backups/mysql/full-$(date +%F).sql.gz

• --source-data=2 writes the binlog file and position into the dump as a comment. With 1 it would be a live CHANGE REPLICATION SOURCE TO statement, which is for building replicas. The old name, --master-data, still works as a deprecated alias.
• --single-transaction dumps InnoDB tables from one consistent snapshot. With --source-data, it takes a global read lock only for a moment at the start, to read the position. A long-running write can make that moment wait.
• --flush-logs starts a new binlog file at the instant of the dump, so the logs you need begin at a file boundary.

Find the recorded position. The pattern also matches the CHANGE MASTER TO form that MariaDB writes:

zgrep -m 1 -E "SOURCE_LOG_FILE|MASTER_LOG_FILE" /var/backups/mysql/full-2026-10-04.sql.gz

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.001002', SOURCE_LOG_POS=27284;

Copy the binary logs off the server

Logs on the database's own disk die with it. There are two ways to get them off.

Rotate and copy. FLUSH BINARY LOGS closes the current file and starts a new one, so every file except the newest is complete. Run this every hour and you lose at most an hour of changes:

#!/usr/bin/env bash
set -euo pipefail

CNF="/etc/mysql/backup.cnf"
DEST="/var/backups/mysql/binlogs"

mkdir -p "$DEST"
mysql --defaults-extra-file="$CNF" -e "FLUSH BINARY LOGS"
ACTIVE=$(mysql --defaults-extra-file="$CNF" -N -e "SHOW BINARY LOG STATUS" | cut -f1)

rsync -a --exclude "$ACTIVE" /var/lib/mysql/binlog.[0-9]* "$DEST"/

-N drops the column header and cut -f1 keeps the file name. FLUSH BINARY LOGS needs RELOAD. The script copies to a local directory; send that directory to other storage too, for example with rclone.

Stream them. From another machine, mysqlbinlog can connect like a replica and write each log as the server produces it:

mysqlbinlog --defaults-extra-file=/etc/mysql/binlog-stream.cnf --read-from-remote-server --raw --stop-never --connection-server-id=901 --result-file=/srv/binlogs/ binlog.001002

• --raw writes the original binary files instead of text; --stop-never stays connected for new events; --result-file is a prefix for the output names, here a directory.
• --connection-server-id sets the server ID mysqlbinlog reports. With --stop-never it reports 1 by default, which can clash with a server or replica already using 1.
• The account needs the REPLICATION SLAVE privilege.
• mysqlbinlog does not reconnect after a network drop or a server restart. Run it under a supervisor that restarts it from the newest file it has.
• Copies of encrypted binary logs are written unencrypted, so protect the destination.

Find the bad statement

Gather the logs you need in one directory on the recovery machine. Decode the window around the incident into a file you can read:

mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-datetime="2026-10-07 14:25:00" --stop-datetime="2026-10-07 14:40:00" binlog.001004 > window.txt

• --verbose shows row events as commented pseudo-SQL starting with ###, such as ### DELETE FROM. Columns usually appear as @1, @2 in table order.
• --base64-output=DECODE-ROWS hides the encoded BINLOG blocks. Its output is for reading only; never pipe it into mysql.
• Dates are read in the local time zone of the machine running mysqlbinlog.

Search window.txt for the table name. Each event starts with # at N, its byte position. A transaction opens with a GTID or Anonymous_GTID event, carries its row events, and ends with a commit marked Xid. Note two numbers: the # at of the event that opens the bad transaction, and the # at of the first event after its commit.

To see the original SQL text and not just rows, turn on binlog_rows_query_log_events ahead of time; mysqlbinlog -vv then prints each statement as a comment. Use the times only to find positions. The MySQL manual warns that replaying by --start-datetime and --stop-datetime risks missing events.

Restore to just before the mistake

Restore on a fresh server or MySQL instance of the same version, not on top of the damaged one: you keep the damaged data to compare, and old GTID history can't get in the way. Load the full dump first. It was made with --all-databases, so it creates the databases itself:

gunzip < full-2026-10-04.sql.gz | mysql -u root -p

Then replay from the dump's position to the start of the bad transaction. --start-position applies to the first file named and --stop-position to the last, and the event that begins at the stop position is left out:

mysqlbinlog --start-position=27284 --stop-position=18432 binlog.001002 binlog.001003 binlog.001004 | mysql -u root -p

Name all the files in one mysqlbinlog command, as above; the manual says to apply several logs over a single connection. To keep the changes made after the mistake, replay from the event after its commit to the end of the newest log:

mysqlbinlog --start-position=19012 binlog.001004 binlog.001005 | mysql -u root -p

Before you point the app at the restored server, check row counts on the damaged tables and a few records written after the incident.

If GTIDs are on

With gtid_mode=ON, every transaction carries an ID such as 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. Three rules follow:

• A dump made with the default --set-gtid-purged=AUTO tells the target which transactions it contains. On a fresh server that is right: the replay then applies only later transactions.
• A server silently skips any transaction whose GTID it has already committed. No error, no change. Replaying logs on the server that originally ran them does nothing.
• Loading the dump into a server that already has those GTIDs, such as the original, fails on its gtid_purged statement. On MySQL 8.4, RESET BINARY LOGS AND GTIDS clears the GTID history, and also deletes every binary log on that server, so copy them first.

GTIDs also give a simpler way to skip one transaction. Read its ID from the SET @@SESSION.GTID_NEXT= line in the decoded log and exclude it in a single replay:

mysqlbinlog --start-position=27284 --exclude-gtids='3E11FA47-71CA-11E1-9E33-C80AA9429562:23' binlog.001002 binlog.001003 binlog.001004 binlog.001005 | mysql -u root -p

Avoid --skip-gtids for recovery. The manual reserves it for rare cases where the IDs are actively unwanted.

MariaDB differences

Positions, --start-position and --stop-position work the same way. MariaDB's GTIDs work differently, so the GTID steps above are MySQL only.

Schedule it

0 1 * * * root /usr/local/bin/mysql-full-dump.sh >> /var/log/mysql-pitr.log 2>&1
5 * * * * root /usr/local/bin/mysql-binlog-copy.sh >> /var/log/mysql-pitr.log 2>&1

mysql-full-dump.sh is the dump command above in a script with set -o pipefail, like the one in the mysqldump guide. Keep dumps and binlogs for the same period, and rehearse now and then: replay to a known time on a scratch server and look for a record you know, as in testing a restore.

Common errors

Frequently asked questions

Is binary logging on by default in MySQL?

Yes in MySQL 8.4, unless a config file turns it off with skip-log-bin or disable-log-bin. MariaDB leaves it off until you set log_bin.

How long should I keep binary logs?

Longer than the gap between full dumps, plus however far back you want to be able to recover. MySQL's default is 30 days; MariaDB's default is to keep them forever.

Do I need binary logs if I already dump every night?

Only if losing up to a day of changes is not acceptable. A dump alone restores the database as it was when the dump ran; binary logs fill the time since.