Point-in-Time Recovery with the Binary Log

A nightly dump loses everything since last night. The binary log closes that gap. It records every data change, in order, so you can restore the last full backup and then replay the log up to the second before the accident. This is point-in-time recovery (PITR). Below is a real example, run on a MySQL 8.4.11 server.

What you need in advance

1. The binary log enabled (it is on by default in MySQL 8: log_bin = ON).
2. A full backup that records the log position at the time it was taken (--source-data=2).
3. All binary logs since that backup, kept safe (copy them off the server regularly).

1

SELECT @@log_bin AS enabled, @@binlog_format AS format;

1
2
3
4
5
6

+---------+--------+
| enabled | format |
+---------+--------+
|       1 | ROW    |
+---------+--------+
1 row in set

ROW format records the changed rows (old and new values). It is the default and the safest for recovery.

Step 1: a backup with the log position

1

mysqldump -u backup -p --single-transaction --source-data=2 --flush-logs --routines --triggers --events --set-gtid-purged=OFF --databases shop > shop_full.sql

--flush-logs starts a new log file at the moment of the backup, and --source-data=2 writes the starting position into the file as a comment. Look for it:

1

grep "CHANGE REPLICATION SOURCE" shop_full.sql

1

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=158;

Everything that happens after this backup is in binlog.000002 from position 158 onward. (On MySQL 8.0 before 8.0.26 the option is --master-data=2, and the line says CHANGE MASTER TO.) The current log position is also available from the server:

1
2

SHOW BINARY LOG STATUS;       -- MySQL 8.4 and later. In 8.0 it is SHOW MASTER STATUS
SHOW BINARY LOGS;

1
2
3
4
5

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      158 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

(SHOW MASTER STATUS was removed in MySQL 8.4. It is an error there, so use SHOW BINARY LOG STATUS.)

Step 2: life goes on, then the accident

After the backup, the shop keeps working. These changes exist only in the binary log, not in the dump:

1
2
3

INSERT INTO shop.customers (name, email, city) VALUES ('Dev', 'dev@example.com', 'Kochi');
INSERT INTO shop.orders (customer_id, total, status) VALUES (6, 70, 'paid');
UPDATE shop.orders SET status = 'shipped' WHERE id = 3;

Then someone runs, by mistake:

1

DELETE FROM shop.orders;       -- no WHERE clause

Then the shop carries on and takes one more order:

1
2

INSERT INTO shop.orders (customer_id, total, status) VALUES (2, 55, 'new');
SELECT COUNT(*) AS orders_now FROM shop.orders;

1
2
3
4
5

+------------+
| orders_now |
+------------+
|          1 |
+------------+

Only the last order survives. First, stop the damage: put the application in maintenance mode, so that no more changes pile up on a database in this state.

Step 3: find the mistake in the log

mysqlbinlog decodes the log. --base64-output=DECODE-ROWS -v prints the row changes as readable pseudo-SQL:

1

mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=158 /var/lib/mysql/binlog.000002

The output is long. Each transaction starts with a # at <position> line. The interesting part, from the real run:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# at 1145
#261005 20:48:25 server id 1  end_log_pos 1224  Anonymous_GTID  ...
# at 1224
#261005 20:48:25 server id 1  end_log_pos 1299  Query   thread_id=7434  exec_time=0  error_code=0
BEGIN
# at 1299
#261005 20:48:25 server id 1  end_log_pos 1365  Table_map: `shop`.`orders` mapped to number 5193
# at 1365
#261005 20:48:25 server id 1  end_log_pos 1573  Delete_rows: table id 5193 flags: STMT_END_F
### DELETE FROM `shop`.`orders`
### WHERE
###   @1=1
###   @2=1
###   @3=120.50
###   @4='paid'
###   @5='2026-10-05 20:47:56'
### DELETE FROM `shop`.`orders`
...
# at 1573
#261005 20:48:25 server id 1  end_log_pos 1604  Xid = 95998
COMMIT/*!*/;
# at 1604

The DELETE transaction starts at position 1145 (its Anonymous_GTID event) and ends at 1604, where the next transaction begins. So:

• Replay up to position 1145 (the stop position is exclusive: events that start at 1145 or later are not applied).
• Skip the bad transaction.
• Replay again from 1604, to include the legitimate order that came after it.

With GTIDs enabled, the log shows a GTID for each transaction instead of an anonymous one, and you can use --exclude-gtids to skip exactly that one. You can also search by time: --start-datetime and --stop-datetime, but positions are exact, and times can be ambiguous.

Step 4: restore the backup

Restore the full dump on the server (or, better, on a new server, and switch over when it is right). The dump puts the tables back as they were at backup time:

1

mysql -u root -p < shop_full.sql

1

SELECT COUNT(*) AS orders_after_restore FROM shop.orders;

1
2
3
4
5

+----------------------+
| orders_after_restore |
+----------------------+
|                    6 |
+----------------------+

The six original orders are back, but the customer and orders from after the backup are still missing.

Step 5: replay the log up to the accident

1

mysqlbinlog --start-position=158 --stop-position=1145 --database=shop /var/lib/mysql/binlog.000002 | mysql -u root -p

--start-position=158 is the position from the backup file. --stop-position=1145 stops before the DELETE. --database=shop replays only that database. The pipe sends the decoded statements to the server.

1
2

SELECT id, status FROM shop.orders ORDER BY id;
SELECT COUNT(*) AS customers FROM shop.customers;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

+----+---------+
| id | status  |
+----+---------+
|  1 | paid    |
|  2 | paid    |
|  3 | shipped |
|  4 | paid    |
|  5 | shipped |
|  6 | new     |
|  7 | paid    |
+----+---------+

+-----------+
| customers |
+-----------+
|         6 |
+-----------+

Order 7 (the new one), the new customer, and the status change of order 3 are back. The accident is not applied.

Step 6: skip the accident, apply the rest

1

mysqlbinlog --start-position=1604 --database=shop /var/lib/mysql/binlog.000002 | mysql -u root -p

1

SELECT id, customer_id, total, status FROM shop.orders ORDER BY id;

1
2
3
4
5
6
7
8
9
10
11
12

+----+-------------+--------+---------+
| id | customer_id | total  | status  |
+----+-------------+--------+---------+
|  1 |           1 | 120.50 | paid    |
|  2 |           1 |  80.00 | paid    |
|  3 |           2 | 300.00 | shipped |
|  4 |           3 |  45.25 | paid    |
|  5 |           4 | 999.00 | shipped |
|  6 |           5 |  15.00 | new     |
|  7 |           6 |  70.00 | paid    |
|  8 |           2 |  55.00 | new     |
+----+-------------+--------+---------+

Every order is back, including the one placed after the accident, and nothing was lost. (In a real recovery, you would continue with --start-position=1604 to the end of the file, and then the following log files in order.)

Rules for point-in-time recovery

• Practise it before you need it. The first time must not be during an emergency.
• Replay on a separate recovery server when you can, then check it and switch.
• Replay log files in order (binlog.000002, .000003, …), in one mysqlbinlog command if they follow each other, so that transactions spanning files are handled.
• Copy the binary logs off the server regularly. If the disk dies, the logs on it die too, and the backup is all you have.
• Do not PURGE BINARY LOGS files that you may still need. binlog_expire_logs_seconds (30 days by default) removes old ones automatically.
• Replaying writes into the binary log again (use sql_log_bin = 0 on a recovery server if you do not want that).
• If the accident was a small one, restoring one table or a few rows from a copy of the backup is often quicker (Restoring). You can also prevent the accident: use safe update mode and transactions (Safe Updates).