解析MYSQL BINLOG二進位制格式(9)--infobin解析binlog幫助文件

原創：轉載請說明出處謝謝！
上接
http://blog.itpub.net/7728585/viewspace-2133188/ 解析MYSQL BINLOG 二進位制格式(1)--準備工作 
http://blog.itpub.net/7728585/viewspace-2133189/ 解析MYSQL BINLOG 二進位制格式(2)--FORMAT_DESCRIPTION_EVENT 
http://blog.itpub.net/7728585/viewspace-2133321/ 解析MYSQL BINLOG 二進位制格式(3)--QUERY_EVENT 
http://blog.itpub.net/7728585/viewspace-2133429/ 解析MYSQL BINLOG 二進位制格式(4)--TABLE_MAP_EVENT 
http://blog.itpub.net/7728585/viewspace-2133463/ 解析MYSQL BINLOG 二進位制格式(5)--WRITE_ROW_EVENT 
http://blog.itpub.net/7728585/viewspace-2133469/ 解析MYSQL BINLOG 二進位制格式(6)--UPDATE_ROW_EVENT/DELETE_ROW_EVENT  
http://blog.itpub.net/7728585/viewspace-2133502/ 解析MYSQL BINLOG 二進位制格式(7)--Xid_log_event/XID_EVENT 
http://blog.itpub.net/7728585/viewspace-2133506/ 解析MYSQL BINLOG二進位制格式(8)--GTID_LOG_EVENT/ANONYMOUS_GTID_LOG_EVENT及其他 


1、工具原理和作用：
   本工具是我透過學習binlog event寫的一個能夠直接讀取binlog二進位制檔案的工具，使用語言C語言，編譯器gcc，
   他是透過讀取各個主要位元組的輸出。因為mysqlbinlog輸出有時候過於繁雜，infobin工具可以幫助初次定位，結合
   mysqlbinlog進行分析,同時寫這個工具也是為了驗證學習event的正確性。
  最後生成一個總的彙總，彙總資訊是我最近更新的加入了：
  如下部分：
-------------Total now--------------
Trx total[counts]:7665
Event total[counts]:183513
Max trx event size:8193(bytes) Pos:306929600[0X124B5FC0]
Avg binlog size(/sec):609537.375(bytes)[595.251(kb)]
Avg binlog size(/min):36572244.000(bytes)[35715.082(kb)]
--Piece view:
(1)Time:1487126981-1487127083(102(s)) piece:237110047(bytes)[231552.781(kb)]
(2)Time:1487127083-1487127083(0(s)) piece:237110047(bytes)[231552.781(kb)]
(3)Time:1487127083-1487127083(0(s)) piece:237110047(bytes)[231552.781(kb)]
(4)Time:1487127083-1487128812(1729(s)) piece:237110047(bytes)[231552.781(kb)]
(5)Time:1487128812-1487128926(114(s)) piece:237110047(bytes)[231552.781(kb)]
--Large than 1000(bytes) trx:
(1)Trx_size:815352746(bytes)[796242.938(kb)] trx_begin_p:1174606[0X11EC4E] trx_end_p:816527352[0X30AB37F8]
(2)Trx_size:3108(bytes)[3.035(kb)] trx_begin_p:817520533[0X30BA5F95] trx_end_p:817523641[0X30BA6BB9]
(3)Trx_size:3116(bytes)[3.043(kb)] trx_begin_p:817523706[0X30BA6BFA] trx_end_p:817526822[0X30BA7826]
(4)Trx_size:5910(bytes)[5.771(kb)] trx_begin_p:817526887[0X30BA7867] trx_end_p:817532797[0X30BA8F7D]
(5)Trx_size:11538(bytes)[11.268(kb)] trx_begin_p:817532862[0X30BA8FBE] trx_end_p:817544400[0X30BABCD0]
(6)Trx_size:22754(bytes)[22.221(kb)] trx_begin_p:817544465[0X30BABD11] trx_end_p:817567219[0X30BB15F3]

    


2、限制：
   --只能使用在Little_endian上，編譯是在LINUX gcc編譯的
   --load data infile event是沒有檢測的
   --不能讀取出row event的語句，因為沒有寫那麼複雜
   --可以讀取出statement格式的語句，但是為了簡潔做了35位元組的截斷，方便輸出
     這些東西在mysqlbinlog解析中都有。
3、使用方法和獲取
    獲取可以透過百度雲盤
   
    獲得，編譯的只有LINUX64版本的

