MYSQL-- binlog事件详解
mysqlbinlog -vvv log.000001
# at 154 #160517 15:09:13 server id 100 end_log_pos 219 CRC32 0x7439a004 GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= '191f7a9f-ffa2-11e5-a825-00163e00242a:1'/*!*/; # at 219 #160517 15:09:13 server id 100 end_log_pos 291 CRC32 0x0839a8df Query thread_id=22 exec_time=0 error_code=0 SET TIMESTAMP=1463468953/*!*/; SET @@session.pseudo_thread_id=22/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; 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=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 291 #160517 15:09:13 server id 100 end_log_pos 336 CRC32 0x738c7c74 Table_map: `test`.`tt` mapped to number 113 # at 336 #160517 15:09:13 server id 100 end_log_pos 376 CRC32 0x1949e971 Write_rows: table id 113 flags: STMT_END_F BINLOG ' mcM6VxNkAAAALQAAAFABAAAAAHEAAAAAAAEABHRlc3QAAnR0AAEDAAF0fIxz mcM6Vx5kAAAAKAAAAHgBAAAAAHEAAAAAAAEAAgAB//4KAAAAcelJGQ== '/*!*/; ### INSERT INTO `test`.`tt` ### SET ### @1=10 /* INT meta=0 nullable=1 is_null=0 */ # at 376 #160517 15:09:13 server id 100 end_log_pos 407 CRC32 0x63b03fee Xid = 132 COMMIT/*!*/;
| log.000001 | 154 | Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= '191f7a9f-ffa2-11e5-a825-00163e00242a:1' | | log.000001 | 219 | Query | 100 | 291 | BEGIN | | log.000001 | 291 | Table_map | 100 | 336 | table_id: 113 (test.tt) | | log.000001 | 336 | Write_rows | 100 | 376 | table_id: 113 flags: STMT_END_F | | log.000001 | 376 | Xid | 100 | 407 | COMMIT /* xid=1
mysqlbinlog --base64-output=decode-rows -vvv log.000001
# at 154 #160517 15:09:13 server id 100 end_log_pos 219 CRC32 0x7439a004 GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= '191f7a9f-ffa2-11e5-a825-00163e00242a:1'/*!*/; # at 219 #160517 15:09:13 server id 100 end_log_pos 291 CRC32 0x0839a8df Query thread_id=22 exec_time=0 error_code=0 SET TIMESTAMP=1463468953/*!*/; SET @@session.pseudo_thread_id=22/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; 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=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 291 #160517 15:09:13 server id 100 end_log_pos 336 CRC32 0x738c7c74 Table_map: `test`.`tt` mapped to number 113 # at 336 #160517 15:09:13 server id 100 end_log_pos 376 CRC32 0x1949e971 Write_rows: table id 113 flags: STMT_END_F ### INSERT INTO `test`.`tt` ### SET ### @1=10 /* INT meta=0 nullable=1 is_null=0 */ # at 376 #160517 15:09:13 server id 100 end_log_pos 407 CRC32 0x63b03fee Xid = 132 COMMIT/*!*/;
binlog事件:由<公有事件头>+[私有事件头]+[事件体]三部分组成
所有的binlog事件都包含公有事件头,另外两个部分是根据事件类型可选
公有事件头:
http://dev.mysql.com/doc/internals/en/event-structure.html
v4 event structure: +=====================================+ | 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 | +=====================================+ header length = x bytes data length = (event_length - x) bytes
timestamp:包含了该项事件的开始的时间
event type:指是该事件的类型
serer_id :产生该事件的服务器的server-id
event_length :(包括了 公有事件头,私有事件头,事件体三部分的长度)
-------------------------------------------------------------------------
binlog事件类型:
FORMAT_DESCRIPTION_EVENT
MYSQL根据其定义的来解析其他事件
QUERY_EVENT:
事物开始 BEGIN事件
binlog_format='STATEMENT' ,具体执行的语句保存在QUERY_EVENT事件中
对于ROW 格式的BINLOG,所有DDL以文本格式的记录在QUERY_EVENT中
ROW_EVENT 分三类:(binlog_format='ROW')
1.WRITE_ROWS_EVENT ->insert
包含了要插入的数据
2.UPDATE_ROWS_EVENT ->update
包含了行修改前的值,也包含了修改后的值
3.DELETE_ROWS_EVENT ->delete
包含了需要删除行的主键值/行号
TABLE_MAP_EVENT
每个ROWS_EVENT事件之前有一个TABLE_MAP_EVENT用于描述内部ID和结构定义
XID_EVENT:
MYSQL进行崩溃恢复时间,根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事物
BINLOG_CHECKPOINT_EVENT
mysql执行崩溃恢的过程中,会根据读取的BINLOG_CHECKPOINT_EVENT来决定那些binlog文件是可以不用扫描的
ROTATE_EVENT
当BINLOG 文件大小达到max_binlog_size参数设置值时或者flush logs命令时,binlog发生切换,在文件尾添加ROTATE_EVENT事件
STOP_EVENT
MYSQL停止时,在文件尾加入STOP_EVENT