成为数据治理专家：深入解析 MySQL Binlog

概述

MySQL 的 Binlog Mysql Sever层维护的一种二进制日志，其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中。Binlog 记录所有的 DDL 和 DML 语句(除了数据查询语句SELECT、SHOW等)，以 Event 的形式记录，同时记录语句执行时间。

Binlog 的主要作用有两个：

数据恢复

因为 Binlog 详细记录了所有修改数据的 SQL，当某一时刻的数据误操作而导致出问题，或者数据库宕机数据丢失，那么可以根据 Binlog 来回放历史数据。

主从复制

想要做多机备份的业务，可以去监听当前写库的 Binlog 日志，同步写库的所有更改。

Binlog 包括两类文件：

二进制日志索引文件(.index)：记录所有的二进制文件。
二进制日志文件(.00000*)：记录所有 DDL 和 DML 语句事件。

binlog 配置

Binlog 日志功能默认是开启的，在 MySQL 的配置文件 my.cnf 中对 binlog 进行设置。

#启用binlog功能，并制定二进制日志的存储目录
log-bin=/home/mysql/binlog/

#mysql-bin.*日志文件最大字节（单位：字节）
#设置最大100MB
max_binlog_size=104857600

#设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7

#binlog日志只记录指定库的更新
#binlog-do-db=db_name

#binlog日志不记录指定库的更新
#binlog-ignore-db=db_name

#写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0

max_binlog_size ：binlog 最大和默认值是 1G，该设置并不能严格控制 binlog 的大小，尤其是 binlog 比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进当前日志直到事务结束。所以真实文件有时候会大于 max_binlog_size 设定值。

expire_logs_days ：binlog 过期删除不是服务定时执行，当服务器重启，服务器被更新，日志达到了最大日志长度 max_binlog_size，日志被刷新时就会执行。

二进制日志由配置文件的 log-bin 选项负责启用，MySQL 服务器将在数据根目录创建两个新文件mysql-bin.000001 和 mysql-bin.index，若配置选项没有给出文件名，MySQL 将使用主机名称命名这两个文件，其中 .index 文件包含一份全体日志文件的清单。

sync_binlog：这个参数决定了 Binlog 日志的更新频率。默认 0 ，表示该操作由操作系统根据自身负载自行决定多久写一次磁盘。

sync_binlog = 1 表示每一条事务提交都会立刻写盘。sync_binlog=n 表示 n 个事务提交才会写盘。

binlog 写入的时机是事务执行完，但任何相关的 Locks 还未释放或事务还未最终 commit 前。这样保证了 binlog 记录的操作时序与数据库实际的数据变更顺序一致。

常用指令

「检查 Binlog 文件是否已开启」

mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/binlog       |
| log_bin_index                   | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
| sql_log_bin                     | ON                                 |
+---------------------------------+------------------------------------+

MySQL 会把用户对所有数据库的内容和结构的修改情况记入 mysql-bin.n 文件，而不会记录 SELECT 和没有实际更新的 UPDATE 语句。

「查看 binlog 文件列表」

#查看binlog 文件列表
show binary logs;

#查看最新的binlog
show master status; 

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       156 | No        |
+------------------+-----------+-----------+

查看 binlog 文件内容

Binlog 文件是二进制文件，强行打开看到的必然是乱码，MySQL 提供了命令行的方式来展示 Binlog 日志：

mysqlbinlog mysql-bin.000004

Binlog 通过事件的方式来管理日志信息，可以通过 show binlog events in 的语法来查看当前 Binlog 文件对应的详细事件信息。

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000001 | 125 | Previous_gtids |         1 |         156 |                                   |
| mysql-bin.000001 | 156 | Stop           |         1 |         179 |                                   |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.01 sec)

Binlog 的版本是V4，出现 Stop event 有两种情况：

是 master shut down 的时候会在 Binlog 文件结尾出现.
是备机在关闭的时候会写入 relay log 结尾，或者执行 RESET SLAVE 命令执行.