使用幫助如下：
[root@testmy data]# ./infobin   
USAGE ERROR!
[Author]: gaopeng [QQ]:22389860 [blog]:http://blog.itpub.net/7728585/ 
--USAGE:./infobin [binlogfile] [piece] [bigtrxsize]
[binlogfile]:binlog file!
[piece]:how many piece will split,is a Highly balanced histogram,
        find which time generate biggest binlog.(must:piece<2000000)
[bigtrxsize](bytes):larger than this size trx will view.(must:trx>256(bytes))

如：./infobin test.000200 5 257

5：代表是piece，這是一個高度均衡直方圖的桶數量，後面會看到輸出解釋
257：是大約257 bytes的事物輸出到最後

4、輸出解析
我們以MYSQL 5.7 row format格式為例，我做的語句是
mysql> select * from testnull2;
+------+---------+---------+
| id   | name1   | name2   |
+------+---------+---------+
|    1 | gaopeng | gaopeng |
|    1 | gaopeng | gaopeng |
+------+---------+---------+
2 rows in set (0.00 sec)

mysql> create table testtool like testnull2;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into testtool select * from testnull2;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0


使用./infobin test.000200 得到的輸出如下：
[root@testmy data]# ./infobin test.000200 5 512
Check is Little_endian
Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com 
Waring: This tool only Little_endian platform!
Little_endian check ok!!!
-------------Now begin--------------
Check Mysql Version is:5.7.13-log
Check Mysql binlog format ver is:V4
Warning:Check This binlog is not closed!
Check This binlog total size:664(bytes)
Note:load data infile not check!
------------Detail now--------------
>Format description log Event:Pos:4(0X4) N_pos:123(0X7b) Time:1487035963 Event_size:119(bytes) 
>Previous gtid Event:Pos:123(0X7b) N_pos:194(0Xc2) Time:1487035963 Event_size:71(bytes) 
>Gtid Event:Pos:194(0Xc2) N_pos:259(0X103) Time:1487035999 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100463
-->Query Event:Pos:259(0X103) N_Pos:362(0X16a) Time:1487035999 Event_size:103(bytes) 
Exe_time:0  Use_db:test Statment(35b-trun):create table testtool like testnull Gno:1100463
>Gtid Event:Pos:362(0X16a) N_pos:427(0X1ab) Time:1487036014 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100464
-->Query Event:Pos:427(0X1ab) N_Pos:499(0X1f3) Time:1487036014 Event_size:72(bytes) 
Exe_time:0  Use_db:test Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1100464
---->Map Event:Pos499(0X1f3) N_pos:556(0X22c) Time:1487036014 Event_size:57(bytes) 
TABLE_ID:346 DB_NAME:test TABLE_NAME:testtool Gno:1100464
------>Insert Event:Pos:556(0X22c) N_pos:633(0X279) Time:1487036014 Event_size:77(bytes) 
Dml on table: test.testtool  table_id:346 Gno:1100464 
>Xid Event:Pos:633(0X279) N_Pos:664(0X298) Time:1487036014 Event_size:31(bytes) 
COMMIT; /*!Trx end*/ Gno:1100464
-------------Total now--------------
Trx total[counts]:1
Event total[counts]:10
Max trx event size:119(bytes) Pos:4[0X4]
Avg binlog size(/sec):5.007(bytes)[0.005(kb)]
Avg binlog size(/min):300.426(bytes)[0.293(kb)]
--Piece view:
(1)Time:1487035963-1487035999(36(s)) piece:141(bytes)[0.138(kb)]
(2)Time:1487035999-1487036014(15(s)) piece:141(bytes)[0.138(kb)]
(3)Time:1487036014-1487036014(0(s)) piece:141(bytes)[0.138(kb)]
(4)Time:1487036014-1487036014(0(s)) piece:141(bytes)[0.138(kb)]
(5)Time:1487036014-1487036104(90(s)) piece:141(bytes)[0.138(kb)]
--Large than 257(bytes) trx:
No trx find!

now begin部分：
一目瞭然需要說明一點Warning:Check This binlog is not closed!說明這個binlog是當前正在使用binlog
這個和mysqlbinlog
# at 4
#170214  9:32:43 server id 93157  end_log_pos 123 CRC32 0xc5518ba9      Start: binlog v 4, server v 5.7.13-log created 170214  9:32:43
# Warning: this binlog is either in use or was not closed properly.
中是一個意思
Detail now部分：
*1、event都以>開始，但是一個事物的event我使用--> ----> ------->來進行區別化更加利於閱讀，如果
   仔細研究過event這些event一定不會陌生
