Using my2sql: A Go‑Based Binlog Parsing Tool for Flashback, Forwarding, and Transaction Analysis

Introduction

Most DBAs are familiar with flashback tools such as binlog2sql and MyFlash. This article presents another Go‑based binlog parsing tool called my2sql , which not only provides flashback functionality but also generates forward SQL for data compensation and analyzes online transactions.

GitHub address: https://github.com/liuhr/my2sql

Comparison

binlog2sql: written in Python, requires Python 2.7 or 3.4+ environment, generates rollback/forward SQL for data recovery.

MyFlash: written in C, needs to be compiled into a binary script, generates reverse binlog files for recovery.

my2sql: written in Go, provides a ready‑to‑run Linux binary, supports flashback, forward SQL, and transaction analysis.

Main Parameters

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

-sql: filter DML type (insert, update, delete).

-ignorePrimaryKeyForInsert: ignore primary key for insert when work‑type is 2sql.

-big-trx-row-limit int: threshold for large transactions (default 500 rows, range 10‑30000).

-long-trx-seconds int: threshold for long transactions (default 300 seconds, range 1‑3600).

-databases, -tables: filter databases/tables (default all).

-start-file, -start-pos, -start-datetime, -stop-datetime: specify binlog range.

-output-dir, -output-toScreen, -tl: output directory, screen output, time zone.

Application Scenarios

Scenario 1: Flashback DML Mistake

-- create test table
mysql> show create table t\G
... (table definition) ...
-- delete 5000 rows
mysql> delete from t where id<5001;
-- generate rollback SQL
./my2sql -user zlm -password zlm -host 10.186.60.62 -port 3332 -work-type rollback -start-file mysql-bin.000003 -start-pos 15994082 --add-extraInfo -output-dir /tmp/my2sql_test
# output files: rollback.3.sql (contains 5000 INSERT statements), binlog_status.txt, biglong_trx.txt
# import rollback SQL
mysql < /tmp/my2sql_test/rollback.3.sql
# verify row count and checksum match original

Scenario 2: Manual Compensation After Master‑Slave Failover

-- start continuous writes with sysbench
sysbench ... run
-- stop slave IO thread, capture GTID and binlog positions
show slave status\G
show master status;
-- generate forward SQL from the missing binlog range
./my2sql -user zlm -password zlm -host 10.186.60.62 -port 3332 -work-type 2sql -start-file mysql-bin.000013 -start-pos 78769827 --add-extraInfo -output-dir /tmp/my2sql_test
# forward.13.sql contains INSERT/UPDATE/DELETE statements (total 105932)
# apply to new master
mysql < /tmp/my2sql_test/forward.13.sql
# verify checksums on both old and new masters

Scenario 3: Online Transaction Analysis

3.1 Preparation

-- create a 5 million row table and keep writing with sysbench
sysbench ... --table_size=5000000 ... run
-- check master and slave status
show master status; show slave status\G
-- analyze binlog for statistics
./my2sql -user zlm -password zlm -host 10.186.60.68 -port 3332 -work-type stats -start-file mysql-bin.000018 -start-pos 194 -output-dir /tmp/my2sql_test

3.2 Large Transaction Analysis

-- delete 4 million rows without primary key (simulates large transaction)
alter table sbtest1 modify id int not null, drop primary key;
delete from sbtest1 where id<4000001;
-- run my2sql with big‑trx‑row‑limit 10000
./my2sql -user zlm -password zlm -host 10.186.60.68 -port 3332 -work-type stats -start-file mysql-bin.000018 -big-trx-row-limit 10000 -output-dir /tmp/my2sql_test
# biglong_trx.txt shows a 4,000,000‑row delete transaction

3.3 Long Transaction Analysis

-- create a simple table and run a 10‑second transaction
create table t1(id int);
begin; insert into t1 values(1); select sleep(10); commit;
-- run my2sql with long‑trx‑seconds 5
./my2sql -user zlm -password zlm -host 10.186.60.68 -port 3332 -work-type stats -start-file mysql-bin.000019 -long-trx-seconds 5 -output-dir /tmp/my2sql_test
# biglong_trx.txt records the 10‑second transaction

Limitations

Requires SELECT, REPLICATION SLAVE, REPLICATION CLIENT privileges.

Does not support MySQL 8.0, needs binlog_format=row and binlog_row_image=full for flashback.

Cannot parse binlog offline; cannot parse by GTID; flashback SQL lacks explicit BEGIN/COMMIT boundaries.

Transaction analysis only reports time, position, objects, and type, not the full SQL statements (requires binlog_rows_query_log_events=on).

Summary

my2sql is a practical binlog parser that can flashback DML errors, generate forward SQL for data compensation, and help diagnose replication lag via transaction analysis.

Implemented in Go, it provides a ready‑to‑run Linux binary with no external dependencies.

Performance is claimed to be 50‑60× faster than binlog2sql, making it suitable for large‑scale environments.

Compared with MyFlash, my2sql offers human‑readable SQL output and broader functionality.