Flashback is a feature that allows instances, databases or tables to be rolled back to an old snapshot.
Flashback is currently supported only over DML statements (INSERT, DELETE, UPDATE). An upcoming version of MariaDB will add support for flashback over DDL statements (DROP, TRUNCATE, ALTER, etc.) by copying or moving the current table to a reserved and hidden database, and then copying or moving back when using flashback. See MDEV-10571.
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 mariadb-binlog / 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.
mariadb-binlog / mysqlbinlog with
--flashback, the Flashback events will be stored in memory. You should make sure your server has enough memory for this feature.
- mariadb-binlog / mysqlbinlog has the option
-Bthat will let it work in flashback mode.
- mariadbd / mysqld has the 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.
Do not use
-vv options, as this adds verbose information to the binary log which can cause problems when importing. See MDEV-12066 and MDEV-12067.
With a table "mytable" in database "test", you can compare the output with
--flashback and without.
mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable \ --start-datetime="2013-03-27 14:54:00" > review.sql
mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable \ --start-datetime="2013-03-27 14:54:00" --flashback > flashback.sql
If you know the exact position,
--start-position can be used instead of
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 primary and two replicas, one started with
--flashback(i.e. with binary logging enabled, using binlog_format=ROW, and binlog_row_image=FULL).
- Something goes wrong on the primary (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 replica from replication.
- Invoke mariadb-binlog / mysqlbinlog to find the exact log position of the first offending operation after the state you want to revert to.
mysqlbinlog --flashback --start-position=xyz | mysqlto pipe the output of
mariadb-binlog / mysqlbinlogdirectly to the
mariadb / mysqlclient, or save the output to a file and then direct the file to the command-line client.