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-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:
. . .
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.
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.