MySQL Single Table Point-In-Time Recovery

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:

1. Restore the backup on the desired server
2. Create a fake master
3. Copy all relevant binlogs to the fake master
4. 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:

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.