666!MySQL 的 binlog 的三种格式这么好玩!

5,594 阅读9分钟

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,我们以此为例来开始今天的演示。如果小伙伴们还不懂 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 操作命令。

  1. 查看所有 binlog 日志

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

show master logs;

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

  1. 查看 master 状态

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

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

  1. 刷新 binlog

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

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

  1. 重置 binlog

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

  1. 查看 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。

5. 小结

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