*2、
   Pos:當前event位置 
   N_pos:下一個event位置，
   Gtid: 當然就是GTID如果是匿名事物就是ANONYMOUS 其GTID為0
   Time:新紀元時間以來的秒數  可以用LINUX命令換算 如：date -s "@1487035999"
   Event_size：這個event有多大
   Gno:gtid的事物號部分，我用來標示它們是一個事物
   TABLE_ID：是行格式特有的，這個用來保證slave複製的正確性
   Use_db: use database 預設當前在哪個資料下，是query event特有的
   DB_NAME: 這是map event特有的，也是行格式特有的，記錄的是表所在的資料庫，和Use_db有區別，     
   Statment(35b-trun)：在query event中記錄的語句為了方便輸出將語句做35位元組階段
   /*!Trx begin!*/：表示這是一個事物的開始，如果是gtid模式需要向前推一個event，因為gtid event也算到事物中
   /*!Trx end*/：自然就是事物的結束點
   
   mysqlbinlog中也是一致的比如：
   >Gtid Event:Pos:194(0Xc2) N_pos:259(0X103) Time:1487035999 Event_size:65(bytes) 
   Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100463
   [root@testmy ~]# date -s "@1487035999"
   Tue Feb 14 09:33:19 CST 2017
   對應mysqlbinlog的如下部分：
   # at 194
   #170214  9:33:19 server id 93157  end_log_pos 259 CRC32 0xb664a0c6      GTID [commit=yes]
   SET @@SESSION.GTID_NEXT= '4a6f2a67-5d87-11e6-a6bd-000c29a879a3:1100463'/*!*/;
 

關於 TABLE_ID Use_db DB_NAME 必須著重說明：
   在語句模式下：
   use mysql
   insert into test.testtool select * from test.testnull2;
   這種語句和salve 端--replicate-do-db 類引數一起出現可能呢出現問題，因為語句模式只有query event 而沒有map event，
   導致過濾的時候這種語句因為它是在mysql下：
   如下：
>Gtid Event:Pos:800(0X320) N_pos:865(0X361) Time:1487036301 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100467
-->Query Event:Pos:865(0X361) N_Pos:945(0X3b1) Time:1487036301 Event_size:80(bytes) 
Exe_time:0  Use_db:mysql Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1100467
-->Query Event:Pos:945(0X3b1) N_Pos:1074(0X432) Time:1487036301 Event_size:129(bytes) 
Exe_time:0  Use_db:mysql Statment(35b-trun):insert into test.testtool select *  Gno:1100467
>Xid Event:Pos:1074(0X432) N_Pos:1105(0X451) Time:1487036301 Event_size:31(bytes) 
COMMIT; /*!Trx end*/ Gno:1100467
  注意這裡Use_db:mysql
  同樣的語句在row格式下：
>Gtid Event:Pos:497(0X1f1) N_pos:562(0X232) Time:1487036269 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100466
-->Query Event:Pos:562(0X232) N_Pos:635(0X27b) Time:1487036269 Event_size:73(bytes) 
Exe_time:0  Use_db:mysql Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1100466
---->Map Event:Pos635(0X27b) N_pos:692(0X2b4) Time:1487036269 Event_size:57(bytes) 
TABLE_ID:346 DB_NAME:test TABLE_NAME:testtool Gno:1100466
------>Insert Event:Pos:692(0X2b4) N_pos:769(0X301) Time:1487036269 Event_size:77(bytes) 
Dml on table: test.testtool  table_id:346 Gno:1100466 
>Xid Event:Pos:769(0X301) N_Pos:800(0X320) Time:1487036269 Event_size:31(bytes) 
COMMIT; /*!Trx end*/ Gno:1100466
 注意Use_db:mysql DB_NAME:test Dml on table: test.testtool ,記錄的正確性就是table id聯絡的。
 這也保證row格式下複製的正確性
 但是DDL語句始終是statement模式。
 這一點在MYSQL 官方文件也有說明：
Statement-based replication.  Tell the slave SQL thread to restrict replication to statements where
the default database (that is, the one selected by USE) is db_name. To specify more than one database,
use this option multiple times, once for each database; however, doing so does notreplicate crossdatabase statements
 such as UPDATE some_db.some_tableSET foo='bar'while a different
