Recover MySQL Data by Parsing Binlogs with reverse_sql

Tool Overview

reverse_sql is an open‑source command‑line tool for parsing MySQL binary logs (binlog) and converting the recorded DML events (INSERT, UPDATE, DELETE) into readable SQL statements and their reverse counterparts, enabling data recovery after accidental operations. The tool requires the binlog to be in ROW format.

Key Features

Binlog parsing : Reads MySQL binlog files and reconstructs the original SQL.

SQL generation : Produces both forward (original) and reverse SQL statements.

Filtering : Allows filtering by time range, table name, or DML type.

Multithreading : Supports concurrent parsing of binlog events.

Note: reverse_sql only outputs SQL; it does not execute any statements on the database.

Principle

The tool leverages the python-mysql-replication library. It divides the total time range into equal segments for each worker thread. Because BinLogStreamReader cannot start from a timestamp, each thread begins from the binlog file and position left by the previous thread, ensuring continuous, non‑overlapping processing.

For example, with a start timestamp of 1625558400 and 4 threads, the time range is split so that thread 0 starts at 1625558400, thread 1 at 1625558400 + range, thread 2 at 1625558400 + 2 × range, and thread 3 at 1625558400 + 3 × range. After processing, results are merged and sorted.

Usage

chmod 755 reverse_sql</code>
<code>./reverse_sql --help</code>
<code>usage: reverse_sql [-h] [-ot ONLY_TABLES [ONLY_TABLES ...]] [-op ONLY_OPERATION] -H MYSQL_HOST</code>
<code>                   -P MYSQL_PORT -u MYSQL_USER -p MYSQL_PASSWD -d MYSQL_DATABASE</code>
<code>                   [-c MYSQL_CHARSET] --binlog-file BINLOG_FILE [--binlog-pos BINLOG_POS]</code>
<code>                   --start-time ST --end-time ET [--max-workers MAX_WORKERS] [--print]</code>
<code>options:</code>
<code>  -h, --help            show this help message and exit</code>
<code>  -ot ONLY_TABLES [ONLY_TABLES ...], --only-tables ONLY_TABLES [ONLY_TABLES ...]</code>
<code>                        Specify tables to recover (comma‑separated)</code>
<code>  -op ONLY_OPERATION, --only-operation ONLY_OPERATION</code>
<code>                        Specify the DML operation (insert/update/delete)</code>
<code>  -H MYSQL_HOST, --mysql-host MYSQL_HOST</code>
<code>                        MySQL host name</code>
<code>  -P MYSQL_PORT, --mysql-port MYSQL_PORT</code>
<code>                        MySQL port</code>
<code>  -u MYSQL_USER, --mysql-user MYSQL_USER</code>
<code>                        MySQL user name</code>
<code>  -p MYSQL_PASSWD, --mysql-passwd MYSQL_PASSWD</code>
<code>                        MySQL password</code>
<code>  -d MYSQL_DATABASE, --mysql-database MYSQL_DATABASE</code>
<code>                        Target database</code>
<code>  -c MYSQL_CHARSET, --mysql-charset MYSQL_CHARSET</code>
<code>                        Character set (default utf8)</code>
<code>  --binlog-file BINLOG_FILE</code>
<code>                        Binlog file name</code>
<code>  --binlog-pos BINLOG_POS</code>
<code>                        Binlog position (default 4)</code>
<code>  --start-time ST       Start timestamp</code>
<code>  --end-time ET         End timestamp</code>
<code>  --max-workers MAX_WORKERS</code>
<code>                        Number of threads (default 10)</code>
<code>  --print               Output parsed SQL to terminal</code>
<code>  --replace             Convert UPDATE to REPLACE</code>

<code>Example:</code>
<code>./reverse_sql -ot table1 -op delete -H 192.168.198.239 -P 3336 -u admin -p hechunyang -d hcy \
      --binlog-file mysql-bin.000124 \
      --start-time "2023-07-06 10:00:00" --end-time "2023-07-06 22:00:00"

When a mistaken DML operation occurs, specify the time window, the affected binlog file (obtainable via SHOW MASTER STATUS), the target table, and the operation type. The tool first checks that binlog_format='ROW' and binlog_row_image='FULL'; otherwise it exits.

After execution, a file named {db}_{table}_recover.sql is created in the current directory, containing the original SQL (with comments) and the reverse SQL. Use --print to display results on the console, or --replace to turn UPDATE statements into REPLACE statements, which generates an additional {db}_{table}_recover_replace.sql file.

Permissions

Minimal MySQL privileges required:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'yourname'@'%';</code>
<code>GRANT SELECT ON `test`.* TO 'yourname'@'%';

The tool supports MySQL 5.7/8.0 and MariaDB on CentOS 7.