Howto make MySQL point-in-time recovery faster ?

Before explaining how you can improve the speed for performing point-in-time recovery, let’s recall what is Point-In-Time Recovery and how it’s usually performed.

Point-in-Time Recovery, PTR

Point-In-Time recovery is a technique of restoring your data until a certain point (usually until an event that you would like that has never happened).

For example, a user did a mistake and you would like to recover your data up to that mistake to revert its effects like a drop table or a massive delete.

The usual technique consists to restore the last backup and replay the binary logs up to that unfortunate “event”.

So, as you might have already realized, backups and binary logs are required 😉

The main spread technique to replace those binary logs event is to use the `mysqlbinlog` command. However, depending on your workload, this process can be quick or slow, depending on how much data there is to process. Moreover, `mysqlbinlog` parses and dumps binary logs in a single thread, therefore sequentially. Imagine you do a daily backup at midnight and one of your user inconveniently deletes some records at 23.59… you have almost a full day of binary logs to process to be able to perform the Point-in-Time Recovery.

Boost binary log processing

Instead of using the `mysqlbinlog` utility to process our MySQL events, in 5.6 and above we have the possibility to use the MySQL server to perform this operation.
In fact, we will use the slave SQL_thread… and as some of you might have realized it already… we could then process those binary logs in parallel using multiple worker threads !

Example

We have a single server running and it’s configured to generate binary logs.

Sysbench is running oltp on 8 tables using 8 threads while we will play on another table not touched by sysbench to make the example easier to follow.

Related

13 thoughts on “Howto make MySQL point-in-time recovery faster ?”

According to my understanding，for the method No 1，if you set gtid_purged like you mentioned, the records of id 7 and 8 will be lost。

for the method No 2, if i just set “START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = ‘7766037d-4d1e-11e7-8a51-08002718d305:25076′;”
all the previous statments would be replay，included the create table,which would definitely break the SQL Thread.

so before that,i set the gtid_purged first,ignording all the dmls when i backup,then i start SQL Thread,but is not cannot replicate normally.

I’m sorry that you are not able to reproduce this solution. So let’s me reply to your 3 questions below:

1. using GTID_PURGED:

In fact your understanding is not correct. I remade the example and I will try to explain it again. In this new example the transaction we want to skip has 9d49bc58-5268-11e7-a2a0-08002718d305:327 as GTID.

If you are using xtrabackup, you will find the info in xtrabackup_binlog_info, therefor you will need to set GTID_PURGED to the content of that file and the value of the transaction you want to skip, example:

Hi，after you have restored from the Xtrabackup, you need first issue “reset master”
it will purge the gtid_executed variable and also truncate the mysql.gtid_executed table.
After that you can set your gtid_purged.