database (or no database) is selected.
Warning
To specify multiple databases you mustuse multiple instances of this option.
Because database names can contain commas, if you supply a comma
separated list then the list will be treated as the name of a single database.
An example of what does not work as you might expect when using statement-based replication: If
the slave is started with --replicate-do-db=salesand you issue the following statements on the
master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this “check just the default database”behavior is that it is difficult from the statement
alone to know whether it should be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATEstatements that act across multiple databases). It is also faster to
check only the default database rather than all databases if there is no need.

*3、如何辨別是一個事物

這就是row模式下的一個事物(算上GTID生成event)：
>Gtid Event:Pos:362(0X16a) N_pos:427(0X1ab) Time:1487036014 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100464
-->Query Event:Pos:427(0X1ab) N_Pos:499(0X1f3) Time:1487036014 Event_size:72(bytes) 
Exe_time:0  Use_db:test Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1100464
---->Map Event:Pos499(0X1f3) N_pos:556(0X22c) Time:1487036014 Event_size:57(bytes) 
TABLE_ID:346 DB_NAME:test TABLE_NAME:testtool Gno:1100464
------>Insert Event:Pos:556(0X22c) N_pos:633(0X279) Time:1487036014 Event_size:77(bytes) 
Dml on table: test.testtool  table_id:346 Gno:1100464 
>Xid Event:Pos:633(0X279) N_Pos:664(0X298) Time:1487036014 Event_size:31(bytes) 
COMMIT; /*!Trx end*/ Gno:1100464


這是statement模式下的一個事物(算上GTID生成event)：
>Gtid Event:Pos:800(0X320) N_pos:865(0X361) Time:1487036301 Event_size:65(bytes) 
Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100467
-->Query Event:Pos:865(0X361) N_Pos:945(0X3b1) Time:1487036301 Event_size:80(bytes) 
Exe_time:0  Use_db:mysql Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1100467
-->Query Event:Pos:945(0X3b1) N_Pos:1074(0X432) Time:1487036301 Event_size:129(bytes) 
Exe_time:0  Use_db:mysql Statment(35b-trun):insert into test.testtool select *  Gno:1100467
>Xid Event:Pos:1074(0X432) N_Pos:1105(0X451) Time:1487036301 Event_size:31(bytes) 
COMMIT; /*!Trx end*/ Gno:1100467
 
Total now 部分：
Trx total[counts]:1 總的事物個數
Event total[counts]:10 總的event個數
Avg binlog size(/sec):5.007(bytes)[0.005(kb)]
Avg binlog size(/min):300.426(bytes)[0.293(kb)]
 平均每秒每分生成的binlog大小
--Piece view:
(1)Time:1487035963-1487035999(36(s)) piece:141(bytes)[0.138(kb)]
(2)Time:1487035999-1487036014(15(s)) piece:141(bytes)[0.138(kb)]
(3)Time:1487036014-1487036014(0(s)) piece:141(bytes)[0.138(kb)]
(4)Time:1487036014-1487036014(0(s)) piece:141(bytes)[0.138(kb)]
(5)Time:1487036014-1487036104(90(s)) piece:141(bytes)[0.138(kb)]

根據指定piece大小得到一個高度均衡直方圖，這個直方圖用於發現是否有某個時間段生成binlog特別大，
如上明顯1487036014這個時間的event 比較多，可能是大事物。

--Large than 257(bytes) trx:
Node trx find!

大約257 BYTES個事務，這裡測試的沒有如果有如下：
--Large than 1000(bytes) trx:
(1)Trx_size:815352746(bytes)[796242.938(kb)] trx_begin_p:1174606[0X11EC4E] trx_end_p:816527352[0X30AB37F8]
(2)Trx_size:3108(bytes)[3.035(kb)] trx_begin_p:817520533[0X30BA5F95] trx_end_p:817523641[0X30BA6BB9]
(3)Trx_size:3116(bytes)[3.043(kb)] trx_begin_p:817523706[0X30BA6BFA] trx_end_p:817526822[0X30BA7826]
(4)Trx_size:5910(bytes)[5.771(kb)] trx_begin_p:817526887[0X30BA7867] trx_end_p:817532797[0X30BA8F7D]
(5)Trx_size:11538(bytes)[11.268(kb)] trx_begin_p:817532862[0X30BA8FBE] trx_end_p:817544400[0X30BABCD0]
(6)Trx_size:22754(bytes)[22.221(kb)] trx_begin_p:817544465[0X30BABD11] trx_end_p:817567219[0X30BB15F3]
(7)Trx_size:45226(bytes)[44.166(kb)] trx_begin_p:817567284[0X30BB1634] trx_end_p:817612510[0X30BBC6DE]