Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted
DELETE
without WHERE
clause or any other harmful command.
PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).
However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, mysql
command-line client, and mysqlbinlog
programs only. There is an alternative approach that involves creating a fake source server, that is described in MySQL Point in Time Recovery the Right Way. You may consider it, especially if you need to apply changes to a single table.
Percona XtraBackup Point-In-Time Recovery
For our example we will create data first, then run DROP
and DELETE
commands on two different tables. Then we will rollback these commands.
First, let’s assume we have a server with two databases: test
and sbtest
. We are using GTIDs and row-based binary log format. We also run the server with the option innodb_file_per_table=1
and all our InnoDB tables use individual tablespaces. Otherwise, the individual restore method would not work.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> show tables from sbtest; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | +------------------+ 8 rows in set (0.00 sec) mysql> show tables from test; +----------------+ | Tables_in_test | +----------------+ | bar | | baz | | foo | +----------------+ 3 rows in set (0.00 sec) |
We will experiment with tables foo
and bar
. We assume that at the time of our first backup, each of the tables contained five rows. Tables in the database sbtest
also contain data, but it does not really matter for our experiment.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select count(*) from foo; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from bar; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from baz; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) |
Since we want to restore individual tables, we need to make a preparation before taking a backup: store database structure. We will do it with help of the mysqldump
command. In this example, I store structure per database to make partial PITR easier, but you are free to use the option --all-databases
.
1 2 |
mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events test > test_structure.sql mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events sbtest > sbtest_structure.sql |
Then we are ready to take the backup.
1 |
xtrabackup --parallel=8 --target-dir=./full_backup --backup |
I am using the option --parallel
to speed up the backup process.
Now let’s do some testing. First, let’s update rows in the table foo.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> update foo set f1=f1*2; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | +----+------+ 5 rows in set (0.00 sec) |
And then drop it and delete all rows from the table bar.
1 2 3 4 5 |
mysql> drop table foo; Query OK, 0 rows affected (0.02 sec) mysql> delete from bar; Query OK, 5 rows affected (0.01 sec) |
Finally, let’s insert a few rows into the tables bar and baz.
1 2 3 4 5 6 7 |
mysql> insert into bar(f1) values(6),(7),(8),(9),(10); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into baz(f1) values(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 |
Assume that the DROP TABLE
and DELETE
command was an accident and we want to restore the state of the tables foo
and bar
as they were before these unlucky statements.
First, we need to prepare the backup.
Since we are interested in restoring only tables in the database test
we need to prepare the backup with a special option --export
that exports tablespaces in a way that they could be later imported:
1 |
xtrabackup --prepare --export --target-dir=./full_backup |
Now the directory for the database test
contains not only table definition files (.frm
, only before 8.0) and tablespace files (.ibd
) but also configuration files (.cfg
).
Since we want all changes that happened after backup and before the problematic DROP TABLE
and DELETE
statements were applied, we need to identify which binary log and position were actual at the backup time. We can find it in the xtrabackup_binlog_info
file:
1 2 |
$ cat full_backup/xtrabackup_binlog_info master-bin.000004 1601 0ec00eed-87f3-11eb-acd9-98af65266957:1-56 |
Now we are ready to perform restore.
First, let’s restore the table foo
from the backup. Restoring individual tablespaces requires the ALTER TABLE ... IMPORT TABLESPACE
command. This command assumes that the table exists in the server. However, in our case, it was dropped and therefore we need to re-create it.
We will recreate the full database test from the file test_structure.sql
Since we do not want these administrative tasks to be re-applied, I suggest disabling binary logging for the session which will recreate the database structure.
1 2 3 4 5 6 7 8 |
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source test_structure.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) .... |
Once tables are recreated discard their tablespaces. I will show an example for the table foo
. Adjust the code for the rest of the tables.
1 2 |
mysql> alter table foo discard tablespace; Query OK, 0 rows affected (0.01 sec) |
Then, in another terminal, copy the tablespace and configuration files from the backup to the database directory:
1 |
cp full_backup/test/foo.{ibd,cfg} var/mysqld.1/data/test/ |
And, finally, import the tablespace:
1 2 |
mysql> alter table foo import tablespace; Query OK, 0 rows affected (0.05 |