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>showbinlogeventsin'mysql-bin.000050';+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+|mysql-bin.000050|4|Format_desc|1051295|123|Serverver:5.7.24-log,Binlogver: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:110flags: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:110flags: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:110flags: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`;altertablet4addkeyidx_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@cdbtest1binlog]#mysqlbinlog--hexdumpmysql-bin.000050|more/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#20020919:44:20serverid1051295end_log_pos123CRC320x3e1b7c96#PositionTimestampTypeMasterIDSizeMasterPosFlags#494f03f5e0f9f0a1000770000007b0000000000#170400352e372e32342d6c6f6700000000|..5.7.24.log....|#2700000000000000000000000000000000|................|#3700000000000000000000000000000000|................|#47000000000000000013380d0008001200|.........8......|#57040404041200005f00041a0800000008|................|#670808020000000a0a0a2a2a0012340001|.............4..|#77967c1b3e|....|#Start:binlogv4,serverv5.7.24-logcreated20020919:44:20
Timestamp:实际上是16进制的'0x5e3ff094',换算为10进制就是1581248660：[root@cdbtest1~]#echo$((0x5e3ff094))1581248660转换为时间，也就是日志解析出来的20020919:44:20[root@cdbtest1~]#date-d@15812486602020年02月09日星期日19:44:20CSTType：0f，也就是10进制的15.Format_desc=15，该event记录了binarylog的版本信息，在每一个binarylog开头。MasterID：16进制的'0x00100a9f',也就是10进制的1051295，正是server_idSize:16进制的77，是10进制的119，正好是下一个event的at123到该event的at4的差值。MasterPos：下一个event开始的地址，16进制的7b，等于10进制的123.根据showbinlogeventsin'mysql-bin.000050'结果，可以得到如下TYPE对应关系：Format_desc=15：该event记录了binarylog的版本信息Previous_gtids=35：该event说明之前binarylog包含的gtidset。如果是在relaylog中，则记录IO线程收到的gtidset。Gtid=33：gtid信息Query=2：语句模式下记录实际的语句。Table_map=19，table_id和具体表名的映射。比如：Table_map:`ming`.`t4`mappedtonumber110Write_rows=30：insert语句生成的eventXid=16：事务结束时添加一个xid信息作为结束的标志。Update_rows=31：update语句生成的eventDelete_rows=32：delete语句生成的eventRotate=4：binlog发生主动或者被动切换，这时会在当前binlog文件末尾添加一个ROTATE_EVENT事件。
下面是剩余的binary log，供分析查看：
BINLOG'lPA/Xg+fChAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAZZ8Gz4='/*!*/;#at123#20020919:44:20serverid1051295end_log_pos194CRC320x5b05428a#PositionTimestampTypeMasterIDSizeMasterPosFlags#7b94f03f5e239f0a100047000000c20000008000#8e01000000000000004c312339ab3811e9|........L1.9.8..|#9e86a8000c290502450100000000000000|.......E........|#ae0200000000000000cb5f010000000000|................|#be8a42055b|.B..|#Previous-GTIDs#4c312339-ab38-11e9-86a8-000c29050245:2-90058#at194#20020919:44:20serverid1051295end_log_pos259CRC320xf1bb8ad0#PositionTimestampTypeMasterIDSizeMasterPosFlags#c294f03f5e219f0a100041000000030100000000#d5004c312339ab3811e986a8000c290502|.L1.9.8.........|#e545cb5f01000000000002000000000000|E...............|#f500000100000000000000d08abbf1|..............|#GTIDlast_committed=0sequence_number=1rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='4c312339-ab38-11e9-86a8-000c29050245:90059'/*!*/;#at259#20020919:44:20serverid1051295end_log_pos331CRC320x9f31253b#PositionTimestampTypeMasterIDSizeMasterPosFlags#10394f03f5e029f0a1000480000004b0100000800#11603000000000000000400001a00000000|................|#12600000100000010000000000603737464|.............std|#136042100210021006d696e670042454749|.......ming.BEGI|#1464e3b25319f|N..1.|#Querythread_id=3exec_time=0error_code=0SETTIMESTAMP=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/*!*/;/*!\Cutf8*//*!*/;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/*!*/;#at331#20020919:44:20serverid1051295end_log_pos378CRC320x387080d0#PositionTimestampTypeMasterIDSizeMasterPosFlags#14b94f03f5e139f0a10002f0000007a0100000000#15e6e00000000000100046d696e67000274|n........ming..t|#16e3400030303030004d0807038|4.........p8|#Table_map:`ming`.`t4`mappedtonumber110#at378#20020919:44:20serverid1051295end_log_pos426CRC320x60bed8e5#PositionTimestampTypeMasterIDSizeMasterPosFlags#17a94f03f5e1e9f0a100030000000aa0100000000#18d6e00000000000100020003fff8070000|n...............|#19d000700000007000000e5d8be60|.............|#Write_rows:tableid110flags:STMT_END_FBINLOG'lPA/XhOfChAALwAAAHoBAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABNCAcDg=lPA/Xh7fChAAMAAAAKoBAAAAAG4AAAAAAAEAAgAD//gHAAAABwAAAAcAAADl2L5g'/*!*/;#at426#20020919:44:20serverid1051295end_log_pos457CRC320x23e1b8fd#PositionTimestampTypeMasterIDSizeMasterPosFlags#1aa94f03f5e109f0a10001f000000c90100000000#1bd1e00000000000000fdb8e123|............|#Xid=30COMMIT/*!*/;#at457#20020919:44:20serverid1051295end_log_pos522CRC320xafc871d8#PositionTimestampTypeMasterIDSizeMasterPosFlags#1c994f03f5e219f0a1000410000000a0200000000#1dc004c312339ab3811e986a8000c290502|.L1.9.8.........|#1ec45cc5f01000000000002010000000000|E...............|#1fc00000200000000000000d871c8af|...........q..|#GTIDlast_committed=1sequence_number=2rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='4c312339-ab38-11e9-86a8-000c29050245:90060'/*!*/;#at522#20020919:44:20serverid1051295end_log_pos594CRC320x3918c9f3#PositionTimestampTypeMasterIDSizeMasterPosFlags#20a94f03f5e029f0a100048000000520200000800#21d03000000000000000400001a00000000|................|#22d00000100000010000000000603737464|.............std|#23d042100210021006d696e670042454749|.......ming.BEGI|#24d4ef3c91839|N...9|#Querythread_id=3exec_time=0error_code=0SETTIMESTAMP=1581248660/*!*/;BEGIN/*!*/;#at594#20020919:44:20serverid1051295end_log_pos641CRC320x59d98e78#PositionTimestampTypeMasterIDSizeMasterPosFlags#25294f03f5e139f0a10002f000000810200000000#2656e00000000000100046d696e67000274|n........ming..t|#2753400030303030004788ed959|4.......x..Y|#Table_map:`ming`.`t4`mappedtonumber110#at641#20020919:44:20serverid1051295end_log_pos703CRC320x68314371#PositionTimestampTypeMasterIDSizeMasterPosFlags#28194f03f5e1f9f0a10003e000000bf0200000000#2946e00000000000100020003fffff80700|n...............|#2a400000700000007000000f80800000007|................|#2b40000000700000071433168|.......qC1h|#Update_rows:tableid110flags:STMT_END_FBINLOG'lPA/XhOfChAALwAAAIECAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABHiO2Vk=lPA/Xh+fChAAPgAAAL8CAAAAAG4AAAAAAAEAAgAD///4BwAAAAcAAAAHAAAA+AgAAAAHAAAABwAAAHFDMWg='/*!*/;#at703#20020919:44:20serverid1051295end_log_pos734CRC320x51fe4201#PositionTimestampTypeMasterIDSizeMasterPosFlags#2bf94f03f5e109f0a10001f000000de0200000000#2d21f000000000000000142fe51|.........B.Q|#Xid=31COMMIT/*!*/;#at734#20020919:44:20serverid1051295end_log_pos799CRC320x20d2c031#PositionTimestampTypeMasterIDSizeMasterPosFlags#2de94f03f5e219f0a1000410000001f0300000000#2f1004c312339ab3811e986a8000c290502|.L1.9.8.........|#30145cd5f01000000000002020000000000|E...............|#3110000030000000000000031c0d220|..........1...|#GTIDlast_committed=2sequence_number=3rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='4c312339-ab38-11e9-86a8-000c29050245:90061'/*!*/;#at799#20020919:44:20serverid1051295end_log_pos871CRC320x44485350#PositionTimestampTypeMasterIDSizeMasterPosFlags#31f94f03f5e029f0a100048000000670300000800#33203000000000000000400001a00000000|................|#34200000100000010000000000603737464|.............std|#352042100210021006d696e670042454749|.......ming.BEGI|#3624e50534844|NPSHD|#Querythread_id=3exec_time=0error_code=0SETTIMESTAMP=1581248660/*!*/;BEGIN/*!*/;#at871#20020919:44:20serverid1051295end_log_pos918CRC320xff10c266#PositionTimestampTypeMasterIDSizeMasterPosFlags#36794f03f5e139f0a10002f000000960300000000#37a6e00000000000100046d696e67000274|n........ming..t|#38a340003030303000466c210ff|4.......f...|#Table_map:`ming`.`t4`mappedtonumber110#at918#20020919:44:20serverid1051295end_log_pos966CRC320xc78da247#PositionTimestampTypeMasterIDSizeMasterPosFlags#39694f03f5e209f0a100030000000c60300000000#3a96e00000000000100020003fff8080000|n...............|#3b900070000000700000047a28dc7|.........G...|#Delete_rows:tableid110flags:STMT_END_FBINLOG'lPA/XhOfChAALwAAAJYDAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABGbCEP8=lPA/XiCfChAAMAAAAMYDAAAAAG4AAAAAAAEAAgAD//gIAAAABwAAAAcAAABHoo3H'/*!*/;#at966#20020919:44:20serverid1051295end_log_pos997CRC320x346ba50c#PositionTimestampTypeMasterIDSizeMasterPosFlags#3c694f03f5e109f0a10001f000000e50300000000#3d920000000000000000ca56b34|..........k4|#Xid=32COMMIT/*!*/;#at997#20020919:44:20serverid1051295end_log_pos1062CRC320x68ff93c5#PositionTimestampTypeMasterIDSizeMasterPosFlags#3e594f03f5e219f0a100041000000260400000000#3f8014c312339ab3811e986a8000c290502|.L1.9.8.........|#40845ce5f01000000000002030000000000|E...............|#41800000400000000000000c593ff68|.............h|#GTIDlast_committed=3sequence_number=4rbr_only=noSET@@SESSION.GTID_NEXT='4c312339-ab38-11e9-86a8-000c29050245:90062'/*!*/;#at1062#20020919:44:20serverid1051295end_log_pos1170CRC320x80305685#PositionTimestampTypeMasterIDSizeMasterPosFlags#42694f03f5e029f0a10006c000000920400000000#43903000000000000000400002100000000|................|#44900000100000010000000000603737464|.............std|#459042100210021000c016d696e67006d69|.........ming.mi|#4696e6700616c746572207461626c652074|ng.alter.table.t|#4793420616464206b6579206964785f7434|4.add.key.idx.t4|#4895f6328632985563080|.c.c..V0.|#Querythread_id=3exec_time=0error_code=0use`ming`/*!*/;SETTIMESTAMP=1581248660/*!*/;altertablet4addkeyidx_t4_c(c)/*!*/;#at1170#20020919:44:21serverid1051295end_log_pos1217CRC320x1862bca8#PositionTimestampTypeMasterIDSizeMasterPosFlags#49295f03f5e049f0a10002f000000c10400000000#4a504000000000000006d7973716c2d6269|........mysql.bi|#4b56e2e303030303531a8bc6218|n.000051..b.|#Rotatetomysql-bin.000051pos:4SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