一般来说一份正常的 Binlog 日志文件会以 Rotate event 结束。当 Binlog 文件超过指定大小，Rotate event 会写在文件最后，指向下一个 Binlog 文件。

有数据写入的 binlog ：

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                    |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4                                       |
| mysql-bin.000002 | 125 | Previous_gtids |         1 |         156 |                                                                         |
| mysql-bin.000002 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                    |
| mysql-bin.000002 | 235 | Query          |         1 |         323 | BEGIN                                                                   |
| mysql-bin.000002 | 323 | Intvar         |         1 |         355 | INSERT_ID=13                                                            |
| mysql-bin.000002 | 355 | Query          |         1 |         494 | use `test_db`; INSERT INTO `test_db`.`test_db`(`name`) VALUES ('xdfdf') |
| mysql-bin.000002 | 494 | Xid            |         1 |         525 | COMMIT /* xid=192 */                                                    |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+

event 查询的数据行关键字段含义：

Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。

第一个事件位置并不是从 0 开始，而是从 4。MySQL 通过文件中的前 4 个字节，来判断这是不是一个 Binlog 文件。

Event_type：表示事件的类型。

Server_id：表示产生这个事件的 MySQL server_id，通过设置 my.cnf 中的 server-id 选项进行配置。

End_log_position：下一个事件的开始位置。

Info：包含事件的具体信息。

binlog 事件类型

binlog 事件的结构主要有3个版本：

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

现在一般不会使用MySQL5.0以下版本，所以下面仅介绍v4版本的binlog事件类型。

enum Log_event_type
{
/**
     Every time you update this enum (when you add a type), you have to
     fix Format_description_event::Format_description_event().
     */
    UNKNOWN_EVENT= 0,
    START_EVENT_V3= 1,
    QUERY_EVENT= 2,
    STOP_EVENT= 3,
    ROTATE_EVENT= 4,
    INTVAR_EVENT= 5,
    LOAD_EVENT= 6,
    SLAVE_EVENT= 7,
    CREATE_FILE_EVENT= 8,
    APPEND_BLOCK_EVENT= 9,
    EXEC_LOAD_EVENT= 10,
    DELETE_FILE_EVENT= 11,
/**
     NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
     sql_ex, allowing multibyte TERMINATED BY etc; both types share the
     same class (Load_event)
     */
    NEW_LOAD_EVENT= 12,
    RAND_EVENT= 13,
    USER_VAR_EVENT= 14,
    FORMAT_DESCRIPTION_EVENT= 15,
    XID_EVENT= 16,
    BEGIN_LOAD_QUERY_EVENT= 17,
    EXECUTE_LOAD_QUERY_EVENT= 18,

    TABLE_MAP_EVENT = 19,

/**
     The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
     therefore obsolete.
     */
    PRE_GA_WRITE_ROWS_EVENT = 20,
    PRE_GA_UPDATE_ROWS_EVENT = 21,
    PRE_GA_DELETE_ROWS_EVENT = 22,

/**
     The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
     */
    WRITE_ROWS_EVENT_V1 = 23,
    UPDATE_ROWS_EVENT_V1 = 24,
    DELETE_ROWS_EVENT_V1 = 25,

/**
     Something out of the ordinary happened on the master
     */
    INCIDENT_EVENT= 26,

/**
     Heartbeat event to be send by master at its idle time
     to ensure master's online status to slave
     */
    HEARTBEAT_LOG_EVENT= 27,

/**
     In some situations, it is necessary to send over ignorable
     data to the slave: data that a slave can handle in case there
     is code for handling it, but which can be ignored if it is not
     recognized.
     */
    IGNORABLE_LOG_EVENT= 28,
    ROWS_QUERY_LOG_EVENT= 29,

/** Version 2 of the Row events */
    WRITE_ROWS_EVENT = 30,
    UPDATE_ROWS_EVENT = 31,
    DELETE_ROWS_EVENT = 32,

    GTID_LOG_EVENT= 33,
    ANONYMOUS_GTID_LOG_EVENT= 34,

    PREVIOUS_GTIDS_LOG_EVENT= 35,

