Mysql binlog 详细解读

　　Mysql的binlog主要用于逻辑同步以及二阶段提交的安全性保证，而在平时的使用中我们DBA也会从中获取一些重要的信息，比如说主从同步延迟了我们可以通过binlog查看当前事务执行的内容，比如可以利用binlog+备份的方式进行误删回滚，开源工具中也有很多利用binlog做闪回、同步数据到缓存中的方案，异地多活的高可用框架完成后有点空闲就对binlog的组成深入了解了下（基于mysql5.7版本，binlog版本v4）,mysql 5.0之后binlog都采用的v4版本，结构如下分为header和data两部分，header部分所有event都一样占用19bytes：
　　+================================+
　　| event  | timestamp  0 : 4  |#当前event写入时的时间
　　| header +-----------------------+
　　|      | type_code  4 : 1   |#当前event的类型ID
　　|      +-----------------------+
　　|      | server_id  5 : 4   |
　　|      +------------------------+
　　|      | event_length 9 : 4   |#当前event总字节数
　　|      +------------------------+
　　|      | next_position 13 : 4  |#下一个event开始的position
　　|      +-------------------------+
　　|      | flags 17 : 2       |#标签
　　|      +-------------------------+
　　|      | extra_headers 19 : x-19 |
　　+===================================+
　　| event  | fixed part x : y    |
　　| data  +------------------------+
　　|      | variable part      |
　　+=================================+
　　binlog文件是二进制文件，由一个一个的event组成，每个对数据变动的操作以及DDL语句都会产生一系列的event,:
　　FORMAT_DESCRIPTION_EVENT：binlog文件的第一个event，记录版本号等元数据信息
　　QUERY_EVENT: 存储statement类的信息，基于statement的binlog格式记录sql语句，在row模式下记录事务begin标签
　　XID_EVENT: 二阶段提交xid记录
　　TABLE_MAP_EVENT: row模式下记录表源数据，对读取行记录提供规则参考，后面会详细介绍
　　WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT: row模式下记录对应行数据变化的记录
　　GTID_LOG_EVENT: 这个就是记录GTID事务号了，用于5.6版本之后基于GTID同步的方式
　　ROTATE_EVENT: 连接下一个binlog文件
　　Event类型还有很多，而与我们平时操作关联较多的也就上面这几个，有兴趣更详细了解的参考https://dev.mysql.com/doc/internals/en/event-classes-and-types.html
　　FORMAT_DESCRIPTION_EVENT
　　简称为format_desc，我们直接在mysql执行reset master清空所有binlog重头生成一个新文件直接操作更容易理解

　　可以看出format_desc开始的pos位置为4，这是因为每个binlog文件开头都会占用一个固定的4bytes，编码为\xFE\x62\x69\x6E，现在来开始对他进行解析

　　Format_desc event data部分的fixed part格式分别为
　　2bytes记录binlog version
　　50bytes记录MySQL server version
　　4bytes记录binlog文件创建时间
　　1bytes 值为19，是所有event的header长度
　　剩余的所有字节分别记录mysql内部已定义event的fix par部分的长度
　　按照这个规则可以找到binlog version记录值为0x0004也就是v4，后面50个字节通过解析为5.7.19-log，再4个字节创建时间的时间戳为1503306555，下面是我用python解析可以看出结果

