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