MySQL 的 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，我们以此为例来开始今天的演示。如果小伙伴们还不懂 docker 的使用，可以在公众号后台回复 docker，有松哥写的教程。

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

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

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

可以看到，这个 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 目录下。

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

# 这个参数表示启用 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

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

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

docker restart mysql1

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

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

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

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

2.2.2 修改 binlog_format

binlog_format 有几种不同的改法：

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

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

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

这是一个永久性的修改。

3. 常见 binlog 操作

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

• 查看所有 binlog 日志

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

show master logs;

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

• 查看 master 状态

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

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

• 刷新 binlog

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

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

• 重置 binlog

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

• 查看 binlog

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

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

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

虽然看起来乱糟糟的，不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库，里边只是创建了一个名为 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';

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

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

这其实就是 Row 格式的 binlog。

4. 小结

好啦，今天这篇文章主要是和小伙伴们分享了 MySQL 的 binlog 日志，主要是一些理论知识，下篇文章松哥将通过两个具体的案例，来和大家演示不同的 binlog_format 存在的问题。

本文转载自微信公众号「江南一点雨 」，可以通过以下二维码关注。转载本文请联系江南一点雨公众号。