牛哇！MySQL中的日志“binlog”的三种格式这么好玩

￡神魔★判官ぃ 2023-09-28 11:21 27阅读 0赞

MySQL 中的日志比较重要的有 binlog（归档日志）、redo log（重做日志）以及 undo log，那么跟我们本文相关的主要是 binlog，另外两个日志松哥将来有空了再和大家详细介绍。

## 1. binlog ##

binlog 我们中文一般称作归档日志，如果大家看过松哥之前发的 MySQL 主从搭建，应该对这个日志有印象，当我们搭建 MySQL 主 从 的时候就离不开 binlog（传送门：MySQL8 主从复制踩坑指南）。

binlog 是 MySQL Server 层 的日志，而不是存储引擎自带的日志，它记录了所有的 DDL 和 DML(不包含数据查询语句)语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等，需要注意的是：

*  binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）。
 *  binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。
 *  一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间。

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：

*  MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通 过 binlog 来同步数据，进而实现主机和从机的数据同步。
 *  MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

## 2. 配置 binlog ##

首先我们在 docker 中安装好 MySQL，然后进入到容器中，通过如下命令可以查看 binlog 是否开启：

![898d4a1adea6881892b410c9129b1c45.png][]

这个 OFF 就表示 binlog 是一个关闭状态，没有开启。

通过以下命令可以查看 binlog 日志的格式，如下：

![e3a7a461c41924dfea73c0183e0fef35.png][]

可以看到，这个 binlog 的格式为 ROW。

这里就涉及到一个问题，binlog 的格式。

## 2.1 binlog 的格式 ##

binlog 有三种格式：

*  Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
 *  Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
 *  Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

## 2.1.1 Statement ##

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。

但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

所以使用 Statement 格式会出现一些数据一致性问题。

## 2.2.2 Row ##

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

## 2.2.3 Mixed ##

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。

Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

## 2.2 配置 ##

接下来我们来看看 binlog 的配置。

## 2.2.1 开启 binlog ##

开启 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf，该文件在容器的 /etc/mysql/mysql.conf.d 目录下。

![8a4d7eb61fbee82720aced256bd7c599.png][]

针对该配置文件，我们做如下修改：

# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
    log-bin=javaboy_logbin
    
    # 设置一个 binlog 文件的最大字节
    # 设置最大 100MB
    max_binlog_size=104857600
    
    # 设置了 binlog 文件的有效期（单位：天）
    expire_logs_days = 7
    
    # binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
    #binlog-do-db=javaboy_db
    
    # binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
    #binlog-ignore-db=javaboy_no_db
    
    # 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
    # 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
    sync_binlog=0
    
    # 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
    server-id=1

各项配置的含义松哥已经在注视中说明了。截图如下：

![e2758a085dd6b38a30043cb7c31d8dde.png][]

配置完成后，执行如下命令重启 mysql 容器（mysql1 是我这里容器的名字）：

docker restart mysql1

重启之后，再次执行 show variables like 'log\_bin%'; 即可看到 binlog 已经开启了。

![dbb5512af8baa15b405f442f3a3d9851.png][]

这里除了 log\_bin 变量外，还有两个变量名也值得我们关注：

*  log\_bin\_basename：这个是将来产生的 binlog 日志文件的名称前缀，换句话说，根据大家目前所看到的配置，将来产生的 binlog 日志文件名为 javaboy\_logbin.xxx，这个文件中将会用来记录所有的 DDL 和 DML 语句事件。
 *  log\_bin\_index：这个是 binlog 的索引文件，保存了所有 binlog 的目录，因为 binlog 可能会有多个。我们可以来查看一下现在的 javaboy\_logbin.index 文件：

![600b56e7e2d4f565a9d62f72f9262c70.png][]

可以看到，目前只有一个 logbin 文件。

## 2.2.2 修改 binlog\_format ##

binlog\_format 有几种不同的改法：

修改当前会话的 binlog\_format，这个修改只针对当前会话有效：

![d618eef119f5a2a0665f1fadfa4119d0.png][]

也可以修改全局的 binlog\_format，这个修改，当 MySQL 重启之后，会失效：

![afb5b054220e7bc678733c59e813f4a3.png][]

如果想一劳永逸搞定这事，可以修改  
/etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，在配置文件中，添加 binlog\_format 选项，如下：

![7063382104fb3c5223bc3048ca72cea9.png][]

这是一个永久性的修改。

## 3. 常见 binlog 操作 ##

接下来我们再来介绍几个常见的 binlog 操作命令。

1.  **查看所有 binlog 日志**

通过如下方式我们可以查看 binlog 日志列表：

show master logs;

![09ee145ef01b16d09170fdbddd62f446.png][]

可以看到，我这里目前只有一个日志文件，文件名为 javaboy\_logbin.000001，File\_size 表示这个文件占用的字节大小是 154。

1.  **查看 master 状态**

这个命令我们在搭建 MySQL 主从的时候经常会用到，如下：

![007fb89bfa0efae25a3afb6731d82a21.png][]

这个时候可以看到最新的 binlog 日志文件名称以及最后一个操作事件的 Position 值（这个值有啥用，我们后面会给大家详细介绍）。

1.  **刷新 binlog**

正常来说，一个 binlog 写满之后，会自动切换到下一个 binlog 开始写，不过我们也可以执行一个 flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：

![6f4f8deea36ea1a77b0b8e49b6196d8b.png][]