    TRANSACTION_CONTEXT_EVENT= 36,

    VIEW_CHANGE_EVENT= 37,

/* Prepared XA transaction terminal event similar to Xid */
    XA_PREPARE_LOG_EVENT= 38,
/**
     Add new events here - right above this comment!
     Existing events (except ENUM_END_EVENT) should never change their numbers
     */
    ENUM_END_EVENT /* end marker */
};

UNKNOWN_EVENT:这个事件不应该发生，他永远不会被写入日志文件中，如果日志文件中的事件无法被识别为其他的事件，则被看成是UNKNOWN_EVENT。

START_EVENT_V3: 该事件是写在每个二进制文件开头的一个描述性的事件，从MySQL 5.0 开始被 FORMAT_DESCRIPTION_EVENT事件取代。

QUERY_EVENT: 在如下情况下写入 1、事务开始时，在binlog中有一个QUERY_EVENT类型的BEGIN。
2、在statement格式中，具体执行的SQL语句会保存在该事件中。
3、对于ROW格式的binlog，所有DDL操作以文本的形式记录在该事件中。

STOP_EVENT: 当数据库停止时写入。

ROTATE_EVENT: 在数据库日志切换到另一个日志文件时写入,如执行flush logsh或者当前日志文件超过了 max_binlog_size设置的最大值时。

INTVAR_EVENT: 该事件在语句使用了AUTO_INCREMENT 列或者LAST_INSERT_ID()函数时写入,它只会在QUERY_EVENT事件前写入，并且在基于行的日志记录中不会出现,该事件有2个子类型 INSERT_ID_EVENT及LAST_INSERT_ID_EVENT。

LOAD_EVENT: MySQL 3.23中，在使用了LOAD DATA INFILE 语句时写入，即从外部文件导入数据。

SLAVE_EVENT: 该事件还未被使用。

RAND_EVENT: 每次使用 RAND() 函数时写入，它会在QUERY_EVENT事件前写入，并且在基于行的日志记录中不会出现。

USER_VAR_EVENT: 每次语句使用了用户变量后写入，它会在QUERY_EVENT事件前写入，并且在基于行的日志记录中不会出现。

FORMAT_DESCRIPTION_EVENT: 该事件在日志文件的开头写入，MySQL 5.0之前使用的是START_EVENT_V3事件。

XID_EVENT: 在使用了事务后写入该事件。

TABLE_MAP_EVENT: 基于行的日志记录生效，该事件在每个行事件之前，将表的定义映射成一个数字，如主从中表的结构不一样该事件使主从得以可行。

BEGIN_LOAD_QUERY_EVENT: MySQL 5.0 时使用了LOAD DATA INFILE语句后写入。

WRITE_ROWS_EVENT: 对单张表进行插入时写入，基于行的日志记录时生效。

UPDATE_ROWS_EVENT: 对单张表进行更新时写入，基于行的日志记录时生效。

DELETE_ROWS_EVENT: 对单张表进行删除时写入，基于行的日志记录时生效。

INCIDENT_EVENT: 该事件在主库中发生异常，提醒从库主库可能发生会导致数据不一致的事件。

HEARTBEAT_LOG_EVENT: 由主库发出以让从库知道主库存活，不会记录在日志中。

一些事件是没有类型代码的，因为他是其他事件的基类，如Log_event ，这些并不会写在日志文件中。

一些事件可能被分配多个类型代码，如Load_log_event 代表LOAD_EVENT 和NEW_LOAD_EVENT 两种事件

所有的类型请参见官方文档: https://dev.mysql.com/doc/dev/mysql-server/latest/namespacemysql_1_1binlog_1_1event.html

binlog 日志格式

针对不同的使用场景，Binlog 也提供了可定制化的服务，提供了三种模式来提供不同详细程度的日志内容。

Statement 模式：基于 SQL 语句的复制(statement-based replication-SBR)
Row 模式：基于行的复制(row-based replication-RBR)
Mixed 模式：混合模式复制(mixed-based replication-MBR)

Statement 模式

保存每一条修改数据的SQL。

