工具簡(jiǎn)介

reverse_sql是一個(gè)用于解析和轉(zhuǎn)換MySQL二進(jìn)制日志（binlog）的工具。它可以將二進(jìn)制日志文件中記錄的數(shù)據(jù)庫(kù)更改操作（如插入、更新、刪除）轉(zhuǎn)換為反向的SQL語(yǔ)句，以便進(jìn)行數(shù)據(jù)恢復(fù)。其運(yùn)行模式需二進(jìn)制日志設(shè)置為ROW格式。

該工具的主要功能和特點(diǎn)包括：

1、解析二進(jìn)制日志：reverse_sql能夠解析MySQL的二進(jìn)制日志文件，并還原出其中的SQL語(yǔ)句。

2、生成可讀的SQL：生成原始SQL和反向SQL。

3、支持過(guò)濾和篩選：可以根據(jù)時(shí)間范圍、表、DML操作等條件來(lái)過(guò)濾出具體的誤操作SQL語(yǔ)句。

4、支持多線(xiàn)程并發(fā)解析binlog事件。

請(qǐng)注意！reverse_sql只是將二進(jìn)制日志還原為SQL語(yǔ)句，而不會(huì)執(zhí)行這些SQL語(yǔ)句來(lái)修改數(shù)據(jù)庫(kù)。

原理

調(diào)用官方（https://python-mysql-replication.readthedocs.io/）庫(kù)來(lái)實(shí)現(xiàn)，通過(guò)指定的時(shí)間范圍，轉(zhuǎn)換為timestamp時(shí)間戳，將整個(gè)時(shí)間范圍平均分配給每個(gè)線(xiàn)程。

由于BinLogStreamReader并不支持指定時(shí)間戳來(lái)進(jìn)行遞增解析，固在每個(gè)任務(wù)開(kāi)始之前，使用上一個(gè)任務(wù)處理過(guò)的binlog_file和binlog_pos，這樣后續(xù)的線(xiàn)程就可以獲取到上一個(gè)線(xiàn)程處理過(guò)的binlog文件名和position，然后進(jìn)行后續(xù)的并發(fā)處理。

假設(shè)開(kāi)始時(shí)間戳start_timestamp是1625558400，線(xiàn)程數(shù)量num_threads是4，整個(gè)時(shí)間范圍被平均分配給每個(gè)線(xiàn)程。那么，通過(guò)計(jì)算可以得到以下結(jié)果：

對(duì)于第一個(gè)線(xiàn)程（i=0），start_time是1625558400。

對(duì)于第二個(gè)線(xiàn)程（i=1），start_time是1625558400 + time_range。

對(duì)于第三個(gè)線(xiàn)程（i=2），start_time是1625558400 + 2 * time_range。

對(duì)于最后一個(gè)線(xiàn)程（i=3），start_time是1625558400 + 3 * time_range。

這樣，每個(gè)線(xiàn)程的開(kāi)始時(shí)間都會(huì)有所偏移，確保處理的時(shí)間范圍沒(méi)有重疊，并且覆蓋了整個(gè)時(shí)間范圍。最后，將結(jié)果保存在一個(gè)列表里，并對(duì)列表做升序排序，取得最終結(jié)果。

使用

shell> chmod 755 reverse_sql
shell> ./reverse_sql --help
usage: reverse_sql [-h] [-ot ONLY_TABLES [ONLY_TABLES ...]] [-op ONLY_OPERATION] -H MYSQL_HOST
                   -P MYSQL_PORT -u MYSQL_USER -p MYSQL_PASSWD -d MYSQL_DATABASE
                   [-c MYSQL_CHARSET] --binlog-file BINLOG_FILE [--binlog-pos BINLOG_POS]
                   --start-time ST --end-time ET [--max-workers MAX_WORKERS] [--print]

Binlog數(shù)據(jù)恢復(fù)，生成反向SQL語(yǔ)句。

options:
  -h, --help            show this help message and exit
  -ot ONLY_TABLES [ONLY_TABLES ...], --only-tables ONLY_TABLES [ONLY_TABLES ...]
                        設(shè)置要恢復(fù)的表，多張表用,逗號(hào)分隔
  -op ONLY_OPERATION, --only-operation ONLY_OPERATION
                        設(shè)置誤操作時(shí)的命令（insert/update/delete）
  -H MYSQL_HOST, --mysql-host MYSQL_HOST
                        MySQL主機(jī)名
  -P MYSQL_PORT, --mysql-port MYSQL_PORT
                        MySQL端口號(hào)
  -u MYSQL_USER, --mysql-user MYSQL_USER
                        MySQL用戶(hù)名
  -p MYSQL_PASSWD, --mysql-passwd MYSQL_PASSWD
                        MySQL密碼
  -d MYSQL_DATABASE, --mysql-database MYSQL_DATABASE
                        MySQL數(shù)據(jù)庫(kù)名
  -c MYSQL_CHARSET, --mysql-charset MYSQL_CHARSET
                        MySQL字符集，默認(rèn)utf8
  --binlog-file BINLOG_FILE
                        Binlog文件
  --binlog-pos BINLOG_POS
                        Binlog位置，默認(rèn)4
  --start-time ST       起始時(shí)間
  --end-time ET         結(jié)束時(shí)間
  --max-workers MAX_WORKERS
                        線(xiàn)程數(shù)，默認(rèn)10
  --print               將解析后的SQL輸出到終端
--replace             將update轉(zhuǎn)換為replace操作

Example usage:
shell> ./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" 

當(dāng)出現(xiàn)誤操作時(shí)，只需指定誤操作的時(shí)間段，其對(duì)應(yīng)的binlog文件（通常你可以通過(guò)show master status得到當(dāng)前的binlog文件名）以及剛才誤操作的表，和具體的DML命令，比如update或者delete。

工具運(yùn)行時(shí)，首先會(huì)進(jìn)行MySQL的環(huán)境檢測(cè)（if binlog_format != 'ROW' and binlog_row_image != 'FULL'），如果不同時(shí)滿(mǎn)足這兩個(gè)條件，程序直接退出。

工具運(yùn)行后，會(huì)在當(dāng)前目錄下生成一個(gè){db}_{table}_recover.sql文件，保存著原生SQL（原生SQL會(huì)加注釋?zhuān)┖头聪騍QL，如果想將結(jié)果輸出到前臺(tái)終端，可以指定--print選項(xiàng)。

如果你想把update操作轉(zhuǎn)換為replace，指定--replace選項(xiàng)即可，同時(shí)會(huì)在當(dāng)前目錄下生成一個(gè){db}_{table}_recover_replace.sql文件。

圖片

MySQL最小化用戶(hù)權(quán)限：

> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 
`yourname`@`%`;

> GRANT SELECT ON `test`.* TO `yourname`@`%`;

恢復(fù)

在{db}_{table}_recover.sql文件中找到你剛才誤操作的DML語(yǔ)句，然后在MySQL數(shù)據(jù)庫(kù)中執(zhí)行逆向工程后的SQL以恢復(fù)數(shù)據(jù)。

如果{db}_{table}_recover.sql文件的內(nèi)容過(guò)多，也可以通過(guò)awk命令進(jìn)行分割，以便更容易進(jìn)行排查。

shell> awk '/^-- SQL執(zhí)行時(shí)間/{filename = "output" ++count ".sql"; print > filename; next} {print > filename}' test_t1_recover.sql

不支持drop和truncate操作，因?yàn)檫@兩個(gè)操作屬于物理性刪除，需要通過(guò)歷史備份進(jìn)行恢復(fù)。

注：reverse_sql支持MySQL 5.7/8.0和MariaDB，適用于CentOS 7系統(tǒng)。

2023年7月12日更新 - 分之版本：可實(shí)現(xiàn)進(jìn)度條展示（處理binlog的event數(shù)量和耗時(shí)時(shí)間）。

工具研發(fā)者介紹

賀春旸，dbaplus社群金牌專(zhuān)家，凡普金科和愛(ài)錢(qián)進(jìn)DBA團(tuán)隊(duì)負(fù)責(zé)人，《MySQL管理之道：性能調(diào)優(yōu)、高可用與監(jiān)控》第一&二版、《MySQL運(yùn)維進(jìn)階指南》作者，曾任職于中國(guó)移動(dòng)飛信、安卓機(jī)鋒網(wǎng)。五次榮獲dbaplus年度MVP，致力于MariaDB、MongoDB等開(kāi)源技術(shù)的研究，主要負(fù)責(zé)數(shù)據(jù)庫(kù)性能調(diào)優(yōu)、監(jiān)控和架構(gòu)設(shè)計(jì)。

工具下載：https://github.com/hcymysql/reverse_sql/