The Ultimate Guide to High Availability with MariaDB

Flashback

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.2.4

DML-only flashback was introduced in MariaDB 10.2.4

Contents

  1. New arguments
  2. Example
  3. Common use case

Flashback is a feature that will allow instances, databases or tables to be rolled back to an old snapshot.

Flashback is currently supported only over DML statements (INSERT, DELETE, UPDATE) is supported. An upcoming version of MariaDB will add support for flashback over DDL statements (DROP, TRUNCATE, ALTER, etc.).

Flashback is achieved in MariaDB Server using existing support for full image format binary logs (binlog_row_image=FULL), so it supports all engines.

The real work of Flashback is done by mysqlbinlog with --flashback. This causes events to be translated: INSERT to DELETE, DELETE to INSERT, and for UPDATEs the before and after images are swapped.

When executing mysqlbinlog with --flashback, the Flashback events will be stored in memory. You should make sure your server has enough memory for this feature.

New arguments

  • mysqlbinlog has a new option: --flashback or -B that will let it work in flashback mode.
  • mysqld has a new option: --flashback that enables the binary log and sets binlog_format=ROW. It is not mandatory to use this option if you have already enabled those options directly.

Example

With a table "t" in database "test", you can compare the output with --flashback and without.

 mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T t \
    --start-datetime="2013-03-27 14:54:00" > review.sql

 mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T t \
    --start-datetime="2013-03-27 14:54:00" --flashback > flashback.sql

If you know the exact position, --start-position can be used instead of --start-datetime.

Then, by importing the output file (mysql < flashback.sql), you can flash your database/table back to the specified time or position.

Common use case

A common use case for Flashback is the following scenario:

  • You have one master and two slaves, one started with --flashback (i.e. with binary logging enabled, using binlog_format=ROW, and binlog_row_image=FULL).
  • Something goes wrong on the master (like a wrong update or delete) and you would like to revert to a state of the database (or just a table) at a certain point in time.
  • Remove the flashback-enabled slave from replication.
  • Invoke mysqlbinlog to find the exact log position of the first offending operation after the state you want to revert to.
  • Run mysqlbinlog --flashback --start-position=xyz | mysql to pipe the output of mysqlbinlog directly to the mysql client, or save the output to a file and then direct the file to the command-line client.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.