MySQL binlog中的事件类型详解

简介

MySQL BINLOG是MySQL数据库的事务日志，用于记录数据库中所有修改数据的SQL语句。它的主要作用是在主从数据库同步时，将主库上的事务日志传输到从库，从而实现数据库的高可用性和异地容灾。备份和数据恢复也依赖于 BINLOG。

BINLOG 是由一系列的事件(Event)构成，每个 Event 记录了MySQL执行中的重要事件。在 MySQL 5.6 中，MySQL BINLOG一共有 13 类事件(Event)，每个事件的格式不一样，它们都用特定格式将数据记录到 event 数据库表中。

本文将详细讲解 MySQL BINLOG 中的所有事件类型及其格式，以及如何使用 BINLOG 进行数据库备份和恢复。同时，文章将提供两个示例说明，以帮助读者更好地理解 BINLOG。

MySQL BINLOG 中的事件类型

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
12. Rand Event
13. User Var Event

1. Query Event

Query Event 记录了在 MySQL 服务器上执行的语句。该事件类型的格式为：

# Query
# 时间戳  ServerId     SQL状态标志         数据库名
# 格式内容 13 38        0       00      00      00      00      06      db01    
# SQL 语句内容
# 格式内容 2f 73 65 6c 65 63 74 20 2a 20 66 72 6f 6d 20 74 5f 6d 61 6c 65 20 77 68 65 72 65 20 74 5f 6d 61 6c 65 2e
SELECT * from t_male where t_male.age > 30

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“SQL状态标志”是一个字节的标志，指示执行结果，“数据库名”标记了 SQL 语句的目标数据库。

2. Stop Event

Stop Event 事件是为了记录 MySQL 数据库服务器的停机时间点，在从库中由 I/O 线程使用。当主库停机，从库需要等待某些时间后再尝试重新连接主库。该事件类型的格式为：

# 时间戳  ServerId o
# 格式内容 3b 31
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“空内容”是用于占位的模板内容，表示空的 Stop Event 事件。

3. Rotate Event

Rotate Event 事件会在 MySQL 的日志文件轮换时出现。当 Binary Log 的容量到达某个值，MySQL 服务器会自动保存旧的 Binary Log 并重新启动一个新的 Binary Log 文件。Rotate Event 记录了这个操作，它标志着一个开始写入的新的日志文件的名字和位置。该事件类型的格式为：

# Rotate
# 时间戳  ServerId offset  文件名
# 格式内容 2d 61 01 00  00 00 00 00  2e 2f 76 61 72 2f 6c 6f 67 2f 6d 79 73 71 6c
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“offset”代表下一条事件在当前日志文件的位置，“文件名”标志着新的日志文件的名字。

4. Intvar Event

Intvar Event 记录了一个整数变量的值，包括自增 ID 的编号等等。该事件类型的格式为：

# Intvar
# 时间戳  ServerId     Intvar ID      Intvar Value
# 格式内容 74 44        01 00 00 00   08 00 00 00
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“Intvar ID”指定了整型变量的类型，“Intvar Value”是这个整型变量的值。

5. Load Event

Load Event 十分简单，用于记录由 LOAD DATA INFILE 命令进行的数据文件导入。该事件类型的格式为：

# Load
# 时间戳  ServerId     数据库名        表名
# 格式内容 77 3c        03 74 65 73 74  01 74 01 00 00 00 00 00 00 
# 数据文件引用目录
# 格式内容 2f 76 61 72 2f 6c 6f 67 2f 6d 79 73 71 6c 2f 2e 2e 2f 74 65 73 74 2e 74 78 74
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“数据库名”和“表名”指定了数据录入的位置，“数据文件引用目录”是指向数据文件的位置，“空内容”用于占位。

6. Slave Event

Slave Event 记录了从库执行的操作的结果，以及与主库连接的状态。该事件类型的格式为：

# Slave
# 时间戳          ServerId     处理状态              主库链接达到时间
# 格式内容 22 1f 04 00 00 00 00 00 00                    5f 14 0d 9c b2 c5 05 00 00
# 查询内容
# 格式内容 6d 79 73 71 6c 20 73 6c 61 76 65
# 查询结果
# 格式内容 00 00 00 08

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“处理状态”为一个字节的标志，它记录了从库对主库的回应，“主库链接达到时间”指的是从库与主库的连接时间，“查询内容”是处理的 SQL 语句，“查询结果”表示 SQL 执行后的状态。

7. Create File Event

Create File Event 事件由使用 CREATE TABLESPACE 或者 CREATE LOGFILE GROUP 语句创建文件进行的操作触发。该事件类型的格式为：

# Create_file
# 时间戳  ServerId     文件 ID         文件名
# 格式内容 5f 28        01 00 00 00    2f 75 73 72 2f 73 68 61 72 65 2f 6d 79 73 71 6c 2f 65 76 65 6e 74 73 3f 3f 
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“文件 ID”指定了新文件的类型和它所代表的文件 ID，“文件名”为文件在系统中的路径，“空内容”用于占位。

8. Append Block Event

Append Block Event 将日志数据附加到某个已经存在的文件中。该事件类型的格式为：

