MySQL的7种日志(四):BinLog

0.前言

续：

• MySQL的7种日志(一):概况
• MySQL的7种日志(二):RedoLog
• MySQL的7种日志(三):UndoLog
• MySQL的7种日志(四):BinLog

1.什么是binlog

• 又名:MySQL归档日志,MySQL二进制日志
• 记录所有数据库表结构变更（DDL例如CREATE、ALTER TABLE…）以及表数据修改（DMLINSERT、UPDATE、DELETE …）的所有操作。
• 默认情况下，二进制日志并不是在每次写的时候同步到磁盘。因此，当数据库所在地操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来问题。

2.binlog的作用

• 时间点的恢复：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可通过二进制日志进行即时点（point-in-time）恢复。
• 主从复制：通过复制和执行二进制日志使一台远程的 Mysql 数据库（一般称为 slave）与一台 MySQL 数据库（一般称为 master）进行实时同步。
• 变更审计：用户可以通过二进制日志中的信息来进行审计，回溯是否对数据库的修改。
• 误操作回滚：当误修改(ins/upd/del)发生时,可以用binlog解析出修改前后的语句,用于快速回滚
• 异构数据同步：通过解析binlog,可以将MySQL的变更通知到异构数据源(kafka,es,mongo,redis,mq…)
• 事务存储引擎的崩溃恢复。MySQL采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared 和 commit 两种。对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog：如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

3.binlog 和 redolog 区别

• 适用对象不同：
• binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
• redolog 是 InnoDB 引擎特有的
• 写入内容不同：
  • binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
  • STATEMENT：语句
  • ROW：记录行数据最终被修改成什么样了
  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式； redolog 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；
• 写入方式不同：
• binlog 是可以追加写入的。“追加写” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
• redolog 是循环写的，空间固定会被用完
• 作用不同

4.什么时候写binlog

• 对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。
• binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
• 如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

5.binlog的存储

binlog日志包括两类文件:

• 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
• 二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

a.binlog的索引文件:一个文本文件，其中内容为当前的binlog文件列表

• 当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：
• 1.MySQL服务器停止或重启时
• 2.使用 flush logs 命令；
• 当 binlog 文件大小超过 max_binlog_size 变量的值时；(如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作)

b.binlog:一个二进制文件集合

• 每个binlog文件以一个4字节的魔数开头，接着是一组Events:
• 魔数：0xfe62696e对应的是0xfebin；
• Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；
• 第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；
• 其余的Event按照第一个Event的格式版本写入；
• 最后一个Event用于说明下一个binlog文件；

binlog日志物理存储

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    | 
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    | 
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
+=====================================+
| event  | binlog_version   19 : 2    | 
| data   +----------------------------+
|        | server_version   21 : 50   |
|        +----------------------------+
|        | create_timestamp 71 : 4    |
|        +----------------------------+
|        | header_length    75 : 1    |
|        +----------------------------+
|        | post-header      76 : n    | 
|        | lengths for all            |  
|        | event types                |
+=====================================+
如果事件头的长度是 x 字节，那么事件体的长度为 (event_length - x) 字节；设事件体中 fixed part 的长度为 y 字节，那么 variable part 的长度为 (event_length - (x + y)) 字节。

type_code

• 主要有3个版本：

• v1: 在 MySQL 3.23 中使用
• v3: 在 MySQL 4.0.2 到 4.1 中使用
• v4: 在 MySQL 5.0 及以上版本中使用

现在一般不会使用MySQL5.0以下版本，所以下面仅介绍v4版本的binlog事件类型。binlog 的事件类型较多，本文在此做一些简单的汇总

6.binlog解析和反解

参见:

• MySQL常用脚本_binlog解析和回滚

其他binlog问题

• MySQL binlog 问答