MySQL PITR

Posted on | In

This blog is about how to perform PITR: 8.0 Reference.

What is PITR

PITR(point-in-time-recovery) is a recovery technique that allows you to restore your database to an exact state at a specific moment in time (e.g. “right before I accidentally dropped that table at 2:30 PM yesterday”).

Enabling the binary log is a prerequisite for performing point-in-time recovery in MySQL. Here’s 2 major phases:

  • Full Backup (Baseline): To perform PITR, you first need a full backup of your database. This backup captures the state of your database at a specific moment.
  • Binary Log (Incremental Changes): After your full backup, any subsequent changes to the database are recorded in the binary log.

The PITR Process

  • You restore your database from the most recent full backup.
  • You then use the binary log to roll forward the database by replaying all the changes that occurred after that full backup, up to the desired point in time.
  • The mysqlbinlog utility is typically used to read the binary log files and convert them into SQL statements (or other formats), which are then applied to the restored database.

Goal

Performing a partial table PITR on the same instance to recover the accidentally dropped table and retain the updates on other tables.

Vagrant

The playground is managed by Vagrant, please launch it before move on to the next section.

Data Seed

On server, let’s create an animals DB and table animals and food:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE DATABASE IF NOT EXISTS animals;

CREATE TABLE animals.animals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    species VARCHAR(50),
    age INT
);

INSERT INTO animals.animals (name, species, age) VALUES
('Bella', 'Dog', 3),
('Milo', 'Cat', 2);


CREATE TABLE animals.food (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    price INT
);

INSERT INTO animals.food (name, price) VALUES
('Meat', 100);

SELECT * FROM animals.animals;
SELECT * FROM animals.food;

Full Instance Backup

Create a new folder for backup hosting:

1
2
3
# Create a folder to host dump
sudo mkdir /etc/mysqlsh-dump
sudo chown vagrant:vagrant /etc/mysqlsh-dump

At this point we take a full instance backup using mysqlsh:

1
2
3
4
5
6
7
8
9
10
mysqlsh \
--user='root' \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./dump_mysqlsh.log \
-- util dump-instance /etc/mysqlsh-dump \
--compression='zstd' \
--consistent=true \
--threads=2 \
--dry-run=false

The output is like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
WARNING: Using a password on the command line interface can be insecure.
Acquiring global read lock
Global read lock acquired

Initializing - done
1 out of 5 schemas will be dumped and within them 2 tables, 0 views.
1 out of 4 users will be dumped.

Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL

NOTE: Table statistics not available for `animals`.`food`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `animals`.`food`;' first.

Running data dump using 2 threads.

NOTE: Progress information uses estimated values and may not be accurate.

Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
150% (3 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 38 bytes
Compressed data size: 56 bytes
Compression ratio: 0.7
Rows written: 3
Bytes written: 56 bytes
Average uncompressed throughput: 38.00 B/s
Average compressed throughput: 56.00 B/s

The dump file functions in terms of table animals.animals:

1
2
3
animals@animals@@0.tsv.zst      <- compressed table data (actual rows)
animals@animals.sql             <- schema (CREATE TABLE, etc.)
animals@animals.json            <- metadata

You can view the plain row data with zstd decoding:

1
2
3
4
5
6
zstd -d < animals@animals@@0.tsv.zst
1	Bella	Dog	3
2	Milo	Cat	2

zstd -d < animals@food@@0.tsv.zst
1	Meat	100

Drop Table

Now drop the table animals, then we do insert on table food after table animals is dropped:

1
2
3
4
5
6
7
8
INSERT INTO animals.animals (name, species, age) VALUES
('Maria', 'Fish', 12);

DROP TABLE animals.animals;

INSERT INTO animals.food (name, price) VALUES
('SUGAR', 18),
('APPLE', 19);

Partial Table PITR

Now, we want to bring table animals back and keep the changes on table food.

First, back up the bin log files:

1
2
3
4
5
6
# You can use SHOW VARIABLES LIKE 'log_bin%'; to check binlog path
# Create a folder to host bin log backups
sudo mkdir /etc/binlog-backup
sudo chown vagrant:vagrant /etc/binlog-backup

sudo cp -R /var/lib/mysql /etc/binlog-backup

Replay Full Backup

The current master status:

1
2
3
4
5
6
7
8
9
mysql> show master status\G

*************************** 1. row ***************************
             File: binlog.000002
         Position: 2374
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Run dump load with mysqlsh for animals table only:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# You must specify includeTables, otherwise you will have conflicts errors
# because animals.food table still exists.
# See filter options:
# https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html#mysql-shell-utilities-load-dump-opt-filtering

# -e: to use fine controlled load, we need --py and this flag, is it different
# from the format we use for dump above.
mysqlsh \
--user='root' \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./load_mysqlsh.log \
--py \
-e "util.load_dump('/etc/mysqlsh-dump', {
  'schema': 'animals',
  'includeTables': ['animals.animals'],
  'threads': 2,
  'deferTableIndexes': 'all',
  'DryRun': 'False'
})"

