mysql binlog event格式是什么样的
发表于：2024-11-24 作者：千家信息网编辑
千家信息网最后更新 2024年11月24日，mysql binlog event格式是什么样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。首先发起几个事务，生成一
千家信息网最后更新 2024年11月24日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 CSTType：0f，也就是10进制的15.Format_desc=15，该event记录了binary log的版本信息，在每一个binary log开头。Master ID ：16进制的'0x00100a9f',也就是10进制的1051295，正是server_idSize: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 110Write_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='/*!*/;# 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=0SET 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_FBINLOG '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 = 30COMMIT/*!*/;# 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=0SET 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_FBINLOG 'lPA/XhOfChAALwAAAIECAAAAAG4AAAAAAAEABG1pbmcAAnQ0AAMDAwMABHiO2Vk=lPA/Xh+fChAAPgAAAL8CAAAAAG4AAAAAAAEAAgAD///4BwAAAAcAAAAHAAAA+AgAAAAHAAAABwAAAHFDMWg='/*!*/;# 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 = 31COMMIT/*!*/;# 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=0SET 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_FBINLOG '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 = 32COMMIT/*!*/;# 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=noSET @@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=0use `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: 4SET @@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*/;
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。
进制
字节
生成
语句
信息
也就是
分析
事件
事务
实际
日志
版本
帮助
格式
很大
清楚
主动
三个
位置
作用
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
foreach两个数据库
服务器配件缺货
乐牌北京网络技术有限公司客服
关于网络安全法值得一提的事
目前网络安全常见的使用技术
基于web的数据库优点
学生成绩数据库英文翻译
负责测试软件开发
怎样进入用友数据库
正确打开数据库
数据库题库选择填空题
APP开发还是桌面软件开发
服务器u2卡是什么意思
东莞顶盛投资顾问软件开发公司
杭州数据库安全管理系统
网络安全凯撒密码计算
杨浦区营销软件开发售后服务
c 数据库事务处理
php判断数据库数据是否重复
井下供配电网络安全要求
数据存储服务器功能
软件开发二次加密
深圳中保科技互联网有限公司
江阴电商软件开发定制价格
郑州网络安全科技馆最厉害的展品
用友时空数据库连接失败
台州本地软件开发执行标准
嵌入软件开发工程师
数据库查询和表有什么区别
管理服务器的设备
