# Flashback

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](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert), [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete), [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update)). (An upcoming version of MariaDB will add support for flashback for DDL statements ([DROP](https://mariadb.com/docs/server/server-usage/tables/drop-table), [TRUNCATE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table), [ALTER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table), 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](https://jira.mariadb.org/browse/MDEV-10571).)

Flashback is achieved in MariaDB Server using existing support for full image format binary logs ([binlog\_row\_image=FULL](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#binlog_row_image)), so it supports all engines.

The real work of Flashback is done by [mariadb-binlog](https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog) with [`--flashback`](https://mariadb.com/docs/server/starting-and-stopping-mariadb/mariadbd-options#flashback). This causes events to be translated: `INSERT` to `DELETE`, `DELETE` to `INSERT`, and for `UPDATE` statements, the before and after images are swapped.

{% hint style="info" %}
When executing `mariadb-binlog` with `--flashback`, the Flashback events are stored in memory. Make sure your server has enough memory for this feature.
{% endhint %}

## Arguments

* [mariadb-binlog](https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog) has the `--flashback` or `-B` option that makes it work in flashback mode.
* [mariadbd](https://mariadb.com/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options) has the option [--flashback](https://mariadb.com/docs/server/starting-and-stopping-mariadb/mariadbd-options#-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](https://jira.mariadb.org/browse/MDEV-12066) and [MDEV-12067](https://jira.mariadb.org/browse/MDEV-12067).

## Example

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

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

```bash
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](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#binlog_format), and [binlog\_row\_image=FULL](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#binlog_row_image)).
* 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](https://mariadb.com/docs/server/clients-and-utilities/logging-tools/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.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/flashback.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.