Now we can see animals.animals table is back at the time of full instance backup, but the last insert is missing because it is after the full instance backup:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
+----+-------+---------+------+
2 rows in set (0.00 sec)

Now check the master status, the Position is updated from 2374 to 3469:

1
2
3
4
5
6
7
8
9
mysql> show master status\G

*************************** 1. row ***************************
             File: binlog.000002
         Position: 3469
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Inspect Binlog

We then need to check bin log to find the exact moment when the animals.animals was dropped and we will replay the SQL before it to bring the missing insert row back.

First go to find the binlog coordinates, it is in @.json file (on show the irrelevant lines):

1
2
3
4
5
6
7
{
    ...
    "binlogFile": "binlog.000002",
    "binlogPosition": 1544,
    "begin": "2025-07-05 20:41:29"
    ...
}

Then we inspect the binlog.000002 and its successors(if they are) to find the time window between the missing insert and drop of animals.animals table.

Please note here is just a simple case, as we don’t have SQLs from other tables, otherwise you need some manual work to filter them out.

1
2
3
4
5
sudo mysqlbinlog \
--base64-output=DECODE-ROWS \
--verbose \
/etc/binlog-backup/mysql/binlog.000002 |
grep -i 'INSERT INTO `animals`.`animals`' -B 2 -A 5
1
2
3
4
5
6
7
# 250705 20:41:42 server id 1  end_log_pos 1817 CRC32 0xb27353c1 	Write_rows: table id 125 flags: STMT_END_F
### INSERT INTO `animals`.`animals`
### SET
###   @1=3
###   @2='Maria'
###   @3='Fish'
###   @4=12
1
2
3
4
5
sudo mysqlbinlog \
--base64-output=DECODE-ROWS \
--verbose \
/etc/binlog-backup/mysql/binlog.000002 |
grep -i 'DROP TABLE' -B 2
1
2
3
# 250705 20:41:46 server id 1  end_log_pos 2066 CRC32 0x2d60fed1 	Query	thread_id=8	exec_time=0	error_code=0	Xid = 101
SET TIMESTAMP=1751748106/*!*/;
DROP TABLE `animals`.`animals` /* generated by server */

Replay Binlog

Now we can replay the missing insert for table animals.animals:

1
2
3
4
5
6
7
8
9
# --start-datetime 2025-07-05 20:41:42, inclusive for insert SQL
# --stop-datetime 2025-07-05 20:41:46, exclusive, right before drop
# --start-position=1544 from @.json file
sudo mysqlbinlog \
--start-datetime="2025-07-05 20:41:42" \
--stop-datetime="2025-07-05 20:41:46" \
--start-position=1544 \
--skip-gtids \
/etc/binlog-backup/mysql/binlog.000002 | mysql -uroot -p

Verification

Now, checking and we can see the missing insert is back:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
|  3 | Maria | Fish    |   12 |
+----+-------+---------+------+
3 rows in set (0.00 sec)
0%