mysql binlog event格式是什么样的
mysql binlog event格式是什么样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
首先发起几个事务，生成一个新的binlog方便分析。
|
flush logs; insert into t4 values(7,7,7); update t4 set a=8 where a=7; delete from t4 where a=8; alter table t4 add key idx_t4_c(c); flush logs; |
每一个event都分为三个部分组成。event head,event data,event footer。
Event header：
Timestamp |
4字节 |
这个值是1970年1月日0时0分0秒以来的秒数，该值有一个很大的作用是从库计算Seconds_Behind_Master关键值。 |
Type_code |
1字节 |
Event事件编码。 |
Server_id |
4字节 |
生成event的服务器的server_id。即使从库开启了log_slave_updates参数，从库将此event写入binlog时，记录的也是主库的server_id。 |
Event_len |
4字节 |
Event的长度 |
End_log_p |
4字节 |
下一个event的起始位置 |
Flags |
2字节 |
Event footer：包括一个4字节的crc，标识此event的完整性。
查看生成的mysql-bin.000050的event：
mysql> show binlog events in 'mysql-bin.000050'; +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+ | mysql-bin.000050 | 4 | Format_desc | 1051295 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 | | mysql-bin.000050 | 123 | Previous_gtids | 1051295 | 194 | 4c312339-ab38-11e9-86a8-000c29050245:2-90058 | | mysql-bin.000050 | 194 | Gtid | 1051295 | 259 | SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90059' | | mysql-bin.000050 | 259 | Query | 1051295 | 331 | BEGIN | | mysql-bin.000050 | 331 | Table_map | 1051295 | 378 | table_id: 110 (ming.t4) | | mysql-bin.000050 | 378 | Write_rows | 1051295 | 426 | table_id: 110 flags: STMT_END_F | | mysql-bin.000050 | 426 | Xid | 1051295 | 457 | COMMIT /* xid=30 */ | | mysql-bin.000050 | 457 | Gtid | 1051295 | 522 | SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90060' | | mysql-bin.000050 | 522 | Query | 1051295 | 594 | BEGIN | | mysql-bin.000050 | 594 | Table_map | 1051295 | 641 | table_id: 110 (ming.t4) | | mysql-bin.000050 | 641 | Update_rows | 1051295 | 703 | table_id: 110 flags: STMT_END_F | | mysql-bin.000050 | 703 | Xid | 1051295 | 734 | COMMIT /* xid=31 */ | | mysql-bin.000050 | 734 | Gtid | 1051295 | 799 | SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90061' | | mysql-bin.000050 | 799 | Query | 1051295 | 871 | BEGIN | | mysql-bin.000050 | 871 | Table_map | 1051295 | 918 | table_id: 110 (ming.t4) | | mysql-bin.000050 | 918 | Delete_rows | 1051295 | 966 | table_id: 110 flags: STMT_END_F | | mysql-bin.000050 | 966 | Xid | 1051295 | 997 | COMMIT /* xid=32 */ | | mysql-bin.000050 | 997 | Gtid | 1051295 | 1062 | SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90062' | | mysql-bin.000050 | 1062 | Query | 1051295 | 1170 | use `ming`; alter table t4 add key idx_t4_c(c) | | mysql-bin.000050 | 1170 | Rotate | 1051295 | 1217 | mysql-bin.000051;pos=4 | +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
20 rows in set (0.00 sec)
用mysqlbinlog --hexdump分析下日志
[root@cdbtest1 binlog]# mysqlbinlog --hexdump mysql-bin.000050|more /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200209 19:44:20 server id 1051295 end_log_pos 123 CRC32 0x3e1b7c96 # Position Timestamp Type Master ID Size Master Pos Flags # 4 94 f0 3f 5e 0f 9f 0a 10 00 77 00 00 00 7b 00 00 00 00 00 # 17 04 00 35 2e 37 2e 32 34 2d 6c 6f 67 00 00 00 00 |..5.7.24.log....| # 27 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| # 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| # 47 00 00 00 00 00 00 00 00 13 38 0d 00 08 00 12 00 |.........8......| # 57 04 04 04 04 12 00 00 5f 00 04 1a 08 00 00 00 08 |................| # 67 08 08 02 00 00 00 0a 0a 0a 2a 2a 00 12 34 00 01 |.............4..| # 77 96 7c 1b 3e |....| # Start: binlog v 4, server v 5.7.24-log created 200209 19:44:20
Timestamp:实际上是16进制的'0x5e3ff094',换算为10进制就是1581248660： [root@cdbtest1 ~]# echo $((0x5e3ff094)) 1581248660 转换为时间，也就是日志解析出来的200209 19:44:20 [root@cdbtest1 ~]# date -d @1581248660 2020年 02月 09日 星期日 19:44:20 CST Type：0f，也就是10进制的15.Format_desc=15，该event记录了binary log的版本信息，在每一个binary log开头。 Master ID ：16进制的'0x00100a9f',也就是10进制的1051295，正是server_id Size:16进制的77，是10进制的119，正好是下一个event的at 123到该event的at 4的差值。 Master Pos：下一个event开始的地址，16进制的7b，等于10进制的123. 根据show binlog events in 'mysql-bin.000050'结果，可以得到如下TYPE对应关系： Format_desc=15：该event记录了binary log的版本信息 Previous_gtids=35：该event说明之前binary log包含的gtid set。如果是在relay log中，则记录IO线程收到的gtid set。 Gtid=33：gtid信息 Query=2：语句模式下记录实际的语句。 Table_map=19，table_id和具体表名的映射。比如：Table_map: `ming`.`t4` mapped to number 110 Write_rows=30：insert语句生成的event Xid=16：事务结束时添加一个xid信息作为结束的标志。 Update_rows=31：update语句生成的event Delete_rows=32：delete语句生成的event Rotate=4：binlog发生主动或者被动切换，这时会在当前binlog文件末尾添加一个ROTATE_EVENT事件。
下面是剩余的binary log，供分析查看：
BINLOG ' lPA/Xg+fChAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AZZ8Gz4= '/*!*/; # at 123 #200209 19:44:20 server id 1051295 end_log_pos 194 CRC32 0x5b05428a # Position Timestamp Type Master ID Size Master Pos Flags # 7b 94 f0 3f 5e 23 9f 0a 10 00 47 00 00 00 c2 00 00 00 80 00 # 8e 01 00 00 00 00 00 00 00 4c 31 23 39 ab 38 11 e9 |........L1.9.8..| # 9e 86 a8 00 0c 29 05 02 45 01 00 00 00 00 00 00 00 |.......E........| # ae 02 00 00 00 00 00 00 00 cb 5f 01 00 00 00 00 00 |................| # be 8a 42 05 5b |.B..| # Previous-GTIDs # 4c312339-ab38-11e9-86a8-000c29050245:2-90058 # at 194 #200209 19:44:20 server id 1051295 end_log_pos 259 CRC32 0xf1bb8ad0 # Position Timestamp Type Master ID Size Master Pos Flags # c2 94 f0 3f 5e 21 9f 0a 10 00 41 00 00 00 03 01 00 00 00 00 # d5 00 4c 31 23 39 ab 38 11 e9 86 a8 00 0c 29 05 02 |.L1.9.8.........| # e5 45 cb 5f 01 00 00 00 00 00 02 00 00 00 00 00 00 |E...............| # f5 00 00 01 00 00 00 00 00 00 00 d0 8a bb f1 |..............| # GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90059'/*!*/; # at 259 #200209 19:44:20 server id 1051295 end_log_pos 331 CRC32 0x9f31253b # Position Timestamp Type Master ID Size Master Pos Flags # 103 94 f0 3f 5e 02 9f 0a 10 00 48 00 00 00 4b 01 00 00 08 00 # 116 03 00 00 00 00 00 00 00 04 00 00 1a 00 00 00 00 |................| # 126 00 00 01 00 00 00 10 00 00 00 00 06 03 73 74 64 |.............std| # 136 04 21 00 21 00 21 00 6d 69 6e 67 00 42 45 47 49 |.......ming.BEGI| # 146 4e 3b 25 31 9f |N..1.| # Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1581248660/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=268435456/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 331 #200209 19:44:20 server id 1051295 end_log_pos 378 CRC32 0x387080d0 # Position Timestamp Type Master ID Size Master Pos Flags # 14b 94 f0 3f 5e 13 9f 0a 10 00 2f 00 00 00 7a 01 00 00 00 00 # 15e 6e 00 00 00 00 00 01 00 04 6d 69 6e 67 00 02 74 |n........ming..t| # 16e 34 00 03 03 03 03 00 04 d0 80 70 38 |4.........p8| # Table_map: `ming`.`t4` mapped to number 110 # at 378 #200209 19:44:20 server id 1051295 end_log_pos 426 CRC32 0x60bed8e5 # Position Timestamp Type Master ID Size Master Pos Flags # 17a 94 f0 3f 5e 1e 9f 0a 10 00 30 00 00 00 aa 01 00 00 00 00 # 18d 6e 00 00 00 00 00 01 00 02 00 03 ff f8 07 00 00 |n...............| # 19d 00 07 00 00 00 07 00 00 00 e5 d8 be 60 |.............| # Write_rows: table id 110 flags: STMT_END_F BINLOG ' lPA/XhOfChAALwAAAHoBAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABNCAcDg= lPA/Xh7fChAAMAAAAKoBAAAAAG4AAAAAAAEAAgAD//gHAAAABwAAAAcAAADl2L5g '/*!*/; # at 426 #200209 19:44:20 server id 1051295 end_log_pos 457 CRC32 0x23e1b8fd # Position Timestamp Type Master ID Size Master Pos Flags # 1aa 94 f0 3f 5e 10 9f 0a 10 00 1f 00 00 00 c9 01 00 00 00 00 # 1bd 1e 00 00 00 00 00 00 00 fd b8 e1 23 |............| # Xid = 30 COMMIT/*!*/; # at 457 #200209 19:44:20 server id 1051295 end_log_pos 522 CRC32 0xafc871d8 # Position Timestamp Type Master ID Size Master Pos Flags # 1c9 94 f0 3f 5e 21 9f 0a 10 00 41 00 00 00 0a 02 00 00 00 00 # 1dc 00 4c 31 23 39 ab 38 11 e9 86 a8 00 0c 29 05 02 |.L1.9.8.........| # 1ec 45 cc 5f 01 00 00 00 00 00 02 01 00 00 00 00 00 |E...............| # 1fc 00 00 02 00 00 00 00 00 00 00 d8 71 c8 af |...........q..| # GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90060'/*!*/; # at 522 #200209 19:44:20 server id 1051295 end_log_pos 594 CRC32 0x3918c9f3 # Position Timestamp Type Master ID Size Master Pos Flags # 20a 94 f0 3f 5e 02 9f 0a 10 00 48 00 00 00 52 02 00 00 08 00 # 21d 03 00 00 00 00 00 00 00 04 00 00 1a 00 00 00 00 |................| # 22d 00 00 01 00 00 00 10 00 00 00 00 06 03 73 74 64 |.............std| # 23d 04 21 00 21 00 21 00 6d 69 6e 67 00 42 45 47 49 |.......ming.BEGI| # 24d 4e f3 c9 18 39 |N...9| # Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1581248660/*!*/; BEGIN /*!*/; # at 594 #200209 19:44:20 server id 1051295 end_log_pos 641 CRC32 0x59d98e78 # Position Timestamp Type Master ID Size Master Pos Flags # 252 94 f0 3f 5e 13 9f 0a 10 00 2f 00 00 00 81 02 00 00 00 00 # 265 6e 00 00 00 00 00 01 00 04 6d 69 6e 67 00 02 74 |n........ming..t| # 275 34 00 03 03 03 03 00 04 78 8e d9 59 |4.......x..Y| # Table_map: `ming`.`t4` mapped to number 110 # at 641 #200209 19:44:20 server id 1051295 end_log_pos 703 CRC32 0x68314371 # Position Timestamp Type Master ID Size Master Pos Flags # 281 94 f0 3f 5e 1f 9f 0a 10 00 3e 00 00 00 bf 02 00 00 00 00 # 294 6e 00 00 00 00 00 01 00 02 00 03 ff ff f8 07 00 |n...............| # 2a4 00 00 07 00 00 00 07 00 00 00 f8 08 00 00 00 07 |................| # 2b4 00 00 00 07 00 00 00 71 43 31 68 |.......qC1h| # Update_rows: table id 110 flags: STMT_END_F BINLOG ' lPA/XhOfChAALwAAAIECAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABHiO2Vk= lPA/Xh+fChAAPgAAAL8CAAAAAG4AAAAAAAEAAgAD///4BwAAAAcAAAAHAAAA+AgAAAAHAAAABwAA AHFDMWg= '/*!*/; # at 703 #200209 19:44:20 server id 1051295 end_log_pos 734 CRC32 0x51fe4201 # Position Timestamp Type Master ID Size Master Pos Flags # 2bf 94 f0 3f 5e 10 9f 0a 10 00 1f 00 00 00 de 02 00 00 00 00 # 2d2 1f 00 00 00 00 00 00 00 01 42 fe 51 |.........B.Q| # Xid = 31 COMMIT/*!*/; # at 734 #200209 19:44:20 server id 1051295 end_log_pos 799 CRC32 0x20d2c031 # Position Timestamp Type Master ID Size Master Pos Flags # 2de 94 f0 3f 5e 21 9f 0a 10 00 41 00 00 00 1f 03 00 00 00 00 # 2f1 00 4c 31 23 39 ab 38 11 e9 86 a8 00 0c 29 05 02 |.L1.9.8.........| # 301 45 cd 5f 01 00 00 00 00 00 02 02 00 00 00 00 00 |E...............| # 311 00 00 03 00 00 00 00 00 00 00 31 c0 d2 20 |..........1...| # GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90061'/*!*/; # at 799 #200209 19:44:20 server id 1051295 end_log_pos 871 CRC32 0x44485350 # Position Timestamp Type Master ID Size Master Pos Flags # 31f 94 f0 3f 5e 02 9f 0a 10 00 48 00 00 00 67 03 00 00 08 00 # 332 03 00 00 00 00 00 00 00 04 00 00 1a 00 00 00 00 |................| # 342 00 00 01 00 00 00 10 00 00 00 00 06 03 73 74 64 |.............std| # 352 04 21 00 21 00 21 00 6d 69 6e 67 00 42 45 47 49 |.......ming.BEGI| # 362 4e 50 53 48 44 |NPSHD| # Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1581248660/*!*/; BEGIN /*!*/; # at 871 #200209 19:44:20 server id 1051295 end_log_pos 918 CRC32 0xff10c266 # Position Timestamp Type Master ID Size Master Pos Flags # 367 94 f0 3f 5e 13 9f 0a 10 00 2f 00 00 00 96 03 00 00 00 00 # 37a 6e 00 00 00 00 00 01 00 04 6d 69 6e 67 00 02 74 |n........ming..t| # 38a 34 00 03 03 03 03 00 04 66 c2 10 ff |4.......f...| # Table_map: `ming`.`t4` mapped to number 110 # at 918 #200209 19:44:20 server id 1051295 end_log_pos 966 CRC32 0xc78da247 # Position Timestamp Type Master ID Size Master Pos Flags # 396 94 f0 3f 5e 20 9f 0a 10 00 30 00 00 00 c6 03 00 00 00 00 # 3a9 6e 00 00 00 00 00 01 00 02 00 03 ff f8 08 00 00 |n...............| # 3b9 00 07 00 00 00 07 00 00 00 47 a2 8d c7 |.........G...| # Delete_rows: table id 110 flags: STMT_END_F BINLOG ' lPA/XhOfChAALwAAAJYDAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABGbCEP8= lPA/XiCfChAAMAAAAMYDAAAAAG4AAAAAAAEAAgAD//gIAAAABwAAAAcAAABHoo3H '/*!*/; # at 966 #200209 19:44:20 server id 1051295 end_log_pos 997 CRC32 0x346ba50c # Position Timestamp Type Master ID Size Master Pos Flags # 3c6 94 f0 3f 5e 10 9f 0a 10 00 1f 00 00 00 e5 03 00 00 00 00 # 3d9 20 00 00 00 00 00 00 00 0c a5 6b 34 |..........k4| # Xid = 32 COMMIT/*!*/; # at 997 #200209 19:44:20 server id 1051295 end_log_pos 1062 CRC32 0x68ff93c5 # Position Timestamp Type Master ID Size Master Pos Flags # 3e5 94 f0 3f 5e 21 9f 0a 10 00 41 00 00 00 26 04 00 00 00 00 # 3f8 01 4c 31 23 39 ab 38 11 e9 86 a8 00 0c 29 05 02 |.L1.9.8.........| # 408 45 ce 5f 01 00 00 00 00 00 02 03 00 00 00 00 00 |E...............| # 418 00 00 04 00 00 00 00 00 00 00 c5 93 ff 68 |.............h| # GTID last_committed=3 sequence_number=4 rbr_only=no SET @@SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245:90062'/*!*/; # at 1062 #200209 19:44:20 server id 1051295 end_log_pos 1170 CRC32 0x80305685 # Position Timestamp Type Master ID Size Master Pos Flags # 426 94 f0 3f 5e 02 9f 0a 10 00 6c 00 00 00 92 04 00 00 00 00 # 439 03 00 00 00 00 00 00 00 04 00 00 21 00 00 00 00 |................| # 449 00 00 01 00 00 00 10 00 00 00 00 06 03 73 74 64 |.............std| # 459 04 21 00 21 00 21 00 0c 01 6d 69 6e 67 00 6d 69 |.........ming.mi| # 469 6e 67 00 61 6c 74 65 72 20 74 61 62 6c 65 20 74 |ng.alter.table.t| # 479 34 20 61 64 64 20 6b 65 79 20 69 64 78 5f 74 34 |4.add.key.idx.t4| # 489 5f 63 28 63 29 85 56 30 80 |.c.c..V0.| # Query thread_id=3 exec_time=0 error_code=0 use `ming`/*!*/; SET TIMESTAMP=1581248660/*!*/; alter table t4 add key idx_t4_c(c) /*!*/; # at 1170 #200209 19:44:21 server id 1051295 end_log_pos 1217 CRC32 0x1862bca8 # Position Timestamp Type Master ID Size Master Pos Flags # 492 95 f0 3f 5e 04 9f 0a 10 00 2f 00 00 00 c1 04 00 00 00 00 # 4a5 04 00 00 00 00 00 00 00 6d 79 73 71 6c 2d 62 69 |........mysql.bi| # 4b5 6e 2e 30 30 30 30 35 31 a8 bc 62 18 |n.000051..b.| # Rotate to mysql-bin.000051 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对亿速云的支持。