Flashback

Explains how to use the Flashback feature (via mariadb-binlog --flashback) to roll back transactions by reversing the binary log events, useful for recovering from accidental data modifications.

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

Flashback is supported only for DML statements (INSERT, DELETE, UPDATE). Flashback for DDL statements (DROP, TRUNCATE, ALTER, etc.) is not supported. 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 with --flashback. This causes events to be translated: INSERT to DELETE, DELETE to INSERT, and for UPDATE statements, the before and after images are swapped.

When executing mariadb-binlog with --flashback, the Flashback events are stored in memory. Make sure your server has enough memory for this feature.

Arguments

• mariadb-binlog has the --flashback or -B option that makes it work in flashback mode.

• mariadbd 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 the -v or -vv options, as they add verbose information to the binary log which can cause problems when importing. See MDEV-12066 and MDEV-12067.

Example

With a mytable table in a test database, you can compare the output with --flashback and without:

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

mariadb-binlog /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 --start-datetime.

Then, by importing the output file (mariadb < 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 to find the exact log position of the first offending operation after the state you want to revert to.

• Run mariadb-binlog --flashback --start-position=xyz | mariadb to pipe the output of mariadb-binlog directly to the mariadb client, or save the output to a file and then direct the file to the command-line client.

Error Reporting

If mariadb-binlog --flashback cannot decode a row image while reversing an event, it writes a diagnostic to standard error and exits with status 1:

Error decoding row image while converting event for --flashback (could not determine field length): event=Delete_rows_v1, column=1, column_type=252, metadata=5

Error decoding row image while converting event for --flashback (field extends past row buffer): event=Delete_rows_v1, column=1

The parenthesized reason is one of:

• could not determine field length — the column's type and metadata, as recorded in the table map event, do not yield a field length. The message also reports column_type and metadata.

• field extends past row buffer — the decoded field length runs past the end of the row image.

Either reason means the event cannot be reversed, for example because the binary log is corrupt or was written by a server whose row format mariadb-binlog cannot decode.

column is the zero-based column index within the row image. For UPDATE events the message also states which image failed, for example event=Update_rows (before image). The event name is the converted event type, so an INSERT that could not be reversed is reported as a Delete_rows event.

Before MariaDB 13.1, both conditions were reported as Error row length: 0, which identified neither the event nor the column.

_{This page is licensed: CC BY-SA / Gnu FDL}