Enhancing my2sql for Data Rollback and Query Capabilities with DBeaver Integration

The article outlines enhancements made to the open‑source my2sql utility, which originally excelled at fast binlog parsing for DML rollback. It identifies common developer pain points such as accidental data deletion and the need to trace the original SQL that caused data changes.

Two major improvements are introduced:

Implementation of a query capability by parsing the Rows_query event, allowing my2sql to retrieve the original SQL statements for a given table and operation type.

Simplified binlog selection: instead of requiring users to specify a start‑binlog file, the tool now accepts a time range, automatically determines the relevant binlog files by inspecting the first event of each binlog, and processes only the necessary range, greatly reducing execution time.

A sample command‑line invocation after the enhancements looks like this:

./my2sql -user root -password xxx -host 127.0.0.1 -port 3306 \
-databases test -tables a -sql delete \
-start-datetime "2021-04-02 11:05:00" -stop-datetime "2021-04-02 11:06:00" \
-work-type rollback \
-add-extraInfo -output-dir /opt/tmp

To address the lack of a graphical interface, the author integrated the improved my2sql into the DBeaver database client. By selecting database, table, and operation type within DBeaver, the external my2sql program is invoked to generate the appropriate rollback or query scripts, leveraging DBeaver’s stored connection information.

The article also includes a raw binlog excerpt illustrating how Rows_query appears in the log and how the generated DELETE statements look after parsing:

BEGIN
/*!*/;
# at 291
#220117 12:49:23 server id 1000000  end_log_pos 328 CRC32 0xc0df907c    Rows_query
# delete from a
# at 328
#220117 12:49:23 server id 1000000  end_log_pos 373 CRC32 0x7bc9707d    Table_map: `test`.`a` mapped to number 5705
# at 373
#220117 12:49:23 server id 1000000  end_log_pos 435 CRC32 0x15ae3096    Delete_rows: table id 5705 flags: STMT_END_F
### DELETE FROM `test`.`a`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
COMMIT
/*!*/;

In the concluding section, the author notes that while the enhanced my2sql may not be as precise as a DBA manually selecting binlog positions, it provides sufficient functionality for developers, allowing them to filter out unrelated rollback statements. The tool currently supports only DML statements; DDL recovery still relies on backups and binlog replay.