Flashback: Another Take on Point-In-Time Recovery (PITR) in MySQL/MariaDB/Percona Server

MariaDB no longer meeting your needs?

Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.

Learn More

In this blog post, I’ll look at point-in-time recovery (PITR) options for MySQL, MariaDB and Percona Server for MySQL.

It is a common good practice to extend data safety by having additional measures apart from regular data backups, such as delayed slaves and binary log backups. These two options provide the ability to restore the data to any given point in time, or just revert from some bad accidents. These methods have their limitations of course: delayed slaves only help if a deadly mistake is noticed fast enough, while full point-in-time recovery (PITR) requires the last full backup and binary logs (and therefore usually takes a lot of time).

How to reverse from disaster faster

Alibaba engineers and the MariaDB team implemented an interesting feature in their version of the mysqlbinlog tool: the --flashback option. Based on ROW-based DML events, it can transform the binary log and reverse purposes. That means it can help undo given row changes extremely fast. For instance, it can change DELETE events to INSERTs and vice versa, and it will swap WHERE and SET parts of the UPDATE events. This simple idea can dramatically speed up recovery from certain types of mistakes or disasters.

The question is whether it works with non-MariaDB variants. To verify that, I tested this feature with the latest available Percona Server for MySQL 5.7 (which is fully compatible with upstream MySQL).

First, let’s simulate one possible deadly scenario: a forgotten WHERE in DELETE statement:

So, our data is lost on both the master and slave!

Let’s start by downloading the latest MariaDB server 10.2.x package, which I’m hoping has a mysqlbinlog tool that works with MySQL 5.7, and unpack it to some custom location:

It has the function we are looking for. Now, we have to find the culprit transaction or set of transactions we want to revert. A simplified example may look like this:

By searching through the decoded binary log, we are looking for transactions that have wiped out the table test.sbtest1. It looks like this:

It is very important to take the proper start and stop positions. We need the ones exactly after BEGIN and before the final COMMIT. Then, let’s test if the tool produces the reverse statements as expected:

Inside, we find INSERT statements reversing the deletes. Looks good:

Since we verified the positions are correct, we can prepare a binary log file:

and load it back to our master:

Check the result:

and verify on slaves:

GTID problem

MariaDB has a completely different GTID implementation from MySQL and Percona Server. You can expect problems when decoding incompatible GTID-enabled binary logs with MariaDB.

Solutions include disabling GTID temporarily or manually adding GTID information to the flashback log.

Limitations

Flashback cannot help after DROP/TRUNCATE or other DDL commands. These are not transactional and are not recorded in binary logs. It also does not work with encrypted or compressed binary logs.

Most importantly, the binary log format must be ROW, and row image must be FULL:

If these conditions are not met, you must follow the standard point-in-time recovery procedure.