Comparison of my2sql and binlog2sql for MySQL Binlog Parsing

Introduction

In routine MySQL operations, administrators often need to parse binary logs (binlog) to generate original SQL statements, rollback SQL, or extract DML statistics. Two popular open‑source tools for this purpose are my2sql (a Go‑based tool derived from my2fback and binlog_rollback) and binlog2sql (a Python‑based parser).

my2sql Parameters

Parameter

Description

-mode

repl: act as a replica to fetch binlog from master; file: read binlog from local file (default repl)

-local-binlog-file

Path to binlog file when

-mode=file

is used

-sql

SQL type to parse (insert, update, delete); default parses all

-file-per-table

Generate a separate SQL file for each table

-output-dir

Directory to store generated results

-threads

Number of concurrent threads (default 2)

-work-type

2sql: generate original SQL; rollback: generate rollback SQL; stats: only collect DML/transaction stats

binlog2sql Parameters

Parameter

Description

--stop-never

Continuously parse binlog until the latest position (default false)

-B, --flashback

Generate rollback SQL; supports large files without memory limits (cannot be used with

--stop-never

or

--no-primary-key

)

--start-file

Starting binlog file name (no full path needed)

--start-position / --start-pos

Starting position within the binlog file (default is start of file)

--stop-file / --end-file

Ending binlog file (default same as start file; ignored if

--stop-never

is set)

--stop-position / --end-pos

Ending position within the binlog file (optional)

--sql-type

Parse only specified SQL types (INSERT, UPDATE, DELETE); multiple types separated by spaces

Benchmark Setup

## 查看mysql的相关参数设置
mysql> select @@server_id,@@binlog_format,@@binlog_row_image,@@max_binlog_size,@@log_bin_basename;
## 使用sysbench创建测试数据表
[root@10-186-61-119 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.61.119 \
--mysql-port=3309 --mysql-user=root --mysql-password=root --mysql-db=test --table-size=2000000 --tables=1  \
--threads=50 --db-ps-mode=disable --auto_inc=off --report-interval=3 --max-requests=0 --time=180 \
--percentile=95 --skip_trx=off --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 --create_secondary=off run

my2sql Parsing Process

## 1 thread：使用脚本实现连续解析两个binlog的功能
[root@10-186-61-119 my2sql]# cat /root/sql.sh
#! /bin/bash
date;
my2sql -user root -password root -host 10.186.61.119   -port 3309 -mode file  \
-local-binlog-file /data/mysql/3309/binlog/mysql-bin.000013  -work-type 2sql \
-start-file /data/mysql/3309/binlog/mysql-bin.000013 -threads 1 \
-output-dir /data/my2sql/1thread/13 > /data/my2sql/1thread/13/output;
my2sql -user root -password root -host 10.186.61.119   -port 3309 -mode file \
-local-binlog-file /data/mysql/3309/binlog/mysql-bin.000014  -work-type 2sql \
-start-file /data/mysql/3309/binlog/mysql-bin.000014 -threads 1 \
-output-dir /data/my2sql/1thread/14 > /data/my2sql/1thread/14/output

binlog2sql Parsing Process

## 使用脚本实现连续解析两个binlog的功能
[root@10-186-61-119 ~]# cat binlog.sh
#! /bin/bash
date;
python /data/binlog2sql/binlog2sql/binlog2sql.py -h10.186.61.119 -P3309 -uroot -p'root' \
--start-file='mysql-bin.000013' > /tmp/binlog13.sql;
python /data/binlog2sql/binlog2sql/binlog2sql.py -h10.186.61.119 -P3309 -uroot -p'root' \
--start-file='mysql-bin.000014' > /tmp/binlog14.sql;

Benchmark Results

Binlog Tool

Binlog Size

Concurrency

Completion Time

my2sql

2 GB

1

11 min

my2sql

2 GB

4

12 min

my2sql

2 GB

6

14 min

my2sql

2 GB

8

17 min

binlog2sql (no concurrency)

2 GB

1

54 min

Feature Comparison

Feature

my2sql

binlog2sql

Notes

Generate standard SQL, rollback SQL, INSERT without primary key

Supported

Supported

my2sql can split output per table; binlog2sql produces a single file

Parse large or long transactions

Supported

Not supported

my2sql can handle big transactions via parameters

DML statistics and hotspot table detection

Supported

Not supported

Offline mode parsing

Not supported

Not supported

Both require DB connection to fetch table metadata

Conclusion

my2sql outperforms binlog2sql in parsing speed for 2 GB binlog files, even though increasing thread count does not significantly reduce total time; the default thread count is recommended.

my2sql also offers additional capabilities such as large‑transaction handling and DML statistics, making it a more versatile tool.

Limitations & Precautions

my2sql acts as a replica; the MySQL user must have SELECT , REPLICATION SLAVE , and REPLICATION CLIENT privileges.

Rollback/flashback requires binlog format ROW and binlog_row_image=FULL .

Only DML statements can be rolled back; DDL cannot.

my2sql may have parsing bugs with certain data types (see GitHub issue #31 ).

binlog2sql requires MySQL server settings: server_id , log_bin , max_binlog_size=1G , binlog_format=ROW , binlog_row_image=FULL , and the same replication privileges as my2sql.

References

my2sql GitHub repository

binlog2sql GitHub repository