由上图可以看到，我们刷新日志之后，再通过 show master logs 去查看日志，发现日志文件已经多了一个新产生的了，然后再通过 show master status 去查看最新的日志文件信息，发现也已经变为 javaboy\_logbin.000002。

1.  **重置 binlog**

reset master 可以重置 binlog 日志文件，让日志重新从 000001 开始记录，不过如果当前主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的错误）。

![10a4dac735cec3562cc1106671af9075.png][]

1.  **查看 binlog**

由于 binlog 是二进制日志文件，所以要是直接打开，那肯定是看不了的：

![bcd241991015ffba713a7f20cf037b7f.png][]

没有看到任何有用的信息。

为了查看 binlog，MySQL 为我们提供了两个官方工具，我们一个一个来看，首先是 mysqlbinlog 命令，如下：

![74f1179bb2d145d1b0f80bc13ea62025.png][]

虽然看起来乱糟糟的，不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库，里边只是创建了一个名为 javaboy 的库，然后创建了一个名为 user 的表加了两条数据，其他什么事情都没做，所以创建库的脚本我们其实能够从纷杂的文件中找到。

产生的日志文件中有一个 end\_log\_pos 是日志文件的 pos 点，这个将来在数据恢复的时候有用。

不过这种查看方式不够人性化，我们说 binlog 是按照事件来记录日志的，所以如果我们能够按照事件的方式查看日志，就会好很多，我们再来看看如下一个命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

这个表示以事件的方式来查看 binlog，这里涉及到几个参数：

*  log\_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
 *  pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
 *  offset：这是是偏移量，不指定默认就是 0。
 *  row\_count：查看多少行记录，不指定就是查看所有。

我们来看一个简单的例子：

show binlog events in 'javaboy_logbin.000001';

![65edbcf1100e4442d8a4735bc00ca462.png][]

这下就清晰多了，我们可以看到之前的所有操作，例如：

*  在 Pos 219-322 之间创建了一个库。
 *  在 Pos 387-537 之间创建了一张表。
 *  在 Pos 677-780 之间添加了一条记录。
 *  ...

这其实就是 Row 格式的 binlog。

## 5. 小结 ##

好啦，今天这篇文章主要是和小伙伴们分享了 MySQL 的 binlog 日志，主要是一些理论知识

![8529b16b2b9e497b9beb8e2be9694530.jpeg][]

[898d4a1adea6881892b410c9129b1c45.png]: https://img-blog.csdnimg.cn/img_convert/898d4a1adea6881892b410c9129b1c45.png
[e3a7a461c41924dfea73c0183e0fef35.png]: https://img-blog.csdnimg.cn/img_convert/e3a7a461c41924dfea73c0183e0fef35.png
[8a4d7eb61fbee82720aced256bd7c599.png]: https://img-blog.csdnimg.cn/img_convert/8a4d7eb61fbee82720aced256bd7c599.png
[e2758a085dd6b38a30043cb7c31d8dde.png]: https://img-blog.csdnimg.cn/img_convert/e2758a085dd6b38a30043cb7c31d8dde.png
[dbb5512af8baa15b405f442f3a3d9851.png]: https://img-blog.csdnimg.cn/img_convert/dbb5512af8baa15b405f442f3a3d9851.png
[600b56e7e2d4f565a9d62f72f9262c70.png]: https://img-blog.csdnimg.cn/img_convert/600b56e7e2d4f565a9d62f72f9262c70.png
[d618eef119f5a2a0665f1fadfa4119d0.png]: https://img-blog.csdnimg.cn/img_convert/d618eef119f5a2a0665f1fadfa4119d0.png
[afb5b054220e7bc678733c59e813f4a3.png]: https://img-blog.csdnimg.cn/img_convert/afb5b054220e7bc678733c59e813f4a3.png
[7063382104fb3c5223bc3048ca72cea9.png]: https://img-blog.csdnimg.cn/img_convert/7063382104fb3c5223bc3048ca72cea9.png
[09ee145ef01b16d09170fdbddd62f446.png]: https://img-blog.csdnimg.cn/img_convert/09ee145ef01b16d09170fdbddd62f446.png
[007fb89bfa0efae25a3afb6731d82a21.png]: https://img-blog.csdnimg.cn/img_convert/007fb89bfa0efae25a3afb6731d82a21.png
[6f4f8deea36ea1a77b0b8e49b6196d8b.png]: https://img-blog.csdnimg.cn/img_convert/6f4f8deea36ea1a77b0b8e49b6196d8b.png
[10a4dac735cec3562cc1106671af9075.png]: https://img-blog.csdnimg.cn/img_convert/10a4dac735cec3562cc1106671af9075.png
[bcd241991015ffba713a7f20cf037b7f.png]: https://img-blog.csdnimg.cn/img_convert/bcd241991015ffba713a7f20cf037b7f.png
[74f1179bb2d145d1b0f80bc13ea62025.png]: https://img-blog.csdnimg.cn/img_convert/74f1179bb2d145d1b0f80bc13ea62025.png
[65edbcf1100e4442d8a4735bc00ca462.png]: https://img-blog.csdnimg.cn/img_convert/65edbcf1100e4442d8a4735bc00ca462.png
[8529b16b2b9e497b9beb8e2be9694530.jpeg]: https://img-blog.csdnimg.cn/8529b16b2b9e497b9beb8e2be9694530.jpeg