binlog2sql使用總結

binlog2sql是大眾點評開源的一款用於解析binlog的工具，在測試環境試用了下，還不錯。

其具有以下功能

1. 提取SQL

2. 生成回滾SQL

關於該工具的使用方法可參考github操作文檔：https://github.com/danfengcao/binlog2sql

個人感覺該文檔已相當簡單明瞭。

使用該工具的前提

1. binlog_format為ROW，且binlog_row_image為full或noblog，預設為full。

2. 必須開啟MySQL Server，理由有如下兩點：

   1> 它是基於BINLOG_DUMP協議來獲取binlog內容

   2> 需要讀取server端information_schema.COLUMNS表，獲取表結構的元信息，拼接成可視化的sql語句

該工具所需許可權如下：

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 

因為是偽裝成slave來獲取主的二進位事件，故無需對binlog有可讀許可權。

提取SQL示例

#  python binlog2sql.py -h192.168.244.10 -P3306 -uadmin -p123456 -dtest -ttest --start-file='mysql-bin.000028'

INSERT INTO `test`.`test`(`id`, `name`) VALUES (10, 'a'); #start 4 end 284 time 2017-02-17 15:36:27
INSERT INTO `test`.`test`(`id`, `name`) VALUES (11, 'b'); #start 4 end 376 time 2017-02-17 15:36:31
UPDATE `test`.`test` SET `id`=11, `name`='c' WHERE `id`=11 AND `name`='b' LIMIT 1; #start 4 end 476 time 2017-02-17 15:36:40
DELETE FROM `test`.`test` WHERE `id`=11 AND `name`='c' LIMIT 1; #start 4 end 568 time 2017-02-17 15:36:48
INSERT INTO `test`.`test`(`id`, `name`) VALUES (12, 'd'); #start 599 end 763 time 2017-02-17 15:37:09
INSERT INTO `test`.`test`(`id`, `name`) VALUES (13, 'c'); #start 794 end 958 time 2017-02-17 15:37:15
UPDATE `test`.`test` SET `id`=13, `name`='d' WHERE `id`=13 AND `name`='c' LIMIT 1; #start 989 end 1161 time 2017-02-17 15:37:23
DELETE FROM `test`.`test` WHERE `id`=13 AND `name`='d' LIMIT 1; #start 1192 end 1356 time 2017-02-17 15:37:30

生成回滾SQL示例

# python binlog2sql.py --flashback -h192.168.244.10 -P3306 -uadmin -p123456 -dtest -ttest --start-file='mysql-bin.000028'

INSERT INTO `test`.`test`(`id`, `name`) VALUES (13, 'd'); #start 1192 end 1356 time 2017-02-17 15:37:30
UPDATE `test`.`test` SET `id`=13, `name`='c' WHERE `id`=13 AND `name`='d' LIMIT 1; #start 989 end 1161 time 2017-02-17 15:37:23
DELETE FROM `test`.`test` WHERE `id`=13 AND `name`='c' LIMIT 1; #start 794 end 958 time 2017-02-17 15:37:15
DELETE FROM `test`.`test` WHERE `id`=12 AND `name`='d' LIMIT 1; #start 599 end 763 time 2017-02-17 15:37:09
INSERT INTO `test`.`test`(`id`, `name`) VALUES (11, 'c'); #start 4 end 568 time 2017-02-17 15:36:48
UPDATE `test`.`test` SET `id`=11, `name`='b' WHERE `id`=11 AND `name`='c' LIMIT 1; #start 4 end 476 time 2017-02-17 15:36:40
DELETE FROM `test`.`test` WHERE `id`=11 AND `name`='b' LIMIT 1; #start 4 end 376 time 2017-02-17 15:36:31
DELETE FROM `test`.`test` WHERE `id`=10 AND `name`='a' LIMIT 1; #start 4 end 284 time 2017-02-17 15:36:27

總結

1. 看了下源代碼，它本身的核心代碼比較少，主要是在pymysqlreplication的基礎上進行了二次開發。

    pymysqlreplication實現了MySQL複製協議，可捕捉不同類型的EVENT事件。

    具體可參考：https://github.com/noplay/python-mysql-replication