　　QUERY_EVENT
　　1. Fixed part部分：
　　Thread_id: 4bytes 产生数据的线程ID，可以可以用于DBA审计
　　Execute_time: 4bytes 该语句执行时间，单位秒
　　Databas_length: 1bytes 库名占字节长度
　　Error_code: 2bytes 错误代码，一般该值都为0，比如在master上执行inser...select...语句时myisam表出现主键冲突或者innodb表执行途中ctrl+c退出就会记录该值，在slave执行时会检查报错退出
　　Variable_block_length: 2bytes 记录data part部分variable status的长度
　　2. Variable part:
　　Variable_status : Variable_block_length,每个variable对应一个值，值是紧跟variable后面，这里记录charset、sqlmode、auto_increment等的情况，自增只有偏移大于1的时候才记录
　　Database_name: Databas_length
　　Sql_statement: 整个event剩余部分
　　TABLE_MAP_EVENT
　　1. Fixed part:
　　Table_id : 6bytes
　　Reserved: 2bytes 预留位置
　　2. Variable part:
　　Database_name_length: 1bytes
　　Database_name: database_name_length + 1个空字节
　　Table_name_length: 1bytes
　　Table_name: table_name_length + 1个空字节
　　Columns: 1bytes 记录表字段数，一般情况字段数是不会超过255所以占1bytes
　　Columns_type_code: 记录每个字段类型id,每个字段占用1个子节点（colums*1）,该位记录的值主要对读取后面源数据提供标准，顺序与表结构字段顺序一致
　　Metadata_length: 1bytes 字段元数据占用字节长度，同样一般不会超过1bytes
　　Metadata: 可变长度为metadata_length，需根据Columns_type_code判断每个字段占用长度，数字类型（int,tinyint...）都不会占用空间，只有可变长度的才会占用，比如varchar、char、enum、binary都占用2bytes，text、blob、longtext只占用1bytes，这里的元数据对后面读取row记录提供格式规范
　　Variable_size: 该部分记录字段是否允许为空，一位代表一个字段，占用字int((N+7/8))bytes,N为字段数
　　Crc: 4bytes 最后4字节校验码
　　WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT
　　1. Fixed part:
　　Table_id : 6bytes
　　Reserved: 2bytes 预留位置
　　Extra: 2bytes 具体干嘛的不清楚，官方文档也没介绍有这2bytes内容
　　2. Variable part:
　　Columns: 1bytes 字段数
　　Variable_size: 可变长度int((n+7)/8)，n是字段数，bit标识对应字段是否有值，1代表有，0代表没有，row模式都是有值的
　　Variable_size: 跟上面一个一样，但是只有update_rows_event才有
　　Variable_size: 跟上面两个一样计算长度，该值的bit位标识后面所跟的行数据每个字段是否为NULL，为NULL时bit位为0, 1代表有值，这个bit位和table_map_event的columns_type_code顺序对应
　　Value: 数据内容
　　Crc: 4bytes 校验码
　　XID_EVENT
　　Fixed part为空，只有variable part占有8bytes的xid及结尾的4bytes校验码
　　GTID_LOG_EVENT
　　官网未找到有对该event的结构介绍，在源码中有详细的备注:
　　+------+--------+-------+-------+--------------+---------------+
　　|flags |SID   |GNO   |lt_type|last_committed|sequence_number|
　　|1 byte|16 bytes|8 bytes|1 byte |8 bytes     |8 bytes     |
　　+------+--------+-------+-------+--------------+---------------+
　　Flags: 判断是SBR、RBR的标准
　　0: RBR，在用mysqlbinlog打印时会看到rbr_only=yes以及
　　/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
　　1: SBR 也就是statement模式的记录，打印时会有rbr_only=no
　　SID: mysql服务的uuid
　　GNO: 事务序列id
　　Lt_type: 一般固定为数字2
　　Last_commited: 用于5.7版本后的多线程复制
　　Sequence_number: binlog中的序列号
　　一个正常业务数据库产生的binlog要进行分析的话，基本也就上面这几个和我们关联最多，(https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html)官方文档有对所有event的详细介绍，有兴趣的可以瞧瞧
　　要对binlog进行解析还需要了解数据存储详细占用情况，同样的在官方文档有详细的介绍，参考https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html，这里拿我们常用的几个字段类型来做介绍及测试
　　1. Varchar: 占用字节数0-255bytes使用1byts记录长度，超过255bytes时使用2bytes记录长度 + 数据
　　2. Int、tinyint、bigint: 分别占用4bytes、1bytes、8bytes
　　3. Text: 使用2个字节记录长度 + 数据
　　4. Timestamp(M): 4bytes记录日期时间 + 精确到的毫秒部分，占用长度取决于M
　　5. Datetime(M):5bytes 记录日期时间 + 精确到的毫秒部分，占用长度取决于M
　　6. 毫秒部分占用情况，FSP就是上面所说的M值：
　　FSP    Storage
　　0,0    0 bytes
　　1,2    1 bytes
　　3,4    2bytes
　　4,5    3bytes
　　7. Datetime 5bytes记录分布：
　　1 bit sign (1= non-negative, 0= negative)
　　17 bits year*13+month (year 0-9999, month 0-12)
　　5 bits day (0-31)
　　5 bits hour (0-23)
　　6 bits minute (0-59)
　　6 bits second (0-59)
　　---------------------------
　　40 bits = 5 bytes
　　下面我创建了一个包含几个常用字段的表尝试进行对event解析
　　CREATE TABLE `t1` (
　　`id` int(11) NOT NULL AUTO_INCREMENT,
　　`name` varchar(10) DEFAULT NULL,
　　`content` varchar(256) DEFAULT NULL,
　　`status` tinyint(4) DEFAULT NULL,
　　`bignum` bigint(20) DEFAULT NULL,
　　`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB DEFAULT CHARSET=latin1

　　插入了一条记录，利用show binlog events找到对应的event的起始点进行解析，先来看table_map

　　Table_map_event的postion是从302开始的，也就是302的字节位开始，根据上面的介绍我们跳过19bytes的event_header和8bytes的fix par部分，然后的一个字节为0x07表示库名长度为7bytes，接着的7bytes+空结束符就是库名xz_test，接着0x02表示表名长度为2bytes,接着的2bytes+空结束符为表名t1，紧接着的0x06表示表有6个字段，接着的6bytes位分别是6个字段类型id,接着0x05是元数据占用5bytes，从我们创建的表字段顺序来看，首先int、tinyint、bigint不占用字节位，varchar占用2bytes，timesteamp占用1bytes,所以这后面的5个子节点是对应name、content、timesteamp字段的元数据，0x000a是name字段元数据，长度为10，0x0100是content字段长度256，最后的一个字节0x00表示timestamp没有毫秒的精度，最后面5bytes读完该event就结束
　　紧接着后面的event为write_rows_event是插入的数据所在，首先还是跳过event_header和fix par部分，也就是29个字节，接着的0x06表示有6个字段，接着的0xff的bit位表示各列是否都存在值，这里表示都存在，接下来的0xd0也是用bit位判断，不过这是判断各字段值是否为NULL，0表示null，1表示不为null, 首先4bytes的0x00000002是自增主键id值2，接着是vhachar字段根据table_map得出元数据为10，没有超过255这里占用1bytes即为0x01表示后面的字段内容只占用1bytes,接着的0x61就是插入的’a’，接着就是content字段，根据元数据值得出256大于255所以这里占用2bytes,0x0002数据内容占用2bytes,后面的两字节就是内容，接着是status插入值0x01，紧接着的就是create_time字段值，因为bignum字段我们没有插入值，元数据中没有得出毫秒位精度，所以字节位是0x599baa57，最后4bytes的校验码该event结束
　　一个事务产生的所有event会被GTID_LOG_EVENT和XID_EVENT包住，table_map和update/delete/write_rows_event的关系通过上面已大概清楚，官方文档中对数据存储及各个event结构都有详细介绍，有兴趣的可以参考官网，用python写了一个对binlog文件分析和生成回滚语句的工具，可以快速定位binlog文件中的GTID、thread_id、pos范围、时间范围的数据内容,显示比官方的mysqlbinlog简洁很多（https://github.com/wwwbjqcom/Analysisbinlog.git），暂时不支持set、bit两个字段类型,显示效果见下图

　　ps：mysql技术交流qq群479472450,个人的微信公众号也会发一些自己的研究整理的文章，多多关注