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,个人的微信公众号也会发一些自己的研究整理的文章,多多关注
转载于:https://blog.51cto.com/xiaozhong991/1959715