binlog想必大家都不陌生，在主從複製或某些情況下的資料恢復會用到。由於binlog是二進位數據，因此要查看一般都藉助mysqlbinlog工具。這篇筆記分析了binlog格式，希望能夠了解下mysqlbinlog工具背後所做的事情。
1.什麼時候寫binlog
在說明什麼時候寫binlog前，先簡單介紹下binlog的用途。 binlog是二進位日誌文件，用於記錄mysql的資料更新或潛在更新(例如DELETE語句執行刪除而實際並沒有符合條件的資料)，在mysql主從複製中就是依靠的binlog。在mysql中開啟binlog需要設定my.cnf中的log_bin參數，另外也可以透過binlog_do_db
指定要記錄binlog的資料庫和binlog_ignore_db指定不記錄binlog的資料庫。對執行中的mysql要啟用binlog可以透過指令SET SQL_LOG_BIN=1來設定。設定完成，我們就可以來測試binlog了。
需要注意下innodb引擎中的redo/undo log與mysql binlog是完全不同的日誌，它們主要有以下幾個差異：
a）層次不同。 redo/undo log是innodb層維護的，而binlog是mysql server層維護的，跟採用何種引擎沒有關係，記錄的是所有引擎的更新操作的日誌記錄。 innodb的redo/undo log更詳細的說明可以參考姜承堯的《mysql技術內幕-innodb儲存引擎》一書中相關章節。
b）記錄內容不同。 redo/undo日誌記錄的是每個頁的修改情況，屬於實體日誌+邏輯日誌結合的方式（redo log物理到頁，頁內採用邏輯日誌，undo log採用的是邏輯日誌），目的是保證資料的一致性。 binlog記錄的都是事務操作內容，例如一條語句DELETE FROM TABLE WHERE i > 1之類的，不管採用的是什麼引擎，當然格式是二進位的，要解析日誌內容可以用這個指令mysqlbinlog -vv BINLOG。
c）記錄時機不同。 redo/undo日誌在事務執行過程中會不斷的寫入;而binlog僅在事務提交後才寫入到日誌，之前描述有誤，binlog是在事務最終commit前寫入的，多謝anti-semicolon 指出。當然，binlog什麼時候刷新到磁碟跟參數sync_binlog相關。
顯然，我們執行SELECT等不涉及資料更新的語句是不會記binlog的，而涉及到資料更新則會記錄。要注意的是，對支援事務的引擎如innodb而言，必須要提交了事務才會記錄binlog。
binlog刷新到磁碟的時機跟sync_binlog參數相關，如果設定為0，表示MySQL不控制binlog的刷新，由檔案系統去控制它快取的刷新，而如果設定為不為0的值則表示每sync_binlog次事務，MySQL呼叫檔案系統的刷新操作刷新binlog到磁碟中。設為1是最安全的，在系統故障時最多丟失一個事務的更新，但是會對性能有所影響，一般情況下會設置為100或者0，犧牲一定的一致性來獲取更好的性能。
透過指令SHOW MASTER LOGS可以看到目前的binlog數目。如下面就是我機器上的mysql的binlog情況，第一列是binlog檔名，第二列是binlog檔大小。可以透過設定expire_logs_days來指定binlog保留時間，要手動清理binlog可以透過指定binlog名字或指定保留的日期，指令分別是:purge master logs to BINLOGNAME;和purge master logs before DATE;。
......
| mysql-bin.000018 | 515 |
| mysql-bin.000019 | 504 |
| mysql-bin.000020 | 107 |
+------------------+-----------+
登入後複製
2 binlog格式解析
2.1 binlog檔案格式簡介
binlog格式分為statement，row以及mixed三種，mysql5.5預設的還是statement模式，當然我們在主從同步中一般是不建議用statement模式的，因為會有些語句不支持，比如語句中包含UUID函數，以及LOAD DATA IN FILE語句等，一般推薦的是mixed格式。暫且不管這三種格式的差別，看看binlog的儲存格式是什麼樣的。 binlog是二進位檔案集合，當然除了我們看到的mysql-bin.xxxxxx這些binlog檔外，還有個binlog索引檔mysql-bin.index。如官方文件中所寫，binlog格式如下：
binlog檔案以一個值為0Xfe62696e的魔數開頭，這個魔數對應0xfe 'b''i''n'。
binlog由一系列的binlog event構成。每個binlog event包含header和data兩部分。
从mysql5.0版本开始，binlog采用的是v4版本，第一个event都是format_desc event 用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式。关于之前版本的binlog格式，可以参见http://dev.mysql.com/doc/internals/en/binary-log-versions.html
接下来的event就是按照上面的格式版本写入的event。
最后一个rotate event用于说明下一个binlog文件。
binlog索引文件是一个文本文件，其中内容为当前的binlog文件列表。比如下面就是一个mysql-bin.index文件的内容。
/var/log/mysql/mysql-bin.000019
/var/log/mysql/mysql-bin.000020
/var/log/mysql/mysql-bin.000021
登入後複製
接下来分析下几种常见的event，其他的event类型可以参见官方文档。event数据结构如下：
+=====================================+
| event | timestamp 0 : 4 |
| header +----------------------------+
| | type_code 4 : 1 |
| +----------------------------+
| | server_id 5 : 4 |
| +----------------------------+
| | event_length 9 : 4 |
| +----------------------------+
| | next_position 13 : 4 |
| +----------------------------+
| | flags 17 : 2 |
| +----------------------------+
| | extra_headers 19 : x-19 |
+=====================================+
| event | fixed part x : y |
| data +----------------------------+
| | variable part |
+=====================================+
登入後複製
2.2 format_desc event
下面是我在FLUSH LOGS之后新建的一个全新的binlog文件mysql-bin.000053，从binlog第一个event也就是format_desc event开始分析（mysql日志是小端字节序）：
root@ubuntu:/var/log/mysql# hexdump -C mysql-bin.000053
00000000 fe 62 69 6e b8 b2 7f 56 0f 04 00 00 00 67 00 00 |.bin...V.....g..|
00000010 00 6b 00 00 00 01 00 04 00 35 2e 35 2e 34 36 2d |.k.......5.5.46-|
00000020 30 75 62 75 6e 74 75 30 2e 31 34 2e 30 34 2e 32 |0ubuntu0.14.04.2|
00000030 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 |-log............|
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 54 00 |8.............T.|
00000060 04 1a 08 00 00 00 08 08 08 02 00 |...........|
登入後複製
对照官方文档中的说明来看下format_desc event格式：
+=====================================+
| event | timestamp 0 : 4 |
| header +----------------------------+
| | type_code 4 : 1 | = FORMAT_DESCRIPTION_EVENT = 15
| +----------------------------+
| | 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 |
+=====================================+
登入後複製
前面4个字节是固定的magic number,值为0x6e6962fe。接着是一个format_desc event，先看下19个字节的header。这19个字节中前4个字节0x567fb2b8是时间戳，第5个字节0x0f是event type，接着4个字节0x00000004是server_id，再接着4个字节0x00000067是长度103，然后的4个字节0x0000006b是下一个event的起始位置107，接着的2个字节的0x0001是flag（1为LOG_EVENT_BINLOG_IN_USE_F，标识binlog还没有关闭，binlog关闭后，flag会被设置为0），这样4+1+4+4+4+2=19个字节的公共头就完了(extra_headers暂时没有用到)。然后是这个event的data部分，event的data分为Fixed data和Variable data两部分，其中Fixed data是event的固定长度和格式的数据，Variable data则是长度变化的数据，比如format_desc event的Fixed data长度是0x54=84个字节。下面看下这84=2+50+4+1+27个字节的分配：开始的2个字节0x0004为binlog的版本号4，接着的50个字节为mysql-server版本，如我的版本是5.5.46-0ubuntu0.14.04.2-log，与SELECT version();查看的结果一致。接下来4个字节是binlog创建时间，这里是0；然后的1个字节0x13是指之后所有event的公共头长度，这里都是19；接着的27个字节中每个字节为mysql已知的event（共27个）的Fixed data的长度；可以发现format_desc event自身的Variable data部分为空。
2.3 rotate event
接着我们不做额外操作，直接FLUSH LOGS，可以看到一个rotate event，此时的binlog内容如下:
......
00000060 ................................. c2 b3 7f 56 04 |..............V.|
00000070 04 00 00 00 2b 00 00 00 96 00 00 00 00 00 04 00 |....+...........|
00000080 00 00 00 00 00 00 6d 79 73 71 6c 2d 62 69 6e 2e |......mysql-bin.|
00000090 30 30 30 30 35 34 |000054|
00000096
登入後複製
前面的内容跟之前的几乎一致，除了format_desc event的flag从0x0001变成了0x0000。然后从0x567fb3c2开始是一个rotate event。依照前面的分析，前面19个字节为event的header，其event type是0x04，长度为0x2b=43，下一个event起始位置为0x96=150，然后是flag为0x0000，接着是event data部分，首先的8个字节为Fixed data部分，记录的是下一个binlog的位置偏移4，而余下来的43-19-8=16个字节为Variable data部分，记录的是下一个binlog的文件名mysql-bin.000054。对照mysqlbinlog -vv mysql-bin.000053可以验证。
ssj@ubuntu:/var/log/mysql$ mysqlbinlog -vv mysql-bin.000053
...
# at 4
#151227 17:43:20 server id 4 end_log_pos 107 Start: binlog v 4, server v 5.5.46-0ubuntu0.14.04.2-log created 151227 17:43:20
BINLOG '
uLJ/Vg8EAAAAZwAAAGsAAAAAAAQANS41LjQ2LTB1YnVudHUwLjE0LjA0LjItbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#151227 17:47:46 server id 4 end_log_pos 150 Rotate to mysql-bin.000054 pos: 4
...
登入後複製
2.4 query event
刷新binlog，设置binlog_format=statement，创建一个表CREATE TABLEtt(ivarchar(100) DEFAULT NULL) ENGINE=InnoDB, 然后在测试表tt中插入一条数据insert into tt values('abc')，会产生3个event，包括2个query event和1个xid event。其中2个query event分别是BEGIN以及INSERT 语句，而xid event则是事务提交语句（xid event是支持XA的存储引擎才有的，因为测试表tt是innodb引擎的，所以会有。如果是myisam引擎的表，也会有BEGIN和COMMIT,只不过COMMIT会是一个query event而不是xid event）。
mysql> show binlog events in 'mysql-bin.000060';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000060 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000060 | 107 | Query | 4 | 175 | BEGIN |
| mysql-bin.000060 | 175 | Query | 4 | 266 | use `test`; insert into tt values('abc') |
| mysql-bin.000060 | 266 | Xid | 4 | 293 | COMMIT /* xid=138 */ |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------
登入後複製
binlog如下：
.......
0000006b 01 9d 82 56 02 04 00 00 00 44 00 00 00 af 00 00 |...V.....D......|
0000007b 00 08 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............|
0000008b 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................|
0000009b 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 42 |std.!.!...test.B|
000000ab 45 47 49 4e |EGIN|
000000af 01 9d 82 56 02 04 00 00 00 5b 00 00 00 0a 01 00 |...V.....[......|
000000bf 00 00 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............|
000000cf 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................|
000000df 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 69 |std.!.!...test.i|
000000ef 6e 73 65 72 74 20 69 6e 74 6f 20 74 74 20 76 61 |nsert into tt va|
000000ff 6c 75 65 73 28 27 61 62 63 27 29 |lues('abc')|
0000010a 01 9d 82 56 10 04 00 00 00 1b 00 00 00 25 01 00 |...V.........%..|
0000011a 00 00 00 8a 00 00 00 00 00 00 00 |...........|
登入後複製
抛开format_desc event，从0000006b开始分析第一个query event。头部跟之前的event一样，只是query event的type为0x02，长度为0x44=64，下一个event位置为0xaf=175。flag为8，接着是data部分，从format_desc event我们可以知道query event的Fixed data部分为13个字节，因此也可以算出Variable data部分为64-19-13=32字节。
Fixed data：首先的4个字节0x00000026为执行该语句的thread id，接下来的4个字节是执行的时间0(以秒为单位)，接下来的1个字节0x04是语句执行时的默认数据库名字的长度，我这里数据库是test，所以长度为4.接着的2个字节0x0000是错误码（注：通常情况下错误码是0表示没有错误，但是在一些非事务性表如myisam表执行INSERT...SELECT语句时可能插入部分数据后遇到duplicate-key错误会产生错误码1062，或者是事务性表在INSERT...SELECT出错不会插入部分数据，但是在执行过程中CTRL+C终止语句也可能记录错误码。slave db在复制时会执行后检查错误码是否一致，如果不一致，则复制过程会中止）,接着2个字节0x001a为状态变量块的长度26。
Variable data：从0x001a之后的26个字节为状态变量块（这个暂时先不管），然后是默认数据库名test，以0x00结尾，然后是sql语句BEGIN，接下来就是第2个query event的内容了。
第二个query event与第一个格式一样，只是执行语句变成了insert into tt values('abc')。
第三个xid event为COMMIT语句。前19个字节是通用头部，type是16。data部分中Fixed data为空，而variable data为8个字节，这8个字节0x000000008a是事务编号（注意事务编号不一定是小端字节序，因为是从内存中拷贝到磁盘的，所以这个字节序跟机器相关）。
2.5 table_map event & write_rows event
这两个event是在binlog_format=row的时候使用，设置binlog_format=row，然后创建一个测试表
CREATE TABLE `trow` (
`i` int(11) NOT NULL,
`c` varchar(10) DEFAULT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1`
登入後複製
执行语句INSERT INTO trow VALUES(1, NULL), (2, 'a')，这个语句会产生一个query event，一个table_map event、一个write_rows event以及一个xid event。
mysql> show binlog events in 'mysql-bin.000074';
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000074 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000074 | 107 | Query | 4 | 175 | BEGIN |
| mysql-bin.000074 | 175 | Table_map | 4 | 221 | table_id: 50 (test.trow) |
| mysql-bin.000074 | 221 | Write_rows | 4 | 262 | table_id: 50 flags: STMT_END_F |
| mysql-bin.000074 | 262 | Xid | 4 | 289 | COMMIT /* xid=245 */
登入後複製
对应的mysql-bin.000074数据如下：
...
#query event (BEGIN)
0000006b 95 2a 85 56 02 04 00 00 00 44 00 00 00 af 00 00 |.*.V.....D......|
0000007b 00 08 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............|
0000008b 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................|
0000009b 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 42 |std.!.!...test.B|
000000ab 45 47 49 4e |EGIN|
#table_map event
000000af 95 2a 85 56 13 04 00 00 00 2e 00 00 00 dd 00 00 |.*.V............|
000000bf 00 00 00 32 00 00 00 00 00 01 00 04 74 65 73 74 |...2........test|
000000cf 00 04 74 72 6f 77 00 02 03 0f 02 0a 00 02 |..trow........|
#write_rows event
000000dd 95 2a 85 56 17 04 00 00 00 29 00 00 00 06 01 00 |.*.V.....)......|
000000ed 00 00 00 32 00 00 00 00 00 01 00 02 ff fe 01 00 |...2............|
000000fd 00 00 fc 02 00 00 00 01 61 |........a|
#xid event
00000106 95 2a 85 56 10 04 00 00 00 1b 00 00 00 21 01 00 |.*.V.........!..|
00000116 00 00 00 f5 00 00 00 00 00 00 00 |...........|
登入後複製
0x0000006b-0x000000ae为query event，语句是BEGIN,前面已经分析过。
table_map event
0x0000000af开始为table_map event。除去头部19个字节，Fixed data为8个字节，前面6个字节0x32=50为table id，接着2个字节0x0001为flags。
Variable data部分，首先1个字节0x04为数据库名test的长度，然后5个字节是数据库名test+结束符。接着1个字节0x04为表名长度，接着5个字节为表名trow+结束符。接着1个字节0x02为列的数目。而后是2个列的类型定义，分别是0x03和0x0f（列的类型MYSQL_TYPE_LONG为0x03，MYSQL_TYPE_VARCHAR为0x0f)。接着是列的元数据定义，首先0x02表示元数据长度为2，因为MYSQL_TYPE_LONG没有元数据，而MYSQL_TYPE_VARCHAR元数据长度为2。接着的0x000a就是MYSQL_TYPE_VARCHAR的元数据，表示我们在定义表时的varchar字段c长度为10，最后一个字节0x02为掩码，表示第一个字段i不能为NULL。关于列的类型以及元数据等更详细的信息可以参见http://dev.mysql.com/doc/internals/en/table-map-event.html。
write_rows event
从0x000000dd开始为write_rows event，除去头部19个字节，前6个字节0x32也是table id，然后两个字节0x0001为flags。接着的1个字节0x02为表中列的数目。然后1个字节0xff各个bit标识各列是否存在值，这里表示都存在。
接着的就是插入的各行数据了。第1个字节0xfe的各个bit标识该行变化之后各列是否为NULL，为NULL记为1.这里表示第1列不为NULL，因为第一行数据插入的是(1,NULL)。接下来是各列的数据，第一列是MYSQL_TYPE_LONG,长度为4个字节，所以0x00000001就是这个值。第二列是NULL不占字节。接下来是第二行，先是0xfc标识两列都不为NULL，先读取第一列的4个字节0x00000002也就是我们插入的数字2，然后读取第二列，先是一个字节的长度0x01，然后是内容0x61也就是字符'a'。到此，write_rows event也就分析完了。rows相关的event还有update_rows event和delete_rows event等，欲了解更多可以参见官方文档。
最后是xid event，之前已经分析过，不再赘述。
2.6 intvar event
intvar event在binlog_format=statement时使用到，用于自增键类型auto_increment，十分重要。intval event的Fixed data部分为空，而Variable data部分为9个字节，第1个字节用于标识自增事件类型 LAST_INSERT_ID_EVENT = 1 or INSERT_ID_EVENT = 2，余下的8个字节为自增ID。
创建一个测试表 create table tinc (i int auto_increment primary key, c varchar(10)) engine=innodb;，然后执行一个插入语句INSERT INTO tinc(c) values('abc');就可以看到intvar event了，这里的自增事件类型为INSERT_ID_EVENT。而如果用语句INSERT INTO tinc(i, c) VALUES(LAST_INSERT_ID()+1, 'abc')，则可以看到自增事件类型为LAST_INSERT_ID_EVENT的intvar event。
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------
| mysql-bin.000079 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000079 | 107 | Query | 4 | 175 | BEGIN |
| mysql-bin.000079 | 175 | Intvar | 4 | 203 | INSERT_ID=1 |
| mysql-bin.000079 | 203 | Query | 4 | 299 | use `test`; insert into tinc(c) values('abc') |
| mysql-bin.000079 | 299 | Xid | 4 | 326 | COMMIT /* xid=263 */
登入後複製
3 简单总结
上面提到，binlog有三种格式，各有优缺点：
statement：基于SQL语句的模式，某些语句和函数如UUID, LOAD DATA INFILE等在复制过程可能导致数据不一致甚至出错。
row：基于行的模式，记录的是行的变化，很安全。但是binlog会比其他两种模式大很多，在一些大表中清除大量数据时在binlog中会生成很多条语句，可能导致从库延迟变大。
mixed：混合模式，根据语句来选用是statement还是row模式。
不同版本的mysql在主从复制要慎重，虽然mysql5.0之后都用的V4版本的binlog了，估计还是会有些坑在里面，特别是高版本为主库，低版本为从库时容易出问题。在主从复制时最好还是主库从库版本一致，至少是大版本一致。mysql复制是个大的话题，希望有时间能单独总结一篇笔记。
【相关推荐】
1. 免费mysql在线视频教程
2. MySQL最新手册教程
3. 布尔教育燕十八mysql入门视频教程
以上是什麼是MySQL binlog？ MySQL binlog的用途及格式解析的詳細內容。更多資訊請關注PHP中文網其他相關文章！
