MariaDB starting with 10.2.4
DML-only flashback was introduced in MariaDB 10.2.4
Flashback is a feature that will allow instances, databases or tables to be rolled back to an old snapshot. Currently only DML flashback is supported.
It's implemented on the server-level by full image format binary logs (--binlog-row-image=FULL), so it supports all engines.
Because the flashback binlog events will be stored in memory, you should make sure your server has enough memory for this feature.
mysqlbinlog has a new option:
-B that will let it work in flashback mode.
With a table "t" in database "test", you can compare the output with "--flashback" and without.
- client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" > /tmp/1.sql
- client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" -B > /tmp/2.sql
Then, importing the output flashback file (/tmp/2.log), you can flashback your database/table to the specific time (--start-datetime).
If you know the exact position, " -- start-position " also works. mysqlbinlog will output the flashback logs that can flashback to the " -- start-position " position.
Common use case
The common usage case for flashback is the following scenario:
- You have one master and two slaves, one started with --flashback.
- Something goes wrong (like a wrong update or delete) and you would like to get a copy of the database (or just a table) at a certain point in time.
- Remove the --flashback slave from replication.
mysqlbinlog --flashback --start-datetime=xxx | mysql--socket=connect-to-slave.
This will rollback all transaction in the slave to the given time xxx
An upcoming version of MariaDB will add support for flashback over DDL's. Currently only flashback over DML's is supported.