今天分享一下binlog2sql，它是一款比較常用的數(shù)據(jù)恢復(fù)工具，可以通過它從MySQL binlog解析出你要的SQL，并根據(jù)不同選項(xiàng)，可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。主要用途如下：

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

1.系統(tǒng)環(huán)境準(zhǔn)備

（1）安裝Git

該項(xiàng)目分享與git上，可以直接從git上獲取，因此可以先安裝git。

yum install  -y  pip

（2）安裝Python

因binlog2sql依賴于python2.7或python3.4+版本，如果本機(jī)python版本較低，則無法使用。安裝或升級(jí)python的方法可以參考 《python安裝及升級(jí)》

（3）安裝pip

如果系統(tǒng)中沒有安裝pip，則需要先安裝pip,因?yàn)楹罄m(xù)需要用pip安裝python所需的包。

（4）binlog2sql下載及依賴包安裝

binlog2sql可以部署在其他機(jī)器上，而不是必須部署在mysql服務(wù)端上。

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

2.MySQL環(huán)境準(zhǔn)備

（1）參數(shù)設(shè)置

MySQL server必須設(shè)置以下參數(shù)。

[mysqld]
server_id = 128
log_bin = /data/mysql/mysql3306/logs/mysql-bin
max_binlog_size = 512M
binlog_format = row
binlog_row_image = full   # 默認(rèn)值，可以不顯式設(shè)置

（2）創(chuàng)建恢復(fù)賬號(hào)

因binlog2sql是通過模擬從庫的方式獲取binlog，所以，數(shù)據(jù)庫賬號(hào)權(quán)限至少需設(shè)置為從庫所需的權(quán)限。

--  創(chuàng)建用戶
mysql> create  user  data_rec@'192.168.56.%'  identified by 'xxxxxxxx';
Query OK, 0 rows affected (0.01 sec)
--  授權(quán)
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO data_rec@'192.168.56.%';
Query OK, 0 rows affected (0.00 sec)

權(quán)限說明：

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

（3）創(chuàng)建測(cè)試表及數(shù)據(jù)

mysql> use testdb;
Database changed
mysql> create table t_test1 (id int primary key  auto_increment ,c_name  varchar(20), c_num  int );
Query OK, 0 rows affected (0.02 sec)


mysql> insert into  t_test1(c_name,c_num) values('aaaa',10),('abcc',15),('bacess',9),('andd',10);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> insert into  t_test1(c_name,c_num) values('bbbaa',1),('dc',5),('vgcess',29),('hdgd',0);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

（4）模擬誤刪除數(shù)據(jù)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:08 |
+---------------------+
1 row in set (0.00 sec)


mysql> delete from  t_test1;
Query OK, 8 rows affected (0.00 sec)


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-18 16:29:26 |
+---------------------+
1 row in set (0.00 sec)

3.binlog2sql恢復(fù)數(shù)據(jù)

（1）生成恢復(fù)SQL

因知道大概誤刪除的時(shí)間，因此通過解析對(duì)應(yīng)時(shí)間的binlog恢復(fù)出指定庫表的數(shù)據(jù)，生成的結(jié)果是用于恢復(fù)的sql.

python binlog2sql.py  --flashback  -h 192.168.56.128 -udata_rec -p'xxxxxxxx' -d testdb  -t t_test1  --start-file='mysql-bin.000003' --start-datetime='2020-09-18 16:29:08' --stop-datetime='2020-09-18 16:30:00'  >/tmp/rec.sql

結(jié)果內(nèi)容如下：

（2）恢復(fù)數(shù)據(jù)

mysql> select  * from t_test1;
Empty set (0.00 sec)
mysql> source /tmp/rec.sql;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select  * from t_test1;
+----+--------+-------+
| id | c_name | c_num |
+----+--------+-------+
|  1 | aaaa   |    10 |
|  2 | abcc   |    15 |
|  3 | bacess |     9 |
|  4 | andd   |    10 |
|  5 | bbbaa  |     1 |
|  6 | dc     |     5 |
|  7 | vgcess |    29 |
|  8 | hdgd   |     0 |
+----+--------+-------+
8 rows in set (0.00 sec)

 數(shù)據(jù)已恢復(fù)。

 4.使用說明

參考原文鏈接：https://github.com/danfengcao/binlog2sql。

（1）解析模式

• stop-never 持續(xù)解析binlog?？蛇x。默認(rèn)False，同步至執(zhí)行命令時(shí)最新的binlog位置。一般情況下解析單個(gè)binlog即可，但是如果需要回退的表之后有操作，則可能需要持續(xù)解析。
• K, --no-primary-key 對(duì)INSERT語句去除主鍵?？蛇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。可選。默認(rèn)1.0。

（2）解析范圍控制

• start-file 起始解析文件，只需文件名，無需全路徑 ，必選參數(shù)。即指定對(duì)應(yīng)的binlog起始文件名。
• start-position/--start-pos 起始解析位置。可選。默認(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'?？蛇x。默認(rèn)不過濾。
• stop-datetime 終止解析時(shí)間，格式'%Y-%m-%d %H:%M:%S'?？蛇x。默認(rèn)不過濾。

（3）對(duì)象過濾

• d, --databases 只解析目標(biāo)db的sql，多個(gè)庫用空格隔開，如-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。可選。默認(rèn)為增刪改都解析。用了此參數(shù)但沒填任何類型，則三者都不解析。