mysql binlog event格式是啥样的
发布时间：2022-01-13 14:52:28 所属栏目：MySql教程 来源：互联网
导读：mysql binlog event格式是什么样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 首先发起几个事务，生成一个新的binlog方便分析。 flush logs; insert into t4 values
|
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 2 （编辑：上海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
