In this blog post, I’ll look at how to execute a MySQL single table Point-In-Time Recovery.
I recently wrote a blog post describing a different way of doing Point-In-Time Recovery (PITR). If you want to know the step by step, please visit the mentioned blog post. Here is a quick summary of the approach:
- Restore the backup on the desired server
- Create a fake master
- Copy all relevant binlogs to the fake master
- Configure server from the first step as a slave from a fake master
In addition to the above steps, there is a similar approach that eliminates the usage of a fake master (check here). Both approaches allow us (among other things) to:
- Resume the restore after a possible failure
- Speed up restore using parallel replication
Furthermore, another benefit is that it allows us to selective restore events for a particular table (or tables). You can achieve this by adding replication filters on your slave server (replicate-do-db / replicate-wild-do-table / MySQL 5.7+ CHANGE REPLICATION FILTER).
As an example, if we only want to replay events from the world.city table, we can restore either the full backup or do a selective restore as described here. Edit my .cnf as follows:
1 2 3 | [mysqld] . . . replicate-wild-do-table=world.city |
From this point forward, we can start replication and only the events from the desired table are applied. There are a few caveats using replication filter, and they will vary depending on your binlog_format, make sure you understand them as described here.
Summary
While mysqlbinlog allows you to only filter out events on a per-database basis, you can easily configure replication filters to do a single table point-in-time recovery.
Hi There,
Could you please help me to understand the difference between the normal point in time recovery method and this fake master?
My understanding by this blog:
I think fake master means, I have to restore backup on two servers.
One I have to make master, copy there all the binlogs which I took for the point-in-time recovery. Another node , I have to make as a slave, add replication_* filter for the table in my.cnf . and start replication on slave. So it will read only particular table related events from the binlogs which you copied to the master server for point-in-time recovery and run on slave. This is just my assumption.Please confirm.
And help us to understand this blog and it would be great for me if you can share/demonstrate any actual example of this recovery.
Hi Archit,
The fake master doesn’t need the data (restore the backup). The binlog dump thread will only need to read the binlogs to serve them to the slave.