MYSQL Binglog分析利器:binlog2sql使用詳解

大部分使用mysql的業務也都會開啟binlog，用以記錄資料庫的更新操作。當然binlog的格式大家也都比較清楚，分別是statement、mixed、row模式。針對row格式的binlog，它以二進位制形式記錄相應的SQL，透過編輯器開啟無法知道具體的SQL是什麼。當然，我們也可以透過mysqlbinlog分析，但分析結果不利於我們做資料的回滾和redo。前段時間同事分享了一個小工具binlog2sql，覺得蠻有意思，湊著今天有時間裝了學習下。
一、binlog2sql簡介
binlog2sql是一開源工具，其可以從MySQL binlog解析出你要的SQL。根據不同選項，你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。
主要用途如下：
（1）資料快速回滾(閃回)
（2）主從切換後資料不一致的修復
（3）從binlog生成標準SQL，帶來的衍生功能
二、binlog2sql安裝
1、binlog2sql下載

2、binlog2sql依賴包安裝
python2.6+
PyMySQL==0.7.8+
wheel==0.24.0+
mysql-replication==0.9+
（1）PyMySQL-0.7.10安裝

[root@node1 binlogsql]# tar -xzvf PyMySQL-0.7.10.tar.gz
[root@node1 binlogsql]# cd PyMySQL-0.7.10
[root@node1 PyMySQL-0.7.10]# python setup.py install
（2）wheel-0.30.0a0安裝

[root@node1 binlogsql]# tar -xzvf wheel-0.30.0a0.tar.gz
[root@node1 binlogsql]# cd wheel-0.30.0a0
[root@node1 wheel-0.30.0a0]# python setup.py install
（3）python-mysql-replication安裝

[root@node1 binlogsql]# unzip python-mysql-replication-master.zip
[root@node1 binlogsql]# cd python-mysql-replication-master
[root@node1 python-mysql-replication-master]# python setup.py  install
（4）可以透過pip安裝相應的依賴包

