Recover Accidentally Deleted MySQL/MariaDB Data with Binlog Flashback

Introduction

Many developers have accidentally run DELETE or UPDATE statements without proper WHERE clauses, causing entire tables to be modified or erased.

Traditional Solution

Restore from the latest full backup combined with incremental binlog backups.

This approach becomes time‑consuming as table size and binlog volume grow.

A Simpler Method

Yes, you can simulate a flashback feature for MySQL/MariaDB by parsing the binlog.

Although MySQL (including 5.7) and MariaDB 10.1 lack native flashback, you can recreate it manually.

Note Before using this method, ensure binlog_format = ROW . It will not work with STATEMENT format.

What Is “Flashback”?

It works like a Windows Recycle Bin: by analyzing the binlog, you can reconstruct the data that was deleted. Important DROP/TRUNCATE operations cannot be flashback‑recovered; they still require full backup + binlog restoration.

How to Perform the Flashback

Scenario

1. Test table with 10 rows:

2. Accidentally executed DELETE without WHERE, removing all rows:

Recovery Steps

1. Locate the binlog that contains the erroneous DELETE.

2. Identify the exact DELETE statement in the binlog.

Note the red‑boxed fields @1,@2,@3,@4 corresponding to the original table columns.

3. Save the relevant binlog segment for later use.

Inspecting recover.binlog yields the raw data needed for reconstruction.

4. Convert the DELETE entries into REPLACE INTO statements.

Note: @4 represents the last column in this example; adjust to @10 if your table has ten columns.

5. Execute the generated SQL to restore the data.

<code># source /root/recover.sql</code>

After running the script, the table is restored to its original state.

Friendly Reminder Never test this procedure on a production environment.

For more advanced techniques, see the author’s book “MySQL Management: Second Edition”.

Reference

<code>https://www.percona.com/blog/2012/10/19/recovering-from-a-bad-update-statement/</code>