my2sql: reading MySQL binlogs to rebuild or undo SQL

Three jobs from one binlog parse

my2sql is a Go version of a MySQL binlog parsing tool. It can generate the original SQL, generate the reverse SQL used for a rollback, generate INSERT statements with the primary key removed, and produce DML statistics, and the description also mentions large transaction analysis. The list of uses given in the README is what makes the tool more than a rollback utility. Fast data rollback, which the tool calls a flash-back, is the first. Repairing data lost on a new master after a replication switchover is the second. Generating standard SQL from a binlog is the third, and it is described as bringing derived capabilities with it. Then there is the analysis side: DML statistics that show which tables update most often, which is what you want when IO and TPS are high and you need to know which table is the culprit, and the detection of whether the database had a large or long transaction at a particular moment.

Two ways in: impersonate a replica or read the file

The mode parameter decides how the binlog is obtained. The value repl means the tool pretends to be a replica parsing the binlog files from a server, and file means offline parsing of binlog files. That distinction matters operationally. In repl mode the tool connects to the server with the credentials you give it and reads the binlog as a replica would, which means it can follow along as new events arrive. In file mode it parses files directly, and the local-binlog-file parameter supplies the relative or absolute path. The documentation notes that you can parse several binlog files in succession by naming only the first, because the program continues automatically onto the next file. The remaining selection parameters are the standard pair: you can bound the parse by a start and stop datetime, or by a start and stop file together with start and stop position.

The work types and what each produces

The work-type parameter has three values and it is the switch that decides what the tool writes. The value 2sql generates the original SQL, rollback generates the reverse SQL, and stats only collects DML and transaction information without producing statements. A typical invocation in repl mode looks like this, with the credentials and the datetime window supplied directly.

./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type 2sql  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir

The same run in file mode replaces the server connection with a local path, using the local-binlog-file parameter to point at the binlog file. The output goes to a directory by default, and there is an output-toScreen option to print to the console instead, which is what you want when you are streaming from a position and watching the statements arrive.

Rollback SQL and the shape of the generated WHERE clause

The interesting parameters are the ones that control how the reverse statements are constructed. The full-columns option decides whether unchanged columns are included in an UPDATE, and for update and delete statements whether all columns are used to build the WHERE condition. The default is false, which means the changed columns form the SET part and the primary or unique key forms the WHERE condition. Turning it on makes the WHERE clause match on every column, which produces a statement that only fires if the row is still exactly as it was, which is the safer behaviour when the table is being written to concurrently. The U flag does something related and simpler: it makes the tool prefer the unique key as the WHERE condition instead of the primary key, defaulting to false. The doNotAddPrifixDb option controls whether the generated SQL carries the database prefix on the table name.

Comments, filters and the stats mode

The add-extraInfo option writes the database, table, datetime, binlog and position information as a comment above each generated statement, defaulting to false. With it enabled, each statement is preceded by a line naming the database, the table, the timestamp and the binlog file with the start and stop positions, which is what makes a generated file auditable.

# datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'

The filtering options are databases and tables, comma separated, and the sql option restricts which statement types are parsed, with insert, update and delete as the choices and all three by default. For the stats work type there are two thresholds, big-trx-row-limit for a transaction touching at least a given number of rows with a default of 500, and long-trx-seconds for the duration, which the examples set to 300. The threads option sets the worker count, defaulting to eight, and file-per-table splits the output into one SQL file per table.

Why it is faster than the tool everyone uses

The README calls binlog2sql the most widely used MySQL rollback tool in the industry and then benchmarks the two on a 1.1 GB binlog. Generating rollback SQL takes 1 minute 40 seconds in my2sql against 65 minutes in binlog2sql. Generating the original SQL takes 1 minute 30 seconds against 50 minutes. Generating the per-table DML statistics and the transaction statistics takes 40 seconds in my2sql, and binlog2sql does not support that at all. The architecture behind the difference is visible in the module file. The Go code is built on go-mysql-org/go-mysql, but with a replace directive pointing it at a fork, github.com/liuhr/go-mysql, pinned to a commit from 2022. The project also depends on the go-sql-driver/mysql client and uses the dropbox godropbox and juju errors packages. The parallelism is the eight worker threads, but the fork is what lets the tool share a single parse across the three work types rather than re-reading the binlog for each.

Data types and the stated limits

One capability the README claims over its peers is support for data types most tools do not handle, naming json, blob, text and emoji. Building a correct INSERT for a JSON column or an emoji is harder than it sounds, because the value has to be escaped according to the column's type rather than treated as an opaque string, and getting it wrong produces a statement that fails on a value that was perfectly valid in the binlog. The limits are stated plainly and they are serious. The rollback and flash-back features require the binlog format to be row with binlog_row_image set to full, while the DML statistics and the large transaction analysis are unaffected by that. Only DML can be rolled back, never DDL. The table structure must be unchanged across the binlog segment being parsed, so an add column or drop column inside the range can make the generated rollback statements fail when executed. A time zone can be given with the t flag to interpret time and datetime fields in the binlog, and the start and stop datetimes are interpreted in that same zone.

Where my2sql is the wrong choice

Four limits apply. It is MySQL only, and specifically MySQL binlogs in row format, so it does nothing for PostgreSQL, MariaDB's other log formats, or a database whose binlog is in statement format. It cannot undo DDL, so a dropped column stays dropped and the tool's output will not restore the schema. Its rollback correctness depends on assumptions you cannot check from the output alone, since the table structure must match across the parsed range and the primary or unique key must still be the same. And the lineage matters for trust: the README says the tool is a secondary development based on my2fback and a binlog_rollback tool, and it ships a fork of the go-mysql library through a replace directive, so you are running a patched dependency. There are also no GitHub releases for the project itself, and while a compiled Linux binary for CentOS 7 is published, the last push to the repository was on 2026-04-23.

Editorial conclusion

Use my2sql when you need to recover what a mistake deleted, to rebuild readable SQL from a binlog, or to find the table that is churning rows, since it handles all three from one parse and writes the SQL out rather than replaying it. Do not rely on it as a general undo for a production incident, because the rollback path needs row format binlogs with full row images, it cannot reverse DDL at all, and a schema change inside the parsed range can make the generated statements fail. Verify first that binlog_format is ROW and binlog_row_image is FULL on the server whose binlog you are about to read, that no ALTER ran between the start and stop positions you are parsing, and that the output goes to a file you can inspect before any of it is executed.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. liuhr/my2sql on GitHub
4. README