[root@node1 tools]# tar -xzvf pip-9.0.1.tar.gz
[root@node1 tools]# cd pip-9.0.1
[root@node1 pip-9.0.1]# python setup.py install
[root@node1 binlog2sql-master]# pip install  -r requirements.txt
3、binlog2sql安裝
直接下載解壓縮即可，執行相應的py指令碼
[root@node1 tools]# unzip binlog2sql-master.zip
[root@node1 tools]# cd binlog2sql-master
設定別名，方便命令呼叫：
alias binlog2sql='python /tools/binlogsql/binlog2sql-master/binlog2sql/binlog2sql.py'
三、binlog2sql使用
1、binlog2sql幫助手冊
[root@node1 binlog2sql]# binlog2sql  --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
                     [--start-file STARTFILE] [--start-position STARTPOS]
                     [--stop-file ENDFILE] [--stop-position ENDPOS]
                     [--start-datetime STARTTIME] [--stop-datetime STOPTIME]
                     [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
                     [-t [TABLES [TABLES ...]]] [-K] [-B]

Parse MySQL binlog to SQL you want

optional arguments:
  --stop-never          Wait for more data from the server. default: stop
                        replicate at the last binlog when you start binlog2sql  ##持續同步binlog。可選。不加則同步至執行命令時最新的binlog位置
  --help                help infomation
  -K, --no-primary-key  Generate insert sql without primary key if exists ##對INSERT語句去除主鍵。可選。
  -B, --flashback       Flashback data to start_postition of start_file ##生成回滾語句，可解析大檔案，不受記憶體限制，每列印一千行加一句SLEEP SELECT(1)。可選。與stop-never或no-primary-key不能同時新增。

connect setting:
  -h HOST, --host HOST  Host the MySQL database server located
  -u USER, --user USER  MySQL Username to log in as
  -p PASSWORD, --password PASSWORD
                        MySQL Password to use
  -P PORT, --port PORT  MySQL port to use

range filter:
  --start-file STARTFILE
                        Start binlog file to be parsed  ##起始解析檔案。必須。
  --start-position STARTPOS, --start-pos STARTPOS ##start-file的起始解析位置。可選。預設為start-file的起始位置。
                        Start position of the --start-file
  --stop-file ENDFILE, --end-file ENDFILE            ##末尾解析檔案。可選。預設為start-file同一個檔案。若解析模式為stop-never，此選項失效。
                        Stop binlog file to be parsed. default: '--start-file'
  --stop-position ENDPOS, --end-pos ENDPOS    ##stop-file的末尾解析位置。可選。預設為stop-file的最末位置；若解析模式為stop-never，此選項失效。
                        Stop position of --stop-file. default: latest position
                        of '--stop-file'
  --start-datetime STARTTIME
                        Start reading the binlog at first event having a
                        datetime equal or posterior to the argument; the
                        argument must be a date and time in the local time
                        zone, in any format accepted by the MySQL server for
                        DATETIME and TIMESTAMP types, for example: 2004-12-25
                        11:25:56 (you should probably use quotes for your
                        shell to set it properly). ##從哪個時間點的binlog開始解析，格式必須為datetime，如'2016-11-11 11:11:11'。可選。預設不過濾。
  --stop-datetime STOPTIME
                        Stop reading the binlog at first event having a
                        datetime equal or posterior to the argument; the
                        argument must be a date and time in the local time
                        zone, in any format accepted by the MySQL server for
                        DATETIME and TIMESTAMP types, for example: 2004-12-25
                        11:25:56 (you should probably use quotes for your
                        shell to set it properly).  ##到哪個時間點的binlog停止解析，格式必須為datetime，如'2016-11-11 11:11:11'。可選。預設不過濾。

schema filter:
  -d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
                        dbs you want to process  ##只輸出目標db的sql。可選。預設為空。
  -t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
                        tables you want to process  ##只輸出目標tables的sql。可選。預設為空。
2、binlog2sql要求mysql配置引數需包括以下內容
[mysqld]
log-bin-index = /home/mysql/bin-index
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
log-bin = /home/mysql/mysql-bin
3、binlog2sql解析binlog案例
（1）解析某個binlog全部內容
正常語句是：python binlog2sql.py  -h10.1.0.10 -P3306 -udb_user -p'abc.123' -ddbtest  --start-file='mysql-bin.000028'
由於上文做了binlog2sql別名，這裡可以透過別名直接呼叫命令：
[root@node1 binlog2sql]#  binlog2sql -h10.1.0.10 -P3306 -udb_user -p'abc.123' -ddbtest  --start-file='mysql-bin.000028'
USE dbtest;
DROP TABLE `t1` /* generated by server */;
USE dbtest;
DROP TABLE `T` /* generated by server */;
USE dbtest;
DROP TABLE `T1` /* generated by server */;
USE dbtest;
DROP TABLE `t2` /* generated by server */;
USE dbtest;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
);
INSERT INTO `dbtest`.`t`(`id`) VALUES (1); #start 35104 end 35274 time 2017-03-15 11:47:38
INSERT INTO `dbtest`.`t`(`id`) VALUES (2); #start 35104 end 35274 time 2017-03-15 11:47:38
INSERT INTO `dbtest`.`t`(`id`) VALUES (3); #start 35104 end 35274 time 2017-03-15 11:47:38
INSERT INTO `dbtest`.`t1`(`id`) VALUES (1); #start 36062 end 36233 time 2017-03-15 11:47:45
INSERT INTO `dbtest`.`t1`(`id`) VALUES (2); #start 36062 end 36233 time 2017-03-15 11:47:45
INSERT INTO `dbtest`.`t1`(`id`) VALUES (3); #start 36062 end 36233 time 2017-03-15 11:47:45
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=1 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=2 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=3 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
（2）解析某幾個binlog相應的表t,t1操作記錄

點選(此處)摺疊或開啟

1. (root:localhost:Wed Mar 15 15:01:01 2017)[dbtest]>flush logs;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. (root:localhost:Wed Mar 15 15:01:09 2017)[dbtest]>show master status \G
   
5. *************************** 1. row ***************************
   
6.              File: mysql-bin.000030
   
7.          Position: 120
   
8.      Binlog_Do_DB:
   
9.  Binlog_Ignore_DB:
   
10. Executed_Gtid_Set:
    
