快捷導(dǎo)航

MySQL中binlog2sql實現(xiàn)數(shù)據(jù)誤刪恢復(fù)工具

更新時間：2025年09月24日 10:26:13 作者：漁不是魚

本文介紹了binlog2sql工具的使用,它能從MySQL binlog中解析出SQL語句,用于數(shù)據(jù)回滾、主從切換后的數(shù)據(jù)修復(fù)等場景,通過設(shè)置不同參數(shù),可以獲取原始SQL、回滾SQL等,具有一定的參考價值,感興趣的可以了解一下

1.簡介

從MySQL binlog解析出你要的SQL。根據(jù)不同選項，你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等
官方文檔: https://github.com/danfengcao/binlog2sql

2.用途

數(shù)據(jù)快速回滾(閃回)
主從切換后新master丟數(shù)據(jù)的修復(fù)
從binlog生成標準SQL，帶來的衍生功能

3.適用環(huán)境

Python 2.7, 3.4+
MySQL 5.6, 5.7

4.安裝

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

#安裝pip
curl https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py

5.使用

使用前提:

#MySQL server必須設(shè)置以下參數(shù)(值不需要和下面相同，只要開啟即可):
[mysqld] 
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row 

#回滾user需要的最小權(quán)限集合：
select, super/replication client, replication slave 
建議授權(quán) GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

#權(quán)限說明
select：需要讀取server端information_schema.COLUMNS表，獲取表結(jié)構(gòu)的元信息，拼接成可視化的sql語句
super/replication client：兩個權(quán)限都可以，需要執(zhí)行'SHOW MASTER STATUS', 獲取server端的binlog列表
replication slave：通過BINLOG_DUMP協(xié)議獲取binlog內(nèi)容的權(quán)限

基本用法:

#解析出標準sql
shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
輸出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); 
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1;
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147
#解析出回滾sql
shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147
輸出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); 
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1;

參數(shù)選項:

#mysql連接配置
-h host; -P port; -u user; -p password
#解析模式
--stop-never 持續(xù)解析binlog?？蛇x。默認False，同步至執(zhí)行命令時最新的binlog位置。
-K, --no-primary-key 對INSERT語句去除主鍵?？蛇x。默認False
-B, --flashback 生成回滾SQL，可解析大文件，不受內(nèi)存限制?？蛇x。默認False。與stop-never或no-primary-key不能同時添加。
--back-interval -B模式下，每打印一千行回滾SQL，加一句SLEEP多少秒，如不想加SLEEP，請設(shè)為0。可選。默認1.0。

#解析范圍控制
--start-file 起始解析文件，只需文件名，無需全路徑 。必須。
--start-position/--start-pos 起始解析位置?？蛇x。默認為start-file的起始位置。
--stop-file/--end-file 終止解析文件?？蛇x。默認為start-file同一個文件。若解析模式為stop-never，此選項失效。
--stop-position/--end-pos 終止解析位置。可選。默認為stop-file的最末位置；若解析模式為stop-never，此選項失效。
--start-datetime 起始解析時間，格式'%Y-%m-%d %H:%M:%S'。可選。默認不過濾。
--stop-datetime 終止解析時間，格式'%Y-%m-%d %H:%M:%S'?？蛇x。默認不過濾。

#對象過濾
-d, --databases 只解析目標db的sql，多個庫用空格隔開，如-d db1 db2?？蛇x。默認為空。
-t, --tables 只解析目標table的sql，多張表用空格隔開，如-t tbl1 tbl2?？蛇x。默認為空。
--only-dml 只解析dml，忽略ddl?？蛇x。默認False。
--sql-type 只解析指定類型，支持INSERT, UPDATE, DELETE。多個類型用空格隔開，如--sql-type INSERT DELETE?？蛇x。默認為增刪改都解析。用了此參數(shù)但沒填任何類型，則三者都不解析。

6.實際案例操作

誤操作

mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| ID   | STATUS | LOCATION_ID | EXTERNAL_IP   | DBNAME               | CREATED_DATE        | MODIFIED_DATE       |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
|    0 |      0 | ind01       | 1292 		  | gachar               | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 |      1 | ind01       | 1293          | NULL                 | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 |      1 | us01        | 1294 		  | change				 | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)

#誤刪除全表
mysql> delete from web_db_status;
Query OK, 3 rows affected (0.20 sec)

mysql> select * from web_db_status;
Empty set (0.00 sec)
可以看到表中數(shù)據(jù)已經(jīng)被刪除

開始恢復(fù)

#查看當前binlog的位點
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql_bin.000020
         Position: 94967010
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

#最新的binlog文件是mysql_bin.000020，我們再定位誤操作SQL的binlog位置。誤操作人只能知道大致的誤操作時間，我們根據(jù)大致時間過濾數(shù)據(jù)
#注意這里可以根據(jù)位點和時間判斷，因為binlog文件都會有寫入時間
#如果知道位點直接位點方式過濾，如果不知道可以用大概時間解析出標準sql
shell>python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00'
輸出:
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=0 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1292' AND `DBNAME`='gachar' AND `CREATED_DATE`='2020-11-24 17:15:16' AND `LOCATION_ID`='ind01' AND `ID`=0 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1293' AND `DBNAME` IS NULL AND `CREATED_DATE`='2020-11-25 15:34:09' AND `LOCATION_ID`='ind01' AND `ID`=1808 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1294' AND `DBNAME`='change' AND `CREATED_DATE`='1970-01-02 00:00:00' AND `LOCATION_ID`='us01' AND `ID`=1809 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到上面解析出來了我們剛才刪除的三條sql語句（注意這里命令行沒有加-B參數(shù)即解析出標準sql）

#接下來我們直接解析回滾sql（命令中加入-B參數(shù) 即--flashback）
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -B -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00' > rollback.sql
shell> cat rollback.sql
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1294', 'change', '1970-01-02 00:00:00', 'us01', 1809); #start 94966615 end 94966979 time 2021-01-05 16:05:04
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1293', NULL, '2020-11-25 15:34:09', 'ind01', 1808); #start 94966615 end 94966979 time 2021-01-05 16:05:04
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (0, '1997-01-01 00:00:00', '1292', 'gachar', '2020-11-24 17:15:16', 'ind01', 0); #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到已經(jīng)生成了反向插入sql，直接將這些sql執(zhí)行到數(shù)據(jù)庫中

#確認一下生成的回滾sql使我們對應(yīng)的表的反向sql，是否正確，確認無誤導(dǎo)入數(shù)據(jù)庫
mysql> source /root/binlog2sql/rollback.sql;
Query OK, 1 row affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| ID   | STATUS | LOCATION_ID | EXTERNAL_IP   | DBNAME               | CREATED_DATE        | MODIFIED_DATE       |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
|    0 |      0 | ind01       | 1292 		  | gachar               | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 |      1 | ind01       | 1293          | NULL                 | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 |      1 | us01        | 1294 		  | change				 | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)

#對比前后的數(shù)據(jù)
#數(shù)據(jù)已經(jīng)成功恢復(fù)

7.優(yōu)缺點對比(mysqlbinlog)

限制（對比mysqlbinlog）

mysql server必須開啟，離線模式下不能解析
參數(shù) binlog_row_image 必須為FULL，暫不支持MINIMAL
解析速度不如mysqlbinlog

優(yōu)點（對比mysqlbinlog）

純Python開發(fā)，安裝與使用都很簡單
自帶flashback、no-primary-key解析模式，無需再裝補丁
flashback模式下，更適合閃回實戰(zhàn) 解析為標準SQL，方便理解、篩選代碼容易改造，可以支持更多個性化解析

到此這篇關(guān)于MySQL中binlog2sql實現(xiàn)數(shù)據(jù)誤刪恢復(fù)工具的文章就介紹到這了,更多相關(guān)MySQL binlog2sql數(shù)據(jù)恢復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: