Using MySQL Flashback Tools to Roll Back DML Mistakes

Before Using Flashback

When a DML mistake occurs, flashback tools can roll back the operation.

Which SQL Types Can Be Flashed Back?

The flashback tool supports DELETE , INSERT , and UPDATE statements; DDL is not supported.

Pre‑conditions for Using Flashback

The following binlog parameters must be set: binlog_row_image=full and binlog_format=row . After the mistaken DML, the table schema must not have added or removed columns because the binlog does not contain column names.

Do You Need a Tool for Small Data Volumes?

If the affected data set is small, executing flashback statements directly on the application side is more efficient, but the statements must be confirmed and run by the application.

Recommended Flashback Tools

The most common MySQL flashback tools are binlog2sql , my2sql , and MyFlash .

Basic Information

binlog2sql

my2sql

MyFlash

MySQL Supported Versions

5.7

5.7 / 8.0

5.7

Offline Parsing

No

Yes

Yes

Language

Python

Go

C

Last Update

2018.10

2022.11

2020.11

Feature Highlights

binlog2sql : rolls back DML and can generate original SQL.

my2sql : rolls back DML, generates original SQL, and can also provide DML, long‑transaction, and large‑transaction statistics.

MyFlash : rolls back DML and creates binlog‑type rollback files.

Overall, my2sql is recommended for DML rollback. The following sections focus on MyFlash and my2sql.

MyFlash

MyFlash, developed by Meituan‑Dianping, parses v4 binlog files to roll back DML operations. It offers many filter options and works fully offline, generating rollback binlog files, but only supports single‑file parsing.

Recommended Approach : specify GTID range, which is equivalent to timestamp and position points.

Usage Example

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 a small‑scale DML mistake and run the flashback command on the application side.

Execute rollback with GTID inclusion:

--include-gtids

The generated rollback file is named 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; it records the local GTID. Use set sql_log_bin=0 to avoid logging the import.

my2sql

my2sql is a Go‑based MySQL binlog parser that can generate original SQL, rollback SQL, or DML statistics. Since version 2.0 it supports the caching_sha2_password plugin.

Specify the work type with the work-type parameter (e.g., -work-type 2sql for original SQL, -work-type rollback for rollback SQL).

Core Parameters

-start-file : starting binlog file name.

-work-type : work type (2sql, rollback, stats).

-mode : repl (replica mode) or file (offline parsing).

-local-binlog-file : path to the binlog file when using -mode file .

Generating Rollback SQL

Example command (offline 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

The command produces three files: rollback.6.sql (rollback statements), biglong_trx.txt (large‑transaction info), and binlog_status.txt (DML statistics).

Generating Original SQL

Use -work-type 2sql with the same parameters as above. The output files are forward.6.sql (original DML), biglong_trx.txt , and binlog_status.txt .

Thinking: How to Prevent Mistakes?

Flashback tools are a remedial measure; robust operations should focus on prevention:

Prevention : reduce accidental deletions through delivery baselines.

Technical Recovery (PITR) : use backups and binlog replay.

Technical Recovery (Flashback) : roll back DML via binlog.

Technical Recovery (Undrop‑for‑InnoDB) : file‑level restoration.

By preventing issues first and then addressing them, operational satisfaction improves.

References

[1] binlog2sql: https://github.com/danfengcao/binlog2sql

[2] my2sql: https://github.com/liuhr/my2sql

[3] MyFlash: https://github.com/Meituan-Dianping/MyFlash