11. 1 row in set (0.00 sec)
    
12. (root:localhost:Wed Mar 15 15:01:16 2017)[dbtest]>delete from t1;
    
13. Query OK, 3 rows affected (0.00 sec)
    
14. 
    
15. (root:localhost:Wed Mar 15 15:01:51 2017)[dbtest]>create table t2(id int);
    
16. Query OK, 0 rows affected (0.00 sec)
    
17. 
    
18. (root:localhost:Wed Mar 15 15:02:03 2017)[dbtest]>insert into t2 select * from t;
    
19. Query OK, 3 rows affected (0.01 sec)
    
20. Records: 3 Duplicates: 0 Warnings: 0

##特別注意的是，如果值解析特定表的sql，DDL全部解析，DML過濾相應的表SQL
[root@node1 mysql]# binlog2sql  -h10.0.1.10  -P3306 -udb_user -p'abc.123' -ddbtest -t t t1 --start-file='mysql-bin.000028' --stop-file='mysql-bin.000030'
USE dbtest;
DROP TABLE `t1` /* generated by server */;
USE dbtest;
DROP TABLE `T` /* generated by server */;
USE dbtest;
DROP TABLE `T1` /* generated by server */;
USE dbtest;
DROP TABLE `t2` /* generated by server */;
USE dbtest;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
);
INSERT INTO `dbtest`.`t1`(`id`) VALUES (1); #start 36062 end 36233 time 2017-03-15 11:47:45
INSERT INTO `dbtest`.`t1`(`id`) VALUES (2); #start 36062 end 36233 time 2017-03-15 11:47:45
INSERT INTO `dbtest`.`t1`(`id`) VALUES (3); #start 36062 end 36233 time 2017-03-15 11:47:45
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=1 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=2 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=11 WHERE `id`=3 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
DELETE FROM `dbtest`.`t1` WHERE `id`=11 LIMIT 1; #start 10116 end 10287 time 2017-03-15 15:01:51
DELETE FROM `dbtest`.`t1` WHERE `id`=11 LIMIT 1; #start 10116 end 10287 time 2017-03-15 15:01:51
DELETE FROM `dbtest`.`t1` WHERE `id`=11 LIMIT 1; #start 10116 end 10287 time 2017-03-15 15:01:51
USE dbtest;
create table t2(id int);
（3）資料庫回滾
##透過-B或者--flashback引數解析回滾的SQL，但從解析內容看，也是隻解析DML
[root@node1 mysql]# binlog2sql --flashback   -h10.0.1.10  -P3306 -udb_user -p'abc.123' -ddbtest -tt -tt1  --start-file='mysql-bin.000028' --stop-file='mysql-bin.000030'           
INSERT INTO `dbtest`.`t1`(`id`) VALUES (11); #start 10116 end 10287 time 2017-03-15 15:01:51
INSERT INTO `dbtest`.`t1`(`id`) VALUES (11); #start 10116 end 10287 time 2017-03-15 15:01:51
INSERT INTO `dbtest`.`t1`(`id`) VALUES (11); #start 10116 end 10287 time 2017-03-15 15:01:51
UPDATE `dbtest`.`t1` SET `id`=3 WHERE `id`=11 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=2 WHERE `id`=11 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
UPDATE `dbtest`.`t1` SET `id`=1 WHERE `id`=11 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12
DELETE FROM `dbtest`.`t1` WHERE `id`=3 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45
DELETE FROM `dbtest`.`t1` WHERE `id`=2 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45
DELETE FROM `dbtest`.`t1` WHERE `id`=1 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45
四、知識擴充套件
1、binlog種類以及其優缺點
參考：
（1）Statement：每一條會修改資料的sql都會記錄在binlog中。
    優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能。(相比row能節約多少效能與日誌量，這個取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況，其所產生的日誌量會增加多少，以及帶來的IO效能問題。)
    缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確執行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的複製,像一些特定函式功能，slave可與master上要保持一致會有很多相關問題(如sleep()函式， last_insert_id()，以及user-defined functions(udf)會出現問題).使用以下函式的語句也無法被複制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)，同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖
（2）Row:不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。
    優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程，或function，以及trigger的呼叫和觸發無法被正確複製的問題
    缺點:所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。
