MySQL資料庫binlog解析神器-binlog2sql應用

資料庫工作筆記發表於2023-10-17
MySql資料庫

來源：運維記事

介紹

binlog2sql是一個開源的python開發的mysql資料庫binlog解析工具，能夠將binlog解析為原始的sql語句。也支援將binlog解析為回滾的sql語句，以便做資料恢復。GitHub地址：

用途

  • 資料快速回滾(閃回)

  • 主從切換後新master丟資料的修復

  • 從binlog生成標準SQL，帶來的衍生功能

專案狀態

正常維護。應用於部分公司線上環境。

  • 已測試環境

    • Python 2.7, 3.4+

    • MySQL 5.6, 5.7

安裝



 shell> git clone .git && cd binlog2sql shell> pip install -r requirements.txt

使用

引數設定

MySQL server必須設定以下引數







 [mysqld] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_size = 1G binlog_format = row binlog_row_image = full

必要授權

user需要的最小許可權集合




 select, super/replication client, replication slave 建議授權 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

  • 許可權說明

    • select：需要讀取server端information_schema.COLUMNS表，獲取表結構的元資訊，拼接成視覺化的sql語句

    • super/replication client：兩個許可權都可以，需要執行'SHOW MASTER STATUS', 獲取server端的binlog列表

    • replication slave：透過BINLOG_DUMP協議獲取binlog內容的許可權

選項說明

mysql連線配置

-h host; -P port; -u user; -p password

解析模式

--stop-never 持續解析binlog。可選。預設False，同步至執行命令時最新的binlog位置。

-K, --no-primary-key 對INSERT語句去除主鍵。可選。預設False

-B, --flashback 生成回滾SQL，可解析大檔案，不受記憶體限制。可選。預設False。與stop-never或no-primary-key不能同時新增。

--back-interval -B模式下，每列印一千行回滾SQL，加一句SLEEP多少秒，如不想加SLEEP，請設為0。可選。預設1.0。

解析範圍控制

--start-file 起始解析檔案，只需檔名，無需全路徑 。必須。

--start-position/--start-pos 起始解析位置。可選。預設為start-file的起始位置。

--stop-file/--end-file 終止解析檔案。可選。預設為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'。可選。預設不過濾。

物件過濾

-d, --databases 只解析目標db的sql，多個庫用空格隔開，如-d db1 db2。可選。預設為空。

-t, --tables 只解析目標table的sql，多張表用空格隔開，如-t tbl1 tbl2。可選。預設為空。

--only-dml 只解析dml，忽略ddl。可選。預設False。

--sql-type 只解析指定型別，支援INSERT, UPDATE, DELETE。多個型別用空格隔開，如--sql-type INSERT DELETE。可選。預設為增刪改都解析。用了此引數但沒填任何型別，則三者都不解析。

應用案例

建立使用者並授權









 mysql> CREATE USER 'binlog2sql'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec)  mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog2sql'@'%'; Query OK, 0 rows affected (0.00 sec)  mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

執行binlog解析





 [root@ansible-server /]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -ubinlog2sql -p123456 --start-file='mysql-bin.000231'; CREATE USER 'binlog2sql'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog2sql'@'%'; flush privileges;

誤刪整張表資料，進行緊急回滾










































 mysql> flush logs; Query OK, 0 rows affected (0.00 sec)  mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000233 |     154 |             |                 |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)  mysql> use test; Database changed  #test庫user表原有資料  mysql> select * from user; +----+--------+------+------+ | id | name   | addr | tel | +----+--------+------+------+ | 1 | 張三   | NULL | NULL | | 2 | 李四   | NULL | NULL | | 3 | 王五   | NULL | NULL | +----+--------+------+------+ 3 rows in set (0.00 sec)  mysql> select sysdate(); +---------------------+ | sysdate()           | +---------------------+ | 2023-10-15 20:48:56 | +---------------------+ 1 row in set (0.00 sec)  #2023-10-15 20:48:56誤刪test庫user表，所有資料丟失 mysql> delete from user; Query OK, 3 rows affected (0.00 sec)  mysql> select * from user; Empty set (0.00 sec)  mysql>

