TOC
MySQL的Binlog
Binlog概述
binlog是记录所有数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据修改(INSERT、UPDATE、DELETE…)的二进制日志。
binlog不会记录SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改,但你可以通过查询通用日志来查看MySQL执行过的所有语句。
二进制日志包括两类文件:
二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,
二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
binlog基本定义:二进制日志,记录对数据发生或潜在发生更改的SQL语句,并以二进制的形式保存在磁盘中;
一般来说开启binlog日志大概会有1%的性能损耗。
MySQL Server有四种类型的日志:
日志类型
写入日志的信息
错误日志Error Log
当数据库启动、运行、停止时出现问题产生该日志
通用查询日志General Query Log
记录建立的客户端连接和执行的语句
二进制日志Binary Log
记录更改数据的语句,数据库内容发生改变时产生该日志,也被用来实现主从复制功能
中继日志relay log
主从复制时,从库上收到主库的数据更新时产生该日志
慢查询日志Slow Query Log
记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
DDL日志(元数据日志)metadata log
执行DDL语句操作元数据时产生该日志
Binlog用途:
MySQL的作用类似于Oracle的归档日志,可以用来查看数据库的变更历史(具体的时间点所有的SQL操作)、数据库增量备份和恢复(增量备份和基于时间点的恢复)、Mysql的复制(主主数据库的复制、主从数据库的复制)
最重要的使用场景
MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复:通过使用 mysqlbinlog工具来使恢复数据
结构解析
索引文件
索引文件就是上文中的 master-bin.index 文件,是一个普通的文本文件,以换行为间隔,一行一个文件名。比如它可能是:
master-bin.000001
master-bin.000002
master-bin.000003
对应的每行文件就是一个 Binlog 实体文件了。
Binlog 文件
Binlog 的文件结构大致由如下几个方面组成:
文件头
文件头由一个四字节Magic Number,其值为 1852400382,在内存中就是 "\xfe\x62\x69\x6e",参考 MySQL 源码的 log_event.h,也就是 '\0xfe' 'b' 'i' 'n'。
与平常二进制一样,通常都有一个 Magic Number 进行文件识别,如果 Magic Number 不吻合上述的值那么这个文件就不是一个正常的 Binlog。
事件
Binlog 事件类型
v1,用于 MySQL 3.2.3
v3,用于 MySQL 4.0.2 以及 4.1.0
v4,用于 MySQL 5.0 以及更高版本
实际上还有一个 v2 版本,不过只在早期 4.0.x 的 MySQL 版本中使用过,但是 v2 已经过于陈旧并且不再被 MySQL 官方支持了。
通常我们现在用的 MySQL 都是在 5.0 以上的了,所以就略过 v1 ~ v3 版本的 Binlog,如果需要了解 v1 ~ v3 版本的 Binlog 可以自行前往上述的《High-level...》文章查看。
事件类型
事件类型
说明
UNKNOWN_EVENT
此事件从不会被触发,也不会被写入binlog中;发生在当读取binlog时,不能被识别其他任何事件,那被视为UNKNOWN_EVENT
START_EVENT_V3
每个binlog文件开始的时候写入的事件,此事件被用在MySQL3.23 – 4.1,MYSQL5.0以后已经被 FORMAT_DESCRIPTION_EVENT 取代
QUERY_EVENT
执行更新语句时会生成此事件,包括:create,insert,update,delete;
STOP_EVENT
当mysqld停止时生成此事件
ROTATE_EVENT
当mysqld切换到新的binlog文件生成此事件,切换到新的binlog文件可以通过执行flush logs命令或者binlog文件大于 max_binlog_size 参数配置的大小;
INTVAR_EVENT
当sql语句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函数;此事件没有被用在binlog_format为ROW模式的情况下
LOAD_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL 3.23版本中使用
SLAVE_EVENT
未使用
CREATE_FILE_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
APPEND_BLOCK_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0版本中使用
EXEC_LOAD_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
DELETE_FILE_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0版本中使用
NEW_LOAD_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
RAND_EVENT
执行包含RAND()函数的语句产生此事件,此事件没有被用在binlog_format为ROW模式的情况下
USER_VAR_EVENT
执行包含了用户变量的语句产生此事件,此事件没有被用在binlog_format为ROW模式的情况下
FORMAT_DESCRIPTION_EVENT
描述事件,被写在每个binlog文件的开始位置,用在MySQL5.0以后的版本中,代替了START_EVENT_V3
XID_EVENT
支持XA的存储引擎才有,本地测试的数据库存储引擎是innodb,所有上面出现了XID_EVENT;innodb事务提交产生了QUERY_EVENT的BEGIN声明,QUERY_EVENT以及COMMIT声明,如果是myIsam存储引擎也会有BEGIN和COMMIT声明,只是COMMIT类型不是XID_EVENT
BEGIN_LOAD_QUERY_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL5.0版本中使用
EXECUTE_LOAD_QUERY_EVENT
执行LOAD DATA INFILE 语句时产生此事件,在MySQL5.0版本中使用
TABLE_MAP_EVENT
用在binlog_format为ROW模式下,将表的定义映射到一个数字,在行操作事件之前记录(包括:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT)
PRE_GA_WRITE_ROWS_EVENT
已过期,被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT
已过期,被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT
已过期,被 DELETE_ROWS_EVENT 代替
WRITE_ROWS_EVENT
用在binlog_format为ROW模式下,对应 insert 操作
UPDATE_ROWS_EVENT
用在binlog_format为ROW模式下,对应 update 操作
DELETE_ROWS_EVENT
用在binlog_format为ROW模式下,对应 delete 操作
INCIDENT_EVENT
主服务器发生了不正常的事件,通知从服务器并告知可能会导致数据处于不一致的状态
HEARTBEAT_LOG_EVENT
主服务器告诉从服务器,主服务器还活着,不写入到日志文件中
事件的结构
在文件头之后,跟随的是一个一个事件依次排列。每个事件都由一个事件头和事件体组成。
事件头:里面的内容包含了这个事件的类型(如新增、删除等)、事件执行时间以及是哪个服务器执行的事件等信息。
第一个事件是一个事件描述符,描述了这个 Binlog 文件格式的版本。接下去的一堆事件将会按照第一个事件描述符所描述的结构版本进行解读。最后一个事件是一个衔接事件,指定了下一个 Binlog 文件名——有点类似于链表里面的 next 指针。
根据《[High-Level Binary Log Structure and Contents](High-Level Binary Log Structure and Contents)》所述,不同版本的 Binlog 格式不一定一样,所以也没有一个定性。在我写这篇文章的时候,目前有三种版本的格式。
+=====================================+
|event|timestamp0:4|
|header+----------------------------+
||type_code4:1|
|+----------------------------+
||server_id5:4|
|+----------------------------+
||event_length9:4|
|+----------------------------+
||next_position13:4|
|+----------------------------+
||flags17:2|
|+----------------------------+
||extra_headers19:x-19|
+=====================================+
|event|fixedpart x:y|
|data+----------------------------+
||variable part|
+==========================