MySQL binlog格式解析
--by 石锅拌饭 [简书地址 http://www.jianshu.com/p/c16686b35807]
binlog想必大家都不陌生,在主从复制或者某些情况下的数据恢复会用到。由于binlog是二进制数据,要查看一般都借助mysqlbinlog工具。这篇笔记分析了binlog格式,希望能够了解下mysqlbinlog工具背后所做的事情。
1.什么时候写binlog
在说明什么时候写binlog前,先简单介绍下binlog的用途。binlog是二进制日志文件,用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据),在mysql主从复制中就是依靠的binlog。在mysql中开启binlog需要设置my.cnf中的log_bin参数,另外也可以通过binlog_do_db
指定要记录binlog的数据库和binlog_ignore_db指定不记录binlog的数据库。对运行中的mysql要启用binlog可以通过命令SET SQL_LOG_BIN=1
来设置。设置完成,我们就可以来测试binlog了。
需要注意下innodb引擎中的redo/undo log与mysql binlog是完全不同的日志,它们主要有以下几个区别:
- a)层次不同。redo/undo log是innodb层维护的,而binlog是mysql server层维护的,跟采用何种引擎没有关系,记录的是所有引擎的更新操作的日志记录。innodb的redo/undo log更详细的说明可以参见姜承尧的《mysql技术内幕-innodb存储引擎》一书中相关章节。
- b)记录内容不同。redo/undo日志记录的是每个页的修改情况,属于物理日志+逻辑日志结合的方式(redo log物理到页,页内采用逻辑日志,undo log采用的是逻辑日志),目的是保证数据的一致性。binlog记录的都是事务操作内容,比如一条语句
DELETE FROM TABLE WHERE i > 1
之类的,不管采用的是什么引擎,当然格式是二进制的,要解析日志内容可以用这个命令mysqlbinlog -vv BINLOG
。 - c)记录时机不同。redo/undo日志在事务执行过程中会不断的写入;而binlog仅仅在事务提交后才写入到日志,当然,binlog什么时候刷新到磁盘跟参数
sync_binlog
相关。
显然,我们执行SELECT等不涉及数据更新的语句是不会记binlog的,而涉及到数据更新则会记录。要注意的是,对支持事务的引擎如innodb而言,必须要提交了事务才会记录binlog。
binlog刷新到磁盘的时机跟sync_binlog参数相关,如果设置为0,则表示MySQL不控制binlog的刷新,由文件系统去控制它缓存的刷新,而如果设置为不为0的值则表示每sync_binlog次事务,MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的,在系统故障时最多丢失一个事务的更新,但是会对性能有所影响,一般情况下会设置为100或者0,牺牲一定的一致性来获取更好的性能。
通过命令SHOW MASTER LOGS
可以看到当前的binlog数目。如下面就是我机器上的mysql的binlog情况,第一列是binlog文件名,第二列是binlog文件大小。可以通过设置expire_logs_days
来指定binlog保留时间,要手动清理binlog可以通过指定binlog名字或者指定保留的日期,命令分别是:purge master logs to BINLOGNAME;
和purge master logs before DATE;
。
......
| mysql-bin.000018 | 515 |
| mysql-bin.000019 | 504 |
| mysql-bin.000020 | 107 |
+------------------+-----------+
2 binlog格式解析
2.1 binlog文件格式简介
binlog格式分为statement,row以及mixed三种,mysql5.5默认的还是statement模式,当然我们在主从同步中一般是不建议用statement模式的,因为会有些语句不支持,比如语句中包含UUID函数,以及LOAD DATA IN FILE语句
等,一般推荐的是mixed格式。暂且不管这三种格式的区别,看看binlog的存储格式是什么样的。binlog是一个二进制文件集合,当然除了我们看到的mysql-bin.xxxxxx这些binlog文件外,还有个binlog索引文件mysql-bin.index。如官方文档中所写,binlog格式如下:
- binlog文件以一个值为0Xfe62696e的魔数开头,这个魔数对应0xfe ‘b”i”n’。
- binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。
- header部分提供的是event的公共的类型信息,包括event的创建时间,服务器等等。
- data部分提供的是针对该event的具体信息,如具体数据的修改。
- 从mysql5.0版本开始,binlog采用的是v4版本,第一个event都是
format_desc event
用于描述binlog文件的格式版本,这个格式就是event写入binlog文件的格式。关于之前版本的binlog格式,可以参见http://dev.mysql.com/doc/internals/en/binary-log-versions.html - 接下来的event就是按照上面的格式版本写入的event。
- 最后一个
rotate event
用于说明下一个binlog文件。 - binlog索引文件是一个文本文件,其中内容为当前的binlog文件列表。比如下面就是一个mysql-bin.index文件的内容。
/var/log/mysql/mysql-bin.000019
/var/log/mysql/mysql-bin.000020
/var/log/mysql/mysql-bin.000021
接下来分析下几种常见的event,其他的event类型可以参见官方文档。event数据结构如下:
+=====================================+
| 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 |
+=====================================+
2.2 format_desc event
下面是我在FLUSH LOGS
之后新建的一个全新的binlog文件mysql-bin.000053,从binlog第一个event也就是format_desc event开始分析(mysql日志是小端字节序):
root@ubuntu:/var/log/mysql# hexdump -C mysql-bin.000053
00000000 fe 62 69 6e b8 b2 7f 56 0f 04 00 00 00 67 00 00 |.bin...V.....g..|
00000010 00 6b 00 00 00 01 00 04 00 35 2e 35 2e 34 36 2d |.k.......5.5.46-|
00000020 30 75 62 75 6e 74 75 30 2e 31 34 2e 30 34 2e 32 |0ubuntu0.14.04.2|
00000030 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 |-log............|
00000040 00 00 00 00 00 00 00 00 00 00 00