恢復資料步驟

登入mysql，檢視目前的binlog檔案。








 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000233 |     449 |             |                 |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

最新的binlog檔案是mysql-bin.000233，我們再定位誤操作SQL的binlog位置。誤操作人只能知道大致的誤操作時間，我們根據大致時間過濾資料。解析2023-10-15 20:00:00-2023-10-15 21:00:00期間test庫user表sql型別為delete的操作記錄。





 [root@ansible-server ~]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -ubinlog2sql -p123456 --start-file='mysql-bin.000233' -d test -t user --start-datetime='2023-10-15 20:00:00' --stop-datetime='2023-10-15 21:00:00' --sql-type=delete DELETE FROM `test`.`user` WHERE `addr` IS NULL AND `tel` IS NULL AND `id`=1 AND `name`='張三' LIMIT 1; #start 4 end 418 time 2023-10-15 20:47:43 DELETE FROM `test`.`user` WHERE `addr` IS NULL AND `tel` IS NULL AND `id`=2 AND `name`='李四' LIMIT 1; #start 4 end 418 time 2023-10-15 20:47:43 DELETE FROM `test`.`user` WHERE `addr` IS NULL AND `tel` IS NULL AND `id`=3 AND `name`='王五' LIMIT 1; #start 4 end 418 time 2023-10-15 20:47:43

我們得到了誤操作sql的準確位置在4-418之間，再根據位置進一步過濾，使用flashback模式生成回滾sql，檢查回滾sql是否正確。





 [root@ansible-server ~]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -ubinlog2sql -p123456 --start-file='mysql-bin.000233' -d test -t user --start-position=4 --stop-position=418 --sql-type=delete -B > rollback.sql;cat rollback.sql  INSERT INTO `test`.`user`(`addr`, `tel`, `id`, `name`) VALUES (NULL, NULL, 3, '王五'); #start 4 end 418 time 2023-10-15 20:47:43 INSERT INTO `test`.`user`(`addr`, `tel`, `id`, `name`) VALUES (NULL, NULL, 2, '李四'); #start 4 end 418 time 2023-10-15 20:47:43 INSERT INTO `test`.`user`(`addr`, `tel`, `id`, `name`) VALUES (NULL, NULL, 1, '張三'); #start 4 end 418 time 2023-10-15 20:47:43

與業務方確認回滾sql正確，執行回滾語句。同時登入mysql確認資料回滾成功。














 [root@ansible-server ~]# mysql -uroot -p123456 < rollback.sql   mysql> select * from test.user; +----+--------+------+------+ | id | name   | addr | tel | +----+--------+------+------+ | 1 | 張三   | NULL | NULL | | 2 | 李四   | NULL | NULL | | 3 | 王五   | NULL | NULL | +----+--------+------+------+ 3 rows in set (0.00 sec)  mysql>

限制（對比mysqlbinlog）

  • mysql server必須開啟，離線模式下不能解析

  • 引數 binlog_row_image 必須為FULL，暫不支援MINIMAL

  • 解析速度不如mysqlbinlog

優點（對比mysqlbinlog）

  • 純Python開發，安裝與使用都很簡單

  • 自帶flashback、no-primary-key解析模式，無需再裝補丁

  • flashback模式下，更適合閃回實戰

  • 解析為標準SQL，方便理解、篩選

  • 程式碼容易改造，可以支援更多個性化解析

總結

binlog2sql在做回滾操作的時候大致分為4步：

1、檢視當前的binlog檔案

2、透過大概誤操時間範圍解析出誤操作執行的SQL；

3、根據第2步得到的binlog位置資訊，使用-B引數生成回滾SQL；

4、經和誤操作方確認回滾SQL無誤，執行回滾操作。

同樣，我們還可以根據不同需求、配合不同引數解析出我們想要的正、反向SQL。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2989266/，如需轉載，請註明出處，否則將追究法律責任。

相關文章