（3）Mixed: 是以上兩種level的混合使用，一般的語句修改使用statment格式儲存binlog，如一些函式，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。
2、binlog相關配置引數
log_bin：設定此參數列示啟用binlog功能，並指定路徑名稱
log_bin_index：設定此引數是指定二進位制索引檔案的路徑與名稱
binlog_do_db：此參數列示只記錄指定資料庫的二進位制日誌
binlog_ignore_db：此參數列示不記錄指定的資料庫的二進位制日誌
max_binlog_cache_size：此參數列示binlog使用的記憶體最大的尺寸
binlog_cache_size：此參數列示binlog使用的記憶體大小，可以透過狀態變數binlog_cache_use和binlog_cache_disk_use來幫助測試。
binlog_cache_use：使用二進位制日誌快取的事務數量
binlog_cache_disk_use:使用二進位制日誌快取但超過binlog_cache_size值並使用臨時檔案來儲存事務中的語句的事務數量
max_binlog_size：Binlog最大值，最大和預設值是1GB，該設定並不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進當前日誌，直到事務結束
sync_binlog：這個引數直接影響mysql的效能和完整性。sync_binlog=0表示當事務提交後，Mysql僅僅是將binlog_cache中的資料寫入Binlog檔案，但不執行fsync之類的磁碟 同步指令通知檔案系統將快取重新整理到磁碟，而讓Filesystem自行決定什麼時候來做同步，這個是效能最好的。sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，同志檔案系統將Binlog檔案快取重新整理到磁碟。Mysql中預設的設定是sync_binlog=0，即不作任何強制性的磁碟重新整理指令，這時效能是最好的，但風險也是最大的。一旦系統繃Crash，在檔案系統快取中的所有Binlog資訊都會丟失
binlog_format：binlog日誌格式，statement/row/mixed
binlog_row_image:其是在5.6之後有的引數，FULL記錄每一行的變更，minimal只記錄影響後的行，前提是row模式。
3、binlog刪除方法
reset master;   ##刪除master的binlog
reset slave;    ##刪除slave的中繼日誌,reset slave all
purge master logs before '2016-03-15 10:00:00';  ##刪除指定日期以前的日誌索引中binlog日誌檔案
purge master logs to 'binlog.000001';  ##刪除指定日誌檔案的日誌索引中binlog日誌檔案
4、mysqlbinlog解析row模式的binlog日誌
mysqlbinlog --base64-output=decode-rows -v --start-datetime='2016-07-13 10:00:00' --stop-datetime='2016-07-13 10:40:00'  mysql-bin.000021
5、主從binlog和relaylog檔案內容
主庫：
    File: binlog.007247                   ##主庫當前寫入的binlog檔案
    Position: 132941350                ##主庫當前寫入的binlog檔案position
從庫：
    Master_Log_File: binlog.007247                    ##從庫io thread當前讀取的主庫binlog檔案
    Read_Master_Log_Pos: 100309902               ##從庫io thread當前讀取的主庫binlog檔案的position
    Relay_Log_File: mysql-relay-bin.025950          ##從庫SQL thread當前寫入的relay-log檔案
    Relay_Log_Pos: 29008629                           ##從庫SQL thread當前寫入的replay-log檔案的position
    Relay_Master_Log_File: binlog.007243           ##當前執行的replay-log對應的主庫binlog檔案
    Exec_Master_Log_Pos: 565883515               ##當前執行的binlog檔案的position
    Relay_Log_Space: 4395318217                    ##讀取與執行的relay-log間隔
    cat relay-log.info                 ##relay-log執行binlog資訊
    ./mysql-relay-bin.025950            ##當前SQL thread寫入的relay-log檔案
    29008629                    ##當前SQL thread寫入的relay-log檔案的position
    binlog.007243                    ##當前執行的relay-log檔案對應的binlog 檔案
    565883515                    ##當前執行的relay-log檔案對應的binlog 檔案的position
    cat master.info                 ##主從同步資訊記錄檔案
    binlog.007247                    ##當前讀取的主庫binlog檔案
    372008635                    ##當前讀取的主庫binlog檔案對應的position