Buy Percona ServicesBuy Now!

MySQL Single Table Point-In-Time Recovery

 | February 26, 2018 |  Posted In: Database Recovery, Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

Point-In-Time RecoveryIn 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.

PREVIOUS POST
NEXT POST
Marcelo Altmann

Marcelo Altmann is a member of Support Team. Prior to joining Percona , he worked as a MySQL DBA at Ireland's CCTLD, and worked as a DBA/PHP developer in Brazil.

Leave a Reply