（一）binlog2sql介紹
binlog2sql是國內MySQL大佬danfengcao開發，許多MySQL愛好者參與改進的一款MySQL binlog解析軟體。根據不同選項，可以得到原始SQL、回滾SQL、去除主鍵的SQL等。
github地址為：https://github.com/danfengcao/binlog2sql
該工具主要用於：
- 資料快速回滾（閃回）；
- 從binlog生成標準SQL；
適用MySQL版本：MySQL5.6 、MySQL5.7
（二）安裝binlog2sql
# 安裝git shell> yum install -y git # 安裝pip工具 shell> yum install -y epel-release shell> yum install -y python-pip # 安裝binlog2sql shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql shell> pip install -r requirements.txt
（三）使用binlog2sql
要使用binlog2sql，MySQL伺服器需要設定以下引數：
[mysqld] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_size = 100M binlog_format = row binlog_row_image = full
binlog2sql在使用時需要連線到資料庫上，連線使用者的許可權為：
select,spuer/replication client,replication slave -- 建議授權： grant select,replication slave,replication client on *.* to user;
binlog2sql的語法為：
[root@masterdb binlog2sql]# pwd /root/binlog2sql/binlog2sql [root@masterdb binlog2sql]# python binlog2sql.py --help usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS] [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]] [-t [TABLES [TABLES ...]]] [--only-dml] [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B] [--back-interval BACK_INTERVAL]
語法解析：
MySQL連結配置引數
- -h host; –p password; –u user ; -P port
解析模式引數：
- --stop-never : 持續解析binlog，同步至執行命令時最新的binlog位置，預設false。
- -K , --no-primary-key ： 對insert語句去除主鍵，預設false。
- -B , --flashback : 生成回滾SQL，可解析大檔案，不受記憶體限制，預設false。與stop-never或no-primary-key不能同時新增。
- --back-interval :在-B模式下，每列印1000條回滾SQL，SLEEP多少秒，預設為1。
範圍控制引數：
- --start-file ：起始解析檔案，只需檔名，無需全路徑。必需引數。
- --stop-file / --end-file : 終止解析檔案，預設為start-file同一個檔案，若解析模式為stop-never，則該引數失效。
- --start-position / --start-pos : 起始解析位置，預設start-file檔案起始位置。
- --stop-position / --stop-pos : 終止解析位置，預設stop-file終點位置。
- --start-datetime ：起始解析時間，格式為’%Y-%m-%d %H:%M:%S’。
- --stop-datetime ：終止解析時間，格式為’%Y-%m-%d %H:%M:%S’
物件過濾引數：
- -d , --databases : 只解析目標DB的sql，多個資料庫用空格隔開，如：-d db1 db2。
- -t , --tables : 只解析目標表的sql，多張表用空格隔開，如：-t table1 table2。
- --only-dml ：只解析DML，忽略DDL。
- --sql-type : 只解析指定型別，支援INSERT,UPDATE,DELETE。多個型別用空格隔開，如--sql-type INSERT UPDATE，預設全解析。
（四）binlog2sql測試
測試目的：本次實驗模擬誤刪除資料，通過使用binlog2sql將資料找回來。
STEP1：構造測試資料
--構造測試表 create table test01 ( id int primary key, name varchar(30) not null, birthday date not null ); -插入3條資料 insert into test01 values(1,'小明','1993-01-02'); insert into test01 values(2,'小華','1994-08-15'); insert into test01 values(3,'小麗','1995-07-12'); mysql> select * from test01; +----+--------+------------+ | id | name | birthday | +----+--------+------------+ | 1 | 小明 | 1993-01-02 | | 2 | 小華 | 1994-08-15 | | 3 | 小麗 | 1995-07-12 | +----+--------+------------+ 3 rows in set (0.00 sec)
STEP2：模擬誤刪除資料
mysql> delete from test01; Query OK, 3 rows affected (0.00 sec)
STEP3：確認最後的日誌
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1600 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
STEP4：解析出標準SQL，用於定位回滾的開始和結束位置
[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' USE lijiamandb; create table test01 ( id int primary key, name varchar(30) not null, birthday date not null ); DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1993-01-02' AND `id`=1 AND `name`='小明' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1994-08-15' AND `id`=2 AND `name`='小華' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1995-07-12' AND `id`=3 AND `name`='小麗' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26
值得注意的是，雖然我指定了只解析DELETE語句，但還是把DDL給解析出來了。
STEP5：解析出回滾SQL
[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 --flashback -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小麗'); #start 1287 end 1569 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小華'); #start 1287 end 1569 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26
STEP6：還原到資料庫
[root@masterdb binlog2sql]# mysql -uroot -p123456 lijiamandb mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小麗'); #start 1287 end 1569 time 2020-04-24 13:40:26 69 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小華'); #start 1287 end 1569 time 2020-04-24 13:40:26 Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26 Query OK, 1 row affected (0.00 sec) mysql> select * from test01; +----+--------+------------+ | id | name | birthday | +----+--------+------------+ | 1 | 小明 | 1993-01-02 | | 2 | 小華 | 1994-08-15 | | 3 | 小麗 | 1995-07-12 | +----+--------+------------+ 3 rows in set (0.00 sec)
（五）總結
使用binlog2sql最大的好處就是解析出來的SQL語句非常直觀，並且在註釋中還包含了時間，這對於我們去查詢故障發生點非常實用。想一想之前用過的mysqlbinlog工具，解析出來的結果中含有大量無關的資訊，為我們排查問題增加了難度，而binlog2sql解析出來的SQL非常乾淨，便於我們排查問題，恢復資料。
相關文件集合： 1.MySQL日誌--二進位制日誌(binlog) |