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

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

#解析出標(biāo)準(zhǔ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; 

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

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

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

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)被刪除

#查看當(dāng)前binlog的位點(diǎn)
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，我們?cè)俣ㄎ徽`操作SQL的binlog位置。誤操作人只能知道大致的誤操作時(shí)間，我們根據(jù)大致時(shí)間過(guò)濾數(shù)據(jù)
#注意這里可以根據(jù)位點(diǎn)和時(shí)間判斷，因?yàn)閎inlog文件都會(huì)有寫入時(shí)間
#如果知道位點(diǎn)直接位點(diǎn)方式過(guò)濾，如果不知道可以用大概時(shí)間解析出標(biāo)準(zhǔn)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
#可以看到上面解析出來(lái)了我們剛才刪除的三條sql語(yǔ)句（注意這里命令行沒(méi)有加-B參數(shù)即解析出標(biāo)準(zhǔn)sql）

#接下來(lái)我們直接解析回滾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ù)庫(kù)中

#確認(rèn)一下生成的回滾sql使我們對(duì)應(yīng)的表的反向sql，是否正確，確認(rèn)無(wú)誤導(dǎo)入數(shù)據(jù)庫(kù)
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)

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