Top MySQL Flashback Tools for DML Recovery and Rollback

When incorrect DML operations are executed in a database, flashback tools can help roll back these operations.

Before Using Flashback Tools

What SQL Types Are Supported?

Flashback tools can roll back DELETE, INSERT, UPDATE operations but do not support DDL.

What Conditions Are Required?

Before using flashback tools, ensure the following parameters are met: binlog_row_image=full and binlog_format=row. Additionally, the table structure must not have been altered after the incorrect DML operation (e.g., adding or dropping columns, as binlog does not contain column names).

Do I Need a Tool for Mistakes?

If the DML operation involves a small amount of data, it is recommended to execute flashback statements directly on the application side for higher efficiency. However, these statements must be confirmed and executed at the application layer.

Recommended Flashback Tools

The mainstream MySQL flashback tools are binlog2sql, my2sql, and MyFlash.

Basic Information

Features

binlog2sql: Rolls back DML and generates rollback or original SQL.

my2sql: Rolls back DML, generates rollback or original SQL, and provides DML statistics and analysis of long or large transactions.

MyFlash: Rolls back DML and generates rollback files in binlog format.

Among these, my2sql is highly recommended for rolling back DML operations. Below, we focus on the usage of MyFlash and my2sql.

MyFlash

MyFlash is a tool developed by Meituan-Dianping for rolling back DML operations. It parses v4 binlog files and offers more filtering options for easier rollback. It is used internally at Meituan-Dianping.

MyFlash can operate completely offline as it only generates rollback binlog files but supports parsing a single file only.

Recommended Approach: Specify a GTID range, equivalent to a time point and position.

Usage

show master status\G
File: mysql-bin.000014
Executed_Gtid_Set: 52875cf3-e9d3-11ef-88ab-02000aba3d09:120764

checksum table test_table;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test01.test_table | 1479091061 |
+-------------------+------------+

‍

Simulate incorrect operations with transaction IDs 120765-120766.

‍

update test_table set blob_field='Tom' where id <900;
update test_table set blob_field='Jreey' where id >900 and id<1000;

‍

Execute rollback using --include-gtids. The default rollback file name is binlog_output_base.flashback.

‍

# cd  MyFlash-master/binary/
./flashback --binlogFileNames=mysql-bin.000014 --databaseNames=test01 --tableNames=test_table --sqlTypes='delete,insert,update' --include-gtids='52875cf3-e9d3-11ef-88ab-02000aba3d09:120765-120766'

‍

Apply the rollback binlog file, which records local GTID transaction numbers.

‍

mysqlbinlog -vv binlog_output_base.flashback --skip-gtids|mysql -uroot -p

‍

Verify the results are consistent!

‍

checksum table test_table;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test01.test_table | 1479091061 |
+-------------------+------------+

‍

⚠️ Notes:

binlogFileNames: Specify the binlog file to rollback (single file only).

sqlTypes: Specify a single type, e.g., --sqlTypes='delete'.

include-gtids: Specify GTIDs to rollback (single or range).

exclude-gtids: Specify GTIDs to exclude (same usage as include-gtids).

my2sql

my2sql is a Go-based MySQL binlog parsing tool that generates original SQL, rollback SQL, and INSERT SQL without primary keys. It also provides DML statistics. It is based on my2fback and binlog_rollback tools and supports the caching_sha2_password plugin from version 2.0.

my2sql uses the work-type parameter to generate either original SQL (-work-type 2sql) or rollback SQL (-work-type rollback).

Recommended Approach: Specify clear start and end times, and use file mode for minimal database intrusion.

Core Parameters

-start-file: Specify the binlog file to start reading from.

• -work-type: Work type:
  2sql
  : Generate original SQL.
  rollback
  : Generate rollback SQL.
  stats
  : Only statistics for DML and transactions.
• -mode: Mode to obtain binlog files:
  repl
  : Pretend to be a replica (default).
  : Offline parsing (recommended).

Generating Rollback SQL

The generated SQL file updates GTID upon import. To avoid GTID updates, use set+source.

show master status\G
File: mysql-bin.000006
Position: 255561
52875cf3-e9d3-11ef-88ab-02000aba3d09:1-34

select count(*) from t01;checksum table t01;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test01.t01 | 1658863531 |
+------------+------------+

‍

Simulate a DELETE operation that deletes 499 rows.

delete from t01 where id < 500;

‍

Find the start time of the incorrect operation, e.g., 250307 14:12:06, and locate the binlog file. You can also search for the incorrect statement directly.

‍

mysqlbinlog --base64-output=decode-rows -vv --database test01 mysql-bin.000006 |grep  -C 10 -i "Rows_query" |grep -C 10    -i "t01" |grep -C 10 -i "delete" > t01_detele.log
#250307 14:12:06 server id 110  end_log_pos 255759      Rows_query
# delete from t01 where id < 500

‍

Find the end time, e.g., 250307 14:38:11 (next transaction start time).

‍

#250307 14:12:06 server id 110  end_log_pos 265277      Xid = 445
COMMIT/*!*/;
# at 265277
#250307 14:38:11 server id 110  end_log_pos 265350

‍

Choose one of the following approaches.

Approach 1: Parsing binlog files directly (file mode)

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313 -mode file -local-binlog-file ./mysql-bin.000004  -work-type rollback  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir

‍

Approach 2: Pretending to be a replica

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313  -work-type rollback  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir01

‍

Both approaches generate the same results, producing three files:

rollback.6.sql: Rollback SQL file.

biglong_trx.txt: Large transaction information.

binlog_status.txt: DML statistics.

Verify the rollback matches the deleted data. The verification result is consistent.

cat rollback.6.sql |grep "INSERT INTO"|wc -l
499

‍

To avoid GTID updates, use set sql_log_bin=0; before importing.

set sql_log_bin=0;
source /tools/my2sql_test/tmpdir/rollback.6.sql;
select count(*) from t01;checksum table t01;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test01.t01 | 1658863531 |
+------------+------------+

‍

⚠️ Notes:

The base64-output=decode-rows parameter in mysqlbinlog decodes Base64-encoded row data for readability.

The # datetime=... line in the flashback SQL file is added by the -add-extrainfo parameter, providing execution time and position details.

The -databases parameter in my2sql filters specific databases.

Generating Original SQL

Use work-type=2sql with the same parameters as generating rollback SQL.

Approach 1: Parsing binlog files directly

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313 -mode file -local-binlog-file ./mysql-bin.000004  -work-type 2sql  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir

‍

Approach 2: Pretending to be a replica

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313  -work-type 2sql  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" --add-extraInfo -output-dir ./tmpdir01

‍

Both approaches generate the same results, producing three files:

forward.6.sql: Original SQL file (original incorrect operation statements).

biglong_trx.txt: Large transaction information.

binlog_status.txt: DML statistics.

View the biglong_trx.txt file:

‍

cat biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql-bin.000006  2025-03-07_14:12:06 2025-03-07_14:12:06 255636     265277     499      0     [test01.t01(inserts=0, updates=0, deletes=499)]

‍

View the binlog_status.txt file for DML statistics in the specified time range:

cat binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql-bin.000006  2025-03-07_14:12:06 2025-03-07_14:12:06 255759     265250     0        0        499      test01          t01                 

‍

🤔 How to Prevent Mistakes?

Using flashback tools is just a remedial measure. What proactive steps can operations teams take to prevent mistakes?

A data protection system (MySQL version) that lets operations teams sleep soundly:

Prevention: Reduce the likelihood of data deletion through delivery baselines.

Technical Recovery (Point-in-Time Recovery): Restore from backups and binlog replay in case of accidental deletions.

Technical Recovery (Flashback Tools): Roll back DML operations using binlog.

Technical Recovery (Undrop-for-InnoDB): Disk-level file recovery (though with some limitations).

Prevent issues from happening and solve them when they do, improving operational satisfaction!