Skip to content

MariaDB flashback by example

Preamble

I have already seen this flashback technology with Oracle (https) but was surprised to see that MariaDB has implemented a similar feature. Apparently it’s there since release 10.2.4 so not new… Flashback can be done at instance, database or table level. For now they only support DML operations (Insert, Update, Delete) but they plan to include DDL…

I rate the feature more useful at table level, because for instance or database level I think DBAs would more use a mariadb-backup and do a Point In Time Recovery (PITR). Not a piece of cake but you can do a single database restore with mariadb-backup if you club your backup with a mariadb-dump and –no-data option. Then mariadb-binlog with –database option let you do a single database PITR…

MariaDB flashback is also a bit more flexible than working with backup and PITR !

The MariaDB feature is fully base on binary logs, you must have binlog_row_image = FULL (default value), so all storage engine are supported even if I think nowadays InnoDB is the most used. In background feature will trigger mariadb-binlog with the –flashback (-B) option. The trick is to use before image provided by binlog_row_image = FULL and deconstruct what has been wrongly done…

Even if the feature is really interesting and can save you in multiple cases it is anyways still not at the level of what Oracle database is providing…

My testing has been done with MariaDB 11.8.3 Community Edition and my test server is running Red Hat Enterprise Linux release 8.10 (Ootpa) with 12 cores and 64GB of memory.

mariadb_flashback01
mariadb_flashback01

Flashback test case

As we have seen you must set in my.cnf binlog_row_image = FULL (default value) and have the binary log format set to ROW. For the second option either you execute the mariadbd daemon with –flashback option:

--flashback         Setup the server to use flashback. This enables binary
                    log in row mode and will enable extra logging for DDL's
                    needed by flashback feature

Or set the parameter in my.cnf, which I have chosen to do:

binlog_format = row
binlog_row_image = full

Then I create a test database and a test01 table to perform my testing:

MariaDB [(none)]> create database test character set utf8mb4 collate utf8mb4_general_ci comment 'My test database';
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table test01(id int, descr varchar(20));
Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> insert into test01(id, descr) values (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five');
Query OK, 5 rows affected (0.012 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | One   |
|    2 | Two   |
|    3 | Three |
|    4 | Four  |
|    5 | Five  |
+------+-------+
5 rows in set (0.001 sec)

Flashback testing

I have this binary log situation:

MariaDB [test]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000093 |       790 |
| mariadb-bin.000094 |      1567 |
| mariadb-bin.000095 |      1000 |
+--------------------+-----------+
3 rows in set (0.000 sec)

MariaDB [test]> show binlog status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000095 |     1000 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Also recall that by default auto commit is activated:

MariaDB [test]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.002 sec)

I take the current time to know at what time the table was in good shape and execute a mistaken DML statement:

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-10-22 15:40:22 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [test]> update test01 set descr=upper(descr);
Query OK, 5 rows affected (0.002 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [test]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000095 |     1316 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [test]> show binlog events in 'mariadb-bin.000095' from 1000;
+--------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name           | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+--------------------+------+----------------+-----------+-------------+--------------------------------------+
| mariadb-bin.000095 | 1000 | Gtid           |         1 |        1042 | BEGIN GTID 0-1-36092397              |
| mariadb-bin.000095 | 1042 | Annotate_rows  |         1 |           0 | update test01 set descr=upper(descr) |
| mariadb-bin.000095 | 1101 | Table_map      |         1 |           0 | table_id: 22 (test.test01)           |
| mariadb-bin.000095 | 1153 | Update_rows_v1 |         1 |           0 | table_id: 22 flags: STMT_END_F       |
| mariadb-bin.000095 | 1285 | Xid            |         1 |        1316 | COMMIT /* xid=18 */                  |
+--------------------+------+----------------+-----------+-------------+--------------------------------------+
5 rows in set (0.006 sec)

Then as suggested in official documentation, I can use the mariadb-binlog tool to extract the culprit DML and the one to revert it. One without –flashback option for redo job and one with –flashback to rollback:

mariadb-binlog /mariadb/logs/mariadb01/mariadb-bin.?????? -vv --database=test --table=test01 --start-datetime="2025-10-22 15:40:22" > review.sql

mariadb-binlog /mariadb/logs/mariadb01/mariadb-bin.?????? -vv --database=test --table=test01 --start-datetime="2025-10-22 15:40:22" --flashback > flashback.sql

Remark:
You can also use –start-position if you know the exact binary log position. You can also club with –stop-datetime/–stop-position if you want to revert only a specific period of time !

The result SQL files are not really readable by a normal human being. Some parts are anyways readable, like in the flashback.sql (same typical command is also in review.sql but to do the mistake again):

BINLOG '
z974aBMBAAAANAAAAAAAAAAAABYAAAAAAAEABHRlc3QABnRlc3QwMQACAw8CUAAD3vIkSQ==
z974aBgBAAAAhAAAAAAAAAAAABYAAAAAAAEAAgMD/AUAAAAERklWRfwFAAAABEZpdmX8BAAAAARG
T1VS/AQAAAAERm91cvwDAAAABVRIUkVF/AMAAAAFVGhyZWX8AgAAAANUV0/8AgAAAANUd2/8AQAA
AANPTkX8AQAAAANPbmWzMsFt
'/*!*/;
### UPDATE `test`.`test01`
### WHERE
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='FIVE' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='Five' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
.
.
.

So let’s try to execute the flashback.sql script even if it looks quite stressful to execute this strange script on a production database:

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | ONE   |
|    2 | TWO   |
|    3 | THREE |
|    4 | FOUR  |
|    5 | FIVE  |
+------+-------+
5 rows in set (0.001 sec)

MariaDB [test]> source flashback.sql
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.028 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.011 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | One   |
|    2 | Two   |
|    3 | Three |
|    4 | Four  |
|    5 | Five  |
+------+-------+
5 rows in set (0.001 sec)

Blissful isn’t it ? I recall we restored a complete server for a user that has forgotten to put a where clause when updating a table. This would have been much easier to use flashback…

The captain obvious suggestion is to include your DML into a start transaction, then you will be able to rollabck it in case of mistake:

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | One   |
|    2 | Two   |
|    3 | Three |
|    4 | Four  |
|    5 | Five  |
+------+-------+
5 rows in set (0.001 sec)

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> update test01 set descr=upper(descr);
Query OK, 5 rows affected (0.001 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | ONE   |
|    2 | TWO   |
|    3 | THREE |
|    4 | FOUR  |
|    5 | FIVE  |
+------+-------+
5 rows in set (0.001 sec)

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.043 sec)

MariaDB [test]> select * from test01 order by id;
+------+-------+
| id   | descr |
+------+-------+
|    1 | One   |
|    2 | Two   |
|    3 | Three |
|    4 | Four  |
|    5 | Five  |
+------+-------+
5 rows in set (0.001 sec)

References

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *