MySQL Single Table Point-In-Time Recovery

Point-In-Time Recovery

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:

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

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569