我也是只菜雞，blog寫的不對或者不嚴(yán)謹?shù)牡胤竭€請大伙指出來，我及時改正，免得誤人子弟。
自貢網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項目制作，到程序開發(fā)，運營維護。成都創(chuàng)新互聯(lián)成立于2013年到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。
實驗環(huán)境：
CentOS7.3.1611 + MySQL社區(qū)版 5.7.19
參考：
小菜鳥DBA的微信公眾號推送
官方文檔：
https://dev.mysql.com/doc/internals/en/binary-log-versions.html
https://dev.mysql.com/doc/internals/en/row-based-binary-logging.html
https://dev.mysql.com/doc/internals/en/event-classes-and-types.html
https://dev.mysql.com/doc/internals/en/event-header-fields.html
https://dev.mysql.com/doc/internals/en/event-meanings.html
https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
3個在線工具：
http://tool.oschina.net/hexconvert/ 在線進制轉(zhuǎn)換
http://tool.chinaz.com/Tools/unixtime.aspx Unix時間戳
https://www.bejson.com/convert/ox2str/ 16進制轉(zhuǎn)字符串
binlog實際上由一個個不同類型的binlog event組成，每個binlog event還包含了event header部分和event data部分(可選)。
【注意：每個event最后還有4bytes的校驗位，官方文檔并沒有提到這個地方，不然分析event物理格式時候會發(fā)現(xiàn)event長度對不上號】
常見的一個binlog物理文件有如下組成部分：
1、4字節(jié)的magic number作為binlog文件的開頭
2、N個不同類型的binlog event
3、rotate event 作為binlog文件的結(jié)尾（正在使用的binlog里面是沒有rotate event的）
此外，還有一個索引文件記錄當(dāng)前有哪些binlog文件，及當(dāng)前正在使用的binlog文件。（文件名類似：mysql-bin.index）
下表就是的binlog event的一般格式：
+=====================================+
| event | timestamp 0 : 4 |
| header +----------------------------+
| | type_code 4 : 1 | = FORMAT_DESCRIPTION_EVENT = 15（binlog v4）
| +----------------------------+
| | server_id 5 : 4 |
| +----------------------------+
| | event_length 9 : 4 | >= 91
| +----------------------------+
| | next_position 13 : 4 |
| +----------------------------+
| | flags 17 : 2 |
+=====================================+
| event | binlog_version 19 : 2 | = 4
| data +----------------------------+
| | server_version 21 : 50 |
| +----------------------------+
| | create_timestamp 71 : 4 |
| +----------------------------+
| | header_length 75 : 1 |
| +----------------------------+
| | post-header 76 : n | = array of n bytes, one byte per event
| | lengths for all | type that the server knows about
| | event types |
+=====================================+
常用的EVENT如下：
FORMAT_DESCRIPTION_EVENT：binlog文件的第一個event，記錄版本號等元數(shù)據(jù)信息
QUERY_EVENT: 存儲statement類的信息，基于statement的binlog格式記錄sql語句，在row模式下記錄事務(wù)begin標(biāo)簽
XID_EVENT: 二階段提交xid記錄
TABLE_MAP_EVENT: row模式下記錄表源數(shù)據(jù)，對讀取行記錄提供規(guī)則參考，后面會詳細介紹
WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT: row模式下記錄對應(yīng)行數(shù)據(jù)變化的記錄
GTID_LOG_EVENT: 這個就是記錄GTID事務(wù)號了，用于5.6版本之后基于GTID同步的方式
ROTATE_EVENT: 連接下一個binlog文件
需要了解更全面的Event類型詳見： https://dev.mysql.com/doc/internals/en/event-classes-and-types.html （全部的定義在源代碼的binlog_event.h中，看了下5.7代碼比5.6又增加了幾個event類型）
下面是我截取的一個完整的binlog文件，具體的events如下：
目前，我們一般都是使用row格式的binlog，其他的mixed和statement格式的binlog這里不去關(guān)注了。
對于row格式的DML操作而言，實際上在binlog里面記錄的是：TABLE_MAP_EVENT+ ROW_LOG_EVENT（ROW_LOG_EVENT還可以細分為WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT）
為什么一個update在ROW模式下需要分解成兩個event：一個Table_map，一個Update_rows?
我們想象一下，一個update如果更新了10000條數(shù)據(jù)，那么對應(yīng)的表結(jié)構(gòu)信息是否需要記錄10000次?其實是對同一個表的操作，所以這里binlog只是記錄了一個Table_map用于記錄表結(jié)構(gòu)相關(guān)信息,而后面的Update_rows記錄了更新數(shù)據(jù)的行信息。他們之間是通過table_id來聯(lián)系的?！総able_id不是固定的，是一個變量，占用的是table_definition_cache和table_open_cache空間（因此flush tables會造成table_id的增長）】
如下是一個insert插入1條記錄的binlog，可以看到有table_map+ write_rows 這2個event組成。
table_map記錄的是表的元數(shù)據(jù)信息，例如庫名、表名、字段類型等信息。
補充：
關(guān)于table_id的幾篇干貨：
http://blog.itpub.net/22664653/viewspace-1158547/ 【楊奇龍】
http://agapple.iteye.com/blog/1797061
http://www.cnblogs.com/yuyue2014/p/3721172.html
http://www.sohu.com/a/130698375_610509 【宋利兵】
http://www.cnblogs.com/cenalulu/archive/2012/09/24/2699907.html 【盧鈞軼】
其他的幾個EVENT類型：
官方文檔：https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
FORMAT_DESCRIPTION_EVENT
這個是最基礎(chǔ)的event，每個新的binlog頭部就帶有這個event。每一個binlog文件只能存在一個FORMAT_DESCRIPTION_EVENT。
Fixed data part:
2 bytes. The binary log format version. This is 4 in MySQL 5.0 and up.
50 bytes. The MySQL server's version (example: 5.0.14-debug-log), padded with 0x00 bytes on the right.
4 bytes. Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.
1 byte. The header length. This length - 19 gives the size of the extra headers field at the end of the header for other events.
Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about.
FORMAT_DESCRIPTION_EVENT 實例：
flush logs; 產(chǎn)生一個全新的binlog文件，導(dǎo)出后如下：
master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000002
00000000 fe 62 69 6e f6 e3 fe 59 0f 01 00 00 00 74 00 00 |.bin...Y.....t..|
00000010 00 78 00 00 00 01 00 04 00 35 2e 36 2e 33 37 2d |.x.......5.6.37-|
00000020 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 |log.............|
00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 13 |................|
00000050 38 0d 00 08 00 12 00 04 04 04 04 12 00 00 5c 00 |8.............\.|
00000060 04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a |................|
00000070 19 19 00 01 08 4c 67 48 |.....LgH|
00000078
magic number （4bytes）
fe 62 69 6e
event header （19bytes）
f6 e3 fe 59 timestamp
0f type_code 表示binlog采用v4版本的
01 00 00 00 server_id
74 00 00 00 event_length 116bytes
78 00 00 00 next_position 下一個event從120開始
01 00 flags
event data：
04 00 binlog version ，表示v4版的binlog格式
35 2e 36 2e 33 37 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 表示的是server_version ，轉(zhuǎn)換成字符串就是5.6.37-log
00 00 00 00 create_timestamp ，用的是相對時間
13 表示的是event header的長度，十進制表示就是19bytes
38 0d 00 08 00 12 00 04 04 04 04 12 00 00 5c 00 04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a 19 19 00 01 36種event 類型
08 4c 67 48 4bytes校驗位。
36種event類型：https://dev.mysql.com/doc/internals/en/event-classes-and-types.html
STOP_EVENT:
當(dāng)正常關(guān)閉mysqld時候，或者是從庫上執(zhí)行了reset slave 都 產(chǎn)生這個stop_event
A Stop_log_event is written under these circumstances:
A master writes the event to the binary log when it shuts down
A slave writes the event to the relay log when it shuts down or when a RESET SLAVE statement is executed
STOP_EVENT實例：
/etc/init.d/mysqld restart
master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000002 -s 120
00000078 f9 f0 fe 59 03 01 00 00 00 17 00 00 00 8f 00 00 |...Y............|
00000088 00 00 00 39 d3 4f ad |...9.O.|
0000008f
含義未知。官方?jīng)]有說。
QYERY_EVENT:
使用begin命令開啟一個事務(wù)的時候，會產(chǎn)生QUERY_EVENT
固定部分：
4bytes thread_id 可以用于審計
4bytes 該語句的執(zhí)行時長，單位秒
1byte 執(zhí)行命令時候所在的庫名的字節(jié)長度
2bytes 錯誤代碼
2bytes 記錄data part部分variable status的長度
可變部分：
0或者更多狀態(tài)變量
默認的庫名
SQL_Statement
master [localhost] {root} (test) > begin;
master [localhost] {root} (test) > insert into tttt2 select 'AAAA';
master [localhost] {root} (test) > commit;
master [localhost] {root} (test) > show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 199 | BEGIN |
| mysql-bin.000001 | 199 | Query | 1 | 304 | use `test`; insert into tttt2 select 'AAAA' |
| mysql-bin.000001 | 304 | Xid | 1 | 335 | COMMIT /* xid=40 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
4 rows in set (0.00 sec)
[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000001 -s 120
00000078 de f3 fe 59 02 01 00 00 00 4f 00 00 00 c7 00 00 |...Y.....O......|
00000088 00 08 00 01 00 00 00 00 00 00 00 04 00 00 21 00 |..............!.|
00000098 00 00 00 00 00 01 00 00 00 40 00 00 00 00 06 03 |.........@......|
000000a8 73 74 64 04 21 00 21 00 08 00 0c 01 74 65 73 74 |std.!.!.....test|
000000b8 00 74 65 73 74 00 42 45 47 49 4e 37 1f 09 57 de |.test.BEGIN7..W.|
000000c8 f3 fe 59 02 01 00 00 00 69 00 00 00 30 01 00 00 |..Y.....i...0...|
000000d8 00 00 01 00 00 00 00 00 00 00 04 00 00 21 00 00 |.............!..|
000000e8 00 00 00 00 01 00 00 00 40 00 00 00 00 06 03 73 |........@......s|
000000f8 74 64 04 21 00 21 00 08 00 0c 01 74 65 73 74 00 |td.!.!.....test.|
00000108 74 65 73 74 00 69 6e 73 65 72 74 20 69 6e 74 6f |test.insert into|
00000118 20 74 74 74 74 32 20 73 65 6c 65 63 74 20 27 41 | tttt2 select 'A|
00000128 41 41 41 27 73 f4 e2 90 e0 f3 fe 59 10 01 00 00 |AAA's......Y....|
00000138 00 1f 00 00 00 4f 01 00 00 00 00 28 00 00 00 00 |.....O.....(....|
00000148 00 00 00 17 4f 16 46 |....O.F|
0000014f
.... 這個類型的event分析卡住了，誰來幫我下。。。。。。。。。 【參考http://www.jianshu.com/p/c16686b35807】
ROTATE_EVENT：
當(dāng)flush logs或者正常的切割binlog時候，會產(chǎn)生ROTATE_EVENT。
When a binary log file exceeds a size limit, a ROTATE_EVENT is written at the end of the file that points to the next file in the squence. This event is information for the slave to know the name of the next binary log it is going to receive.
Fixed data part:
8 bytes. The position of the first event in the next log file. Always contains the number 4 (meaning the next event starts at position 4 in the next binary log). This field is not present in v1; presumably the value is assumed to be 4.
Variable data part:
The name of the next binary log. The filename is not null-terminated. Its length is the event size minus the size of the fixed parts.
XID_EVENT：
為了事務(wù)的一致性，寫binlog的時候，先寫事務(wù)的語句，然后寫xid標(biāo)志，最后才是提交COMMIT命令。
Fixed part為空
variable part 8bytes，記錄的是xid編號
關(guān)于rotate event的例子：
master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.37-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Rotate | 1 | 167 | mysql-bin.000002;pos=4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000001 -s 4 -n 19 導(dǎo)出format desc event的內(nèi)容
00000004 e2 e3 fe 59 0f 01 00 00 00 74 00 00 00 78 00 00 |...Y.....t...x..|
00000014 00 00 00 |...|
00000017
00 00 最后2位都是0000表示這個binlog關(guān)閉了，如果是01 00表示這個binlog還在使用中。
[root@test_mysql26 /root/sandboxes/rsandbox_5_6_37/master/data ]# hexdump -C mysql-bin.000001 -s 120 導(dǎo)出rotate event的內(nèi)容
00000078 f6 e3 fe 59 04 01 00 00 00 2f 00 00 00 a7 00 00 |...Y...../......|
00000088 00 00 00 04 00 00 00 00 00 00 00 6d 79 73 71 6c |...........mysql|
00000098 2d 62 69 6e 2e 30 30 30 30 30 32 ce 7f 95 b8 |-bin.000002....|
000000a7
event_header(19bytes)具體如下：
f6 e3 fe 59 timestamp
04 type_code 其event type是0x04,表示是一個rotate event
01 00 00 00 server_id 表示serverid是 1
2f 00 00 00 event_length 長度為2f，十進制表示就是47bytes
a7 00 00 00 next_position 下一個event起始位置為0xa7，十進制表示就是167
00 00 flags 0000表示這個binlog已經(jīng)正常關(guān)閉了
然后是event data部分，具體如下：
04 00 00 00 00 00 00 00 Fixed data部分，8bytes,記錄的是下一個binlog的位置偏移4
6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 32 Variable data部分，記錄的是下一個binlog的文件名mysql-bin.000002
ce 7f 95 b8 含義未知
TABLE_MAP_EVENT：
Used for row-based binary logging beginning with MySQL 5.1.5.
Fixed data part:
6 bytes. The table ID.
2 bytes. Reserved for future use.
Variable data part:
1 byte. The length of the database name. 庫名的長度
Variable-sized. The database name (null-terminated). 庫名
1 byte. The length of the table name. 表名的長度
Variable-sized. The table name (null-terminated). 表名
Packed integer. The number of columns in the table. 列的數(shù)量
Variable-sized. An array of column types, one byte per column. To find the meanings of these values, look atenum_field_types in the mysql_com.h header file. 每一列的數(shù)據(jù)類型
Packed integer. The length of the metadata block. metadata block的長度
Variable-sized. The metadata block; see log_event.h for contents and format.
Variable-sized. Bit-field indicating whether each column can be NULL, one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes. 該部分記錄字段是否允許為空，一位代表一個字段，占用字int((N+7/8))bytes,N為字段數(shù)
WRITE_ROWS_EVENT、DELETE_ROW_EVENTS、UPDATE_ROW_EVENTS 都參考如下這種解釋：
Used for row-based binary logging beginning with MySQL 5.1.18.
[TODO: following needs verification; it's guesswork]
Fixed data part:
6 bytes. The table ID.
2 bytes. Reserved for future use.
Variable data part:
Packed integer. The number of columns in the table. 列的數(shù)量
Variable-sized. Bit-field indicating whether each column is used, one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes.
Variable-sized (for UPDATE_ROWS_LOG_EVENT only). Bit-field indicating whether each column is used in theUPDATE_ROWS_LOG_EVENT after-image; one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes.
Variable-sized. A sequence of zero or more rows. The end is determined by the size of the event. Each row has the following format:
Variable-sized. Bit-field indicating whether each field in the row is NULL. Only columns that are "used" according to the second field in the variable data part are listed here. If the second field in the variable data part has N one-bits, the amount of storage required for this field is INT((N+7)/8) bytes.
Variable-sized. The row-image, containing values of all table fields. This only lists table fields that are used (according to the second field of the variable data part) and non-NULL (according to the previous field). In other words, the number of values listed here is equal to the number of zero bits in the previous field (not counting padding bits in the last byte).
The format of each value is described in the log_event_print_value() function in log_event.cc.
(for UPDATE_ROWS_EVENT only) the previous two fields are repeated, representing a second table row.
For each row, the following is done:
For WRITE_ROWS_LOG_EVENT, the row described by the row-image is inserted.
For DELETE_ROWS_LOG_EVENT, a row matching the given row-image is deleted.
For UPDATE_ROWS_LOG_EVENT, a row matching the first row-image is removed, and the row described by the second row-image is inserted.
HEARTBEAT_LOG_EVENT：
A Heartbeat_log_event is sent by a master to a slave to let the slave know that the master is still alive. Events of this type do not appear in the binary or relay logs. They are generated on a master server by the thread that dumps events and sent straight to the slave without ever being written to the binary log. The slave examines the event contents and then discards it without writing it to the relay log.
主從心跳的heartbeat event是不寫到binlog里面的。Sent by a master to a slave to let the slave know that the master is still alive. Not written to log files.
重慶分公司
重慶分公司