2. 個人感覺，直接解析文本格式的binlog，也未嘗不是一個好辦法。

    理由如下：

    1> binlog2sql強烈依賴於MySQL複製協議，如果複製協議發生改變，則該工具將不可用。

        雖然，複製協議發生改變的可能性很小（一般都會保持向前相容），但相對而言，自帶的mysqlbinlog肯定更懂binlog，基於mysqlbinlog解析後的結果進行處理，可完

        全屏蔽複製協議等底層細節。

    2> 用python來解析文本格式的binlog，本身也不是件難事。

         譬如，update語句在binlog中的對應的文本

         在得到表結構的情況下，基本上可離線解析。

### UPDATE `test`.`test`
### WHERE
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

內網環境如何使用該工具呢？

該工具的使用依賴以下三個包：

PyMySQL==0.7.8
wheel==0.24.0
mysql-replication==0.9

其中，每個包又會依賴其它包，所以安裝這些包是一個比較麻煩的事情。

如果是在外網的環境下，可直接通過pip install安裝，它會自動下載並安裝依賴包的。

在內網環境下，可手動安裝這些包，目前，這些包已下載並打包，且已上傳到百度雲盤中，大家可自行下載。

http://pan.baidu.com/s/1qYQ2PPy

安裝教程：

# tar xvf binlog2sql.tar.gz

# cd binlog2sql/binlog2sql_dependencies/

# tar xvf setuptools-0.6c11.tar.gz

# cd setuptools-0.6c11

# python setup.py install

# cd ..

# tar xvf pip-9.0.1.tar.gz

# cd pip-9.0.1

# python setup.py install

# cd ..

# pip install *.whl mysql-replication-0.9.tar.gz

DEPRECATION: Python 2.6 is no longer supported by the Python core team, please upgrade your Python. A future version of pip will drop
 support for Python 2.6Processing ./mysql-replication-0.9.tar.gz
Processing ./argparse-1.4.0-py2.py3-none-any.whl
Processing ./linecache2-1.0.0-py2.py3-none-any.whl
Requirement already satisfied: PyMySQL==0.7.8 from file:///root/binlog2sql/binlog2sql_dependencies/PyMySQL-0.7.8-py2-none-any.whl in 
/usr/lib/python2.6/site-packagesProcessing ./six-1.10.0-py2.py3-none-any.whl
Processing ./traceback2-1.4.0-py2.py3-none-any.whl
Processing ./unittest2-1.1.0-py2.py3-none-any.whl
Processing ./wheel-0.24.0-py2.py3-none-any.whl
Installing collected packages: argparse, linecache2, six, traceback2, unittest2, wheel, mysql-replication
  Running setup.py install for mysql-replication ... done
Successfully installed argparse-1.4.0 linecache2-1.0.0 mysql-replication-0.9 six-1.10.0 traceback2-1.4.0 unittest2-1.1.0 wheel-0.24.0

至此，所有依賴包安裝完畢。

測試一下

# cd ..

# cd binlog2sql

# python binlog2sql.py -h 192.168.244.20 -u root -p 123456  -d test -t test --start-file mysql-bin.000022

INSERT INTO `test`.`test`(`id`, `name`) VALUES (4, 'd'); #start 4 end 284 time 2017-02-21 10:44:02
INSERT INTO `test`.`test`(`id`, `name`) VALUES (5, 'f'); #start 315 end 479 time 2017-02-21 10:44:05
DELETE FROM `test`.`test` WHERE `id`=4 AND `name`='d' LIMIT 1; #start 510 end 674 time 2017-02-21 10:44:09
UPDATE `test`.`test` SET `id`=5, `name`='t' WHERE `id`=5 AND `name`='f' LIMIT 1; #start 705 end 877 time 2017-02-21 10:44:22

Tips，如何有效率的下載依賴包？

pip-9.0.1新增了一個選項download，可以只下載包（自動下載相關依賴包）而不安裝它。