Flashback @MariaDB 10.3.8

Neverending story. Commited changes need to be rollback. Some vendors are not resolving this situation at all (stupidity of user is not their problem, indeed), some are providing solutions (Oracle). One of the latest news from MariaDB (10.2) was adding this feature into mysqlbinlog utility.

Let’s create data with faulty DELETE command inside 🙂 :

Situation is pretty clear – in table t1 there are 1-7 numbers, inserted in 3 transactions (AUTOCOMMIT is on) and after accidentaly deleting this table (beware for TRUNCATE TABLE, it is not written into binary log at all!) I created table t2 with just 3 values.

1) Locating where is our DELETE command residing.

As long as we are storing binary (transactional) logs,we  are able to flashback any transaction. Going through hundreds of gigabytes is a bit boring -> it is good to know when this transaction happened (~hours precision). After extracting binary log into human-readable format we will grep DELETE command. Enough of theory:

On fresh MariDB installation it’s not a big deal to detect proper binary log file (in case you are having tons of these files, just extract suspicious ones). Target file looks like this:

We can grab string „DELETE“ or just annotated „Delete_rows“ into grep command to find out what range of lines will be in our interest:

Bingo! In case of huge line numbers, grep it better and extract into standalone file. Please have in mind that transaction begins right after BEGIN word and ending right before COMMIT word. In our case 1212 and 1398. Extracted paragraph is performed by:


2) Preparing flashback binlog fragment

Looks great, we can prepare again those lines, this time with clause –flashback, opposite operation will be generated:

There is no surprise how resulted file will be looking:

See those changes? It reverted original operation (DELETE) and you are having few INSERT INTO lines there. We need to convert this fragment into binary log format again:


3) Propagating changes into database + verification

From mariadb client we will process this „flash“:

And verification is really easy:

Flashback operation performed as expected. Newly created table t2 is intacted and „bad“ DELETE operation was successfully replaced by original values (INSERT INTO).


This entry was posted in MySQL. Bookmark the permalink.

Comments are closed.