Mysql集群篇：Binlog解析

type

status

date

slug

summary

1、Binlog简介

1.1 Binlog是什么

Binlog 是 MySQL 的一种二进制日志，用来记录数据库变化的 SQL 语句（包括 DML 和 DDL，但不包括查询语句）或者行变更记录，主要用于数据复制和数据恢复。

Binlog 主要应用于以下场景：

主从复制：从服务器通过读取主服务器的 Binlog 实现数据同步

数据恢复：通过 Binlog 可以实现时间点恢复(Point-in-Time Recovery)

审计：记录所有对数据库的修改操作

Binlog 日志默认是开启的，可以通过执行下面命令查看 Binlog 是否开启以及 Binlog 文件的具体位置：

1.2 Binlog的三种日志格式

Binlog 有三种格式：

Statement：记录每一条修改数据的sql

优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了IO，提高了性能。
缺点：某些特殊场景下会导致主从复制异常。由于记录的只是执行语句，为了这些语句能在 Slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 Slave 得到和在 Master 端执行的时候相同的结果。像一些特定函数的功能，Slave 可与 Master 上要保持一致会有很多相关问题。

Row：记录每一行的变化。

优点：不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。
缺点：日志量大，尤其是批量操作的时候。

Mixed：默认使用 Statement，下面这些情况下自动切换到 Row 模式。

当 DML 语句更新一个 NDB 表时；
当函数中包含 UUID() 时；
2 个及以上包含 AUTO_INCREMENT 字段的表被更新时；
执行 INSERT DELAYED 语句时；
用 UDF 时；
视图中必须要求运用 row 时，例如建立视图时使用了 UUID() 函数。

生产中推荐使用 Row 格式，不推荐使用 Mixed 格式。Mixed 格式有点不可控，在主从复制时可能会产生一些意外问题。

1.3 查看Binlog日志

可以执行 show variables like '%log_bin%'; 命令查看 Binlog 文件默认存放路径，一般分为两类文件：

索引文件：文件名后缀为 .index，用于记录所有有效的的二进制文件。

日志文件：文件名后缀为 .00000*，记录数据库所有的 DDL 和 DML 语句事件。

Binlog 日志文件是二进制格式，使用普通的 Linux 命令（例如 cat、 more）打开会发生乱码。有两种方式可以查看 Binlog 文件内容：

1、第一种方式，登录 Mysql 服务器后执行 show binlog events 命令：

返回结果如下所示：

2、第二种方式，使用 mysqlbinlog 工具。 mysqlbinlog 是官方提供查看 Binlog 日志的工具，可以把二进制的文件转换成人类可读的日志格式。

返回结果如下：

如果你的电脑还没有安装 mysqlbinlog 工具，可以通过下面方式安装。

1.4 Binlog的文件格式

使用一个二进制工具打开 Binlog 文件并转成十六进制的格式，如下图所示

Binlog 文件以值为 0Xfe62696e 的魔数开头，这个魔数对应英文 bin。

Binlog 的内容由一系列的 binlog event 构成。每个 binlog event 包含 header 和 data 两部分。

event header：19子节，每个 event 都会包含，记录 event 的公共类型信息，包括 event 的创建时间，服务器等等。

event data：记录该 event 的具体信息，部分 event 才会包含，一般是 UPDATE_ROWS_EVENT 等事件。

binlog event 分为很多种类型，主要靠 type_code 来区分。目前 type_code 的分类如下：

执行 show binlog events 命令，以下面的返回结果为例：

FORMAT_DESCRIPTION_EVENT：Binlog 文件开始标志事件。

QUERY_EVENT：事务的开始标志符。记录一条 query 语句，在基于 Statement 的复制和基于 Row 的复制都会有。

TABLE_MAP：ROW EVENT 之前产生，为的是对 ROW EVENT 解析提供依据。

WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT：统称为ROW EVENT, 只有在基于row的复制方式下才会产生。

WRITE_ROWS_EVENT：包含了要插入的数据。
UPDATE_ROWS_EVENT：包含了修改前的值，也包含了修改后的值。
DELETE_ROWS_EVENT：包含了需要删除行前的值。

XID_EVENT：Innodb 下产生的事务结束标志符。如果是 Myisam 引擎的表，COMMIT 会是一个 query event。

ROTATE_EVENT：二进制日志结束标志事件。在即将更换一个新文件会触发该事件，可能因为文件大小达到限制，或者是 Mysql 重启，亦或者是调用了 flush logs 命令。

STOP_EVENT：数据库停止事件。当 MySQL 服务停止时，会在当前 binlog 文件尾添加一个 STOP_EVENT 事件表示数据库的停止。

2、常用Binlog命令

1、开启 binlog

有两种方式，第一种是修改配置文件 my.cnf ，修改后需要重启数据库。

如果不想重启数据库，可以使用第二种方式，执行以下命令

2、查看binlog的状态和文件位置

3、查看binlog的日志格式

4、查看/修改binlog的过期时间

5、查看binlog文件列表

5、查看binlog的内容

另一种方式是通过 mysqlbinlog 工具。

6、查看当前正在写入的binlog文件

7、刷新binlog（创建新文件）

下面情况也会重新生成一个新的 binlog 文件：

MySQL服务器停止或重启时。

当 binlog 文件大小超过 max_binlog_size 系统变量配置的上限时。

8、手动清理binlog

9、重置binlog（慎用）