该模式只保存一条普通的SQL语句，不涉及到执行的上下文信息。

因为每台 MySQL 数据库的本地环境可能不一样，那么对于依赖到本地环境的函数或者上下文处理的逻辑 SQL 去处理的时候可能同样的语句在不同的机器上执行出来的效果不一致。

比如像 sleep()函数，last_insert_id()函数，等等，这些都跟特定时间的本地环境有关。

Row 模式

MySQL V5.1.5 版本开始支持Row模式的 Binlog，它与 Statement 模式的区别在于它不保存具体的 SQL 语句，而是记录具体被修改的信息。

比如一条 update 语句更新10条数据，如果是 Statement 模式那就保存一条 SQL 就够，但是 Row 模式会保存每一行分别更新了什么，有10条数据。

Row 模式的优缺点就很明显了。保存每一个更改的详细信息必然会带来存储空间的快速膨胀，换来的是事件操作的详细记录。所以要求越高代价越高。

Mixed 模式

Mixed 模式即以上两种模式的综合体。

在 Mixed 模式中，一般的更新语句使用 Statement 模式来保存 Binlog，但是遇到一些函数操作，可能会影响数据准确性的操作则使用 Row 模式来保存。这种方式需要根据每一条具体的 SQL 语句来区分选择哪种模式。

MySQL 从 V5.1.8 开始提供 Mixed 模式，V5.7.7 之前的版本默认是Statement 模式，之后默认使用Row模式，但是在 8.0 以上版本已经默认使用 Mixed 模式了。

binlog 分析工具

mysqlbinlog: 这是MySQL自带的一个工具，可以用于读取binlog并将其转换为可读的SQL语句。详细的使用教程参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

mysqldump：这是MySQL自带的一个工具，可以用于从binlog中导出数据。

MySQL Enterprise Backup：是MySQL官方提供的备份和恢复工具，可以使用该工具分析binlog。

Percona Toolkit：这是一款开源的工具，可以用于分析binlog并生成报告。

Maxwell: 能实时读取MySQL二进制日志binlog，并生成 JSON 格式的消息，作为生产者发送给 Kafka，Kinesis、RabbitMQ、Redis、Google Cloud Pub/Sub、文件或其它平台的应用程序。

MyFlash: 美团点评的开源MySQL闪回工具。github: https://github.com/Meituan-Dianping/MyFlash

binlog2sql: binlog2sql是一个开源的Python开发的MySQL Binlog解析工具。根据选项不同，可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。github: https://github.com/danfengcao/binlog2sql

my2sql: go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。它基于my2fback、binlog_rollback工具二次开发而来。github: https://github.com/liuhr/my2sql

my2fback: my2fback 实现了基于row格式binlog的回滚闪回功能，让误删除或者误更新数据，可以不停机不使用备份而快速回滚误操作。也可以解释binlog（支持非row格式binlog）生成易读的SQL。github: https://github.com/WangJiemin/my2fback

binlog_rollback: 回滚/闪回，前滚， DML分析报告， DDL信息。github: https://github.com/GoDannyLai/binlog_rollback

binlog_inspector: 回滚/闪回，前滚，分析各表DML情况，找出长事务与大事务。github: https://github.com/2892931976/binlog_inspector

analysis_binlog: 一个可以快速分析 MySQL Binlog 的工具,基于业务表分析统计各个表的 DML 的次数。统计各个业务表的最后访问时间。统计各表 DML 数量和总的 DML数量。统计该 Binlog 的事务总数。基于业务表的 Binlog to SQL。项目地址：https://gitee.com/mo-shan/analysis_binlog

欢迎加入 DevXTalk 的技术交流群,在这里你可以和大佬们一起交流分享技术,行业经验,提升自我。结识新的业内朋友，扩大自己的圈子，当你想换工作的时候也许就能拿到不错的 offer 。总之期待你的加入...

「看完本文有收获？转发给更多的朋友吧」

「DevXTalk 不止于技术」

ITPub博客

?成为数据治理专家：深入解析 MySQL Binlog