# Append_block
# 时间戳  ServerId     文件指针         内容长度
# 格式内容 7a 1c        00 00 00 00     10 27 00 00 
# 数据块
# 格式内容 04 fe 00 42 0a dc af a6 d6 fa 01 00 39 ce 07 00 
# 校验和
# 格式内容 8a f2 19 7f 

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“文件指针”指向已经存在的文件的位置，“内容长度”表示附加的数据块长度，“数据块”即为附加的日志数据，“校验和”是一些检验和。

9. Exec Load Event

Exec Load Event 是加载器执行 LOAD DATA 语句后产生的事件。该事件类型的格式为：

# Exec_load
# 时间戳  ServerId     数据库名        表名
# 格式内容 3b 3a        05 6c 6f 67 61 74  01 74 00 00 00 00 00 00 
# 插入行数
# 格式内容 3f 52 14 00 
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“数据库名”和“表名”指定了数据录入的位置，“插入行数”表示 INSERT 操作新增的行数，“空内容”用于占位。

10. Delete File Event

Delete File Event 事件由使用 DROP TABLESPACE 或者 DROP LOGFILE GROUP 语句删除文件进行的操作触发。该事件类型的格式为：

# Delete_file
# 时间戳  ServerId     文件 ID         文件名
# 格式内容 37 20        02 b8 7e 13    2f 75 73 72 2f 73 68 61 72 65 2f 6d 79 73 71 6c 2f 65 76 65 6e 74 73 00
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“文件 ID”指定了将要被删除文件的 ID 和其类型，“文件名”为文件在系统中的路径，“空内容”用于占位。

11. New Load Event

New Load Event 是 LOAD DATA 新添加的 Binlog Event 提高了记录的精度，可以看到 LOAD DATA 的详细过程。该事件类型的格式为：

# New_load
# 时间戳  ServerId     文件名长度      文件名
# 格式内容 00 02 16 40  61 62 63 2e 65 78 74 00
# 数据块长度
# 格式内容 01 00 00 00
# 数据块
# 格式内容 31 32 33 34 35 36 0a 

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“文件名长度”是导入数据的文件名的长度，“文件名”是导入数据的文件名，“数据块长度”是导入数据块的长度，“数据块”为记录实际导入的数据。

12. Rand Event

Rand Event 是用来创建一个随机数种子的事件类型，每次执行必须用随机数种子。该事件类型的格式为：

# Rand
# 时间戳          ServerId     随机数种子
# 格式内容 2f 24 04 00 00 00 00 00  fa b8 eb 6a b3 08 d8 34
# 空内容

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“随机数种子”用于生成下一次的插入操作，“空内容”用于占位。

13. User Var Event

User Var Event 记录了 MySQL 中的自定义变量。该事件类型的格式为：

# User_var
# 时间戳  ServerId     变量名长度      变量名
# 格式内容 3d 5e        03 66 6f 6f    00
# 变量值长度
# 格式内容 03 00 00 00
# 变量值
# 格式内容 62 61 72

其中，“#”是注释符号，“时间戳”代表当前事件发生的时间，“ServerId”是服务器的标识符，“变量名长度”和“变量名”指定了自定义变量，“变量值长度”表示自定义变量值的长度，“变量值”为自定义变量的值。

使用 MySQL BINLOG 进行数据备份和恢复

BINLOG 是 MySQL 的事务日志，主要用于记录数据库中所有修改数据的SQL语句。它可以用于数据备份和恢复等应用场景。

数据备份

在进行数据备份的时候，应该首先停止正在进行的 MySQL 服务，然后将 BINLOG 相关文件（Binary Log files）拷贝至备份目录。BINLOG 文件通常位于 MySQL 数据库存储目录下的 /mysql/data 目录中。

数据恢复

使用 BINLOG 进行数据恢复时，需要先完成以下两步操作：

1. 重建数据库：首先，应当清空数据存储目录并重建数据库；
2. 充当主库：然后，将备份资料上传到服务器上，并用此资料来重建主库。

下文给出一个简单的数据恢复示例：

1. 通过 BINLOG 文件找到数据修改语句

找到要恢复的那些数据所在的 BINLOG 文件，然后使用如下命令查找对应的 SQL 语句：

mysqlbinlog --start-position=4 mysql-bin.000001 | grep "UPDATE myuser"

其中，“--start-position=4”代表从第 4 个字节处（即忽略的开头）开始读取 BINLOG 文件，“mysql-bin.000001”为 BINLOG 文件名，“grep”为命令过滤器。在这个例子中，我们用 grep 查找对 myuser 表做的更新操作。

2. 执行 SQL 语句

找到 SQL 语句后，使用 MySQL 命令行工具执行 SQL 语句即可完成数据的恢复。

# 进入MySQL命令行模式
mysql -u root -p
# 执行 SQL 语句
mysql> UPDATE myuser SET Name = 'Mike' WHERE ID = 1;

结论

本文详细讲解了 MySQL BINLOG 中的所有事件类型及其格式，以及如何使用 BINLOG 进行数据库备份和恢复。同时，文章提供了两个示例，帮助读者更好地理解 BINLOG。

在使用 MySQL 时，特别是在进行数据库备份和恢复工作时，BINLOG 的重要性不言而喻。相信通过这篇文章的介绍，读者对 BINLOG 的理解已经更加透彻了。