mysql binlog协议分析--具体event
这几天在修改canal, 连接mysql和maria接收到的event有所区别
拿一个简单的insert sql来举例
mysql 会有以下几个event写入到binlog里
1.ANONYMOUS_GTID_LOG_EVENT
2.QUERY_EVENT
header {
version: 1
logfileName: "20170105-162017-bin.000001"
logfileOffset: 1920
serverId: 1
serverenCode: "UTF-8"
executeTime: 1508809530000
sourceType: MYSQL
schemaName: ""
tableName: ""
eventLength: 72
}
entryType: TRANSACTIONBEGIN
storeValue: " \354\001"
3.TABLE_MAP_EVENT
4.WRITE_ROWS_EVENT
header {
version: 1
logfileName: "20170105-162017-bin.000001"
logfileOffset: 2040
serverId: 1
serverenCode: "UTF-8"
executeTime: 1508809530000
sourceType: MYSQL
schemaName: "test"
tableName: "test1"
eventLength: 40
eventType: INSERT
}
entryType: ROWDATA
storeValue: "\b\333\001\020\001P\000b\035\022\033\b\000\020\004\032\002id \000(\0010\000B\00215R\aint(11)"
5.XID_EVENT
header {
version: 1
logfileName: "20170105-162017-bin.000001"
logfileOffset: 2080
serverId: 1
serverenCode: "UTF-8"
executeTime: 1508809530000
sourceType: MYSQL
schemaName: ""
tableName: ""
eventLength: 31
}
entryType: TRANSACTIONEND
storeValue: "\022\003184"
如果一个事务里不仅一条sql, 4.WRITE_ROWS_EVENT就会有N条, 我们可以注意到一个完整事务TRANSACTIONBEGIN和TRANSACTIONEND必然会配对出现
mysql event部分格式说明
https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
mariadb 的事件和mysql不一样
1.GTID_EVENT
2.TABLE_MAP_EVENT
3.WRITE_ROWS_EVENT
header {
version: 1
logfileName: "20170105-162017-bin.000001"
logfileOffset: 2040
serverId: 1
serverenCode: "UTF-8"
executeTime: 1508809530000
sourceType: MYSQL
schemaName: "test"
tableName: "test1"
eventLength: 40
eventType: INSERT
}
entryType: ROWDATA
storeValue: "\b\333\001\020\001P\000b\035\022\033\b\000\020\004\032\002id \000(\0010\000B\00215R\aint(11)"
4.XID_EVENT
header {
version: 1
logfileName: "20170105-162017-bin.000001"
logfileOffset: 2080
serverId: 1
serverenCode: "UTF-8"
executeTime: 1508809530000
sourceType: MYSQL
schemaName: ""
tableName: ""
eventLength: 31
}
entryType: TRANSACTIONEND
storeValue: "\022\003184"
gtid_event就是用来替代begin query event的
Binlog event的header格式如下：
4 timestamp
1 event type
4 server-id
4 event-size
4 log pos
2 flags
header的长度固定为19，event type用来标识这个event的类型，event size则是该event包括header的整体长度，而log pos则是下一个event所在的位置
binlog基础格式介绍可参考
http://www.jianshu.com/p/5e6b33d8945f
canal源码里并没有对gtid_event进行解析处理，查看mariadb官网
Event Header
- Type[1] = 0xa2
- Flags[2] = 08 00 => LOG_EVENT_SUPPRESS_USE_F
Fields
if flag & FL_GROUP_COMMIT_ID
- uint<8> commit_id
else
- uint<6> 0
具体地址参考 https://mariadb.com/kb/en/library/gtid_event/
下面是截取到2个事务gtid_event的字节码
41, 0, 0, 34, 0, -17, 0, -71, 75, 51, 90, -94, 2, 0, 0, 0, 38, 0, 0, 0, 56, 20, 0, 0, 8, 0, 115, 25, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12, 0, 0, 0, 0, 0, 0, 31, 0, 0, 0, 0, 0, 0, 0, 11, 0, 0, 0, 0, 3, 8, 8, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 19, 56, 13, 0, 8, 0, 18, 0, 4, 4, 4, 4, 18, 0, 0, -35, 0, 4, 26, 8, 0, 0, 0, 8, 8, 8, 2, 0, 0, 0, 10, 10, 10, 41, 0, 0, 38, 0, -17, 0, -30, 75, 51, 90, -94, 2, 0, 0, 0, 38, 0, 0, 0, -28, 20,0, 0, 8, 0, 116, 25, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12, 0, 0, 0, 0, 0, 0, 32, 0, 0, 0, 0, 0, 0, 0, 11, 0, 0, 0, 0, 3, 8, 8, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 19, 56, 13, 0, 8, 0, 18, 0, 4, 4, 4, 4, 18, 0, 0, -35, 0, 4, 26, 8, 0, 0, 0, 8, 8, 8, 2, 0, 0, 0, 10, 10, 10,
红色部分是message header, 41, 0, 0是message长度,34 是message sequence_id, -17, 0是semi标识位, -71或者-30开始就是具体event的内容了 115, 25, 0, 0, 0, 0, 0, 0 就是gtid了，用小序列标识=6516
Protocol::Packet Data between client and server is exchanged in packets of max 16MByte size. Payload Type Name Description int<3> payload_length Length of the payload. The number of bytes in the packet beyond the initial 4 bytes that make up the packet header. int<1> sequence_id Sequence ID string<var> payload payload of the packet Example: 01 00 00 00 01 length: 1 sequence_id: x00 payload: 0x01
