我们前面提到的 redo log、undo log 是 InnoDB 存储引擎特有的日志，MySQL 也有其自身的日志，即无论使用哪个存储引擎都会有该日志，这个日志就是 binlog，即二进制日志。

binlog 是一种记录所有 MySQL 数据库表结构变更以及表数据变更的二进制日志，但是不会记录诸如 SELECT 和 SHOW 这类查询操作的日志，因为这类操作对数据本身并没有修改。此外，binlog 还记录了执行数据库更改操作的时间等其他额外信息。总的来说，binlog 有以下两个最重要的使用场景：

  • 主从复制（replication）：主数据库把 binlog 发送至从数据库，从数据库获取 binlog 后将数据同步至从数据库中，从而达到主从数据库数据的一致性。

  • 数据恢复（recovery）：当 MySQL 数据库发生故障或者崩溃时，可以通过 binlog 进行数据恢复。例如，在一个数据库全备文件恢复后，用户可通过二进制日志进行 point-in-time 的恢复。

需要注意的是，MySQL 默认不开启 binlog，可通过在 my.cnf 配置文件中指定 log-bin=[filename] 参数来启动二进制日志。如果不指定名称，则默认 binlog 文件名为主机名，文件路径为数据库目录（datadir）。开启二进制日志时，还需要指定 server-id 属性，否则 MySQL 启动会报错：

  1. log-bin=mysqlbinlog
  2. server-id=1

生成的 binlog 日志文件如下：
image.png
这里的 mysqlbinlog.000001 即为二进制日志文件，文件名为我们在配置文件中配置的文件名，后缀名为二进制日志的序列号。mysqlbinlog.index 为 binlog 的索引文件，用来存储过往产生的二进制日志序号。

binlog 与 redo log 区别

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。binlog 和 redo log 在一定程度上都能恢复数据，但是二者有着本质的区别，具体内容如下：

  • binlog 是 MySQL 本身就拥有的，不管使用何种存储引擎，binlog 都存在，而 redo log 是 InnoDB 存储引擎特有的，只有 InnoDB 存储引擎才会输出 redo log。


  • binlog 是一种逻辑日志，记录的是这个语句的原始逻辑，比如 “给 ID=2 这一行的 c 字段加 1”。而 redo log 是一种物理日志，记录的是 “在某个数据页上做了什么修改”。


  • redo log 具有幂等性，多次操作的前后状态是一致的，而 binlog 不具有幂等性，记录的是所有影响数据库的操作。例如插入一条数据后再将其删除，则 redo log 前后的状态未发生变化，而 binlog 就会记录相应的插入操作和删除操作。


  • binlog 只会在事务提交时一次性写入，其日志的记录方式与事务的提交顺序有关，并且一个事务的 binlog 中间不会插入其他事务的 binlog。而 redo log 记录的是物理页的修改，最后一个提交的事务记录会覆盖之前所有未提交的事务记录，并且一个事务的 redo log 中间会插入其他事务的 redo log。


  • binlog 是追加写入，写完一个日志文件再写下一个日志文件，不会覆盖使用，而 redo log 是循环写入，日志空间的大小是固定的，会覆盖使用。


  • binlog 一般用于主从复制和数据恢复，并且不具备崩溃自动恢复的能力，而 redo log 是在服务器发生故障后重启 MySQL，用于恢复事务已提交但未写入数据表的数据。

binlog 写入过程

其实 binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache（用于缓存 binlog 的内存缓冲区）中，等到事务提交时，再把 binlog cache 写到 binlog 文件中。注意，这里是每个事务线程都有一个自己的缓冲区。一个事务的 binlog 不能被拆分，因此不论这个事务多大，也会确保一个事务中产生的 binlog 要被一次性写入到磁盘中，所以一个事务的 binlog 是完整的，中间不会插入其他事务的 binlog。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交时，执行器会把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。
image.png
可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

  • 图中的 write 是指把日志写入到文件系统的 page cache，但并没有把数据持久化到磁盘。
  • 图中的 fsync 才是将数据持久化到磁盘的操作。一般我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

  • sync_binlog = 0 时，表示每次提交事务都只 write，不 fsync，fsync 交由操作系统去实现。
  • sync_binlog = 1 时，表示每次提交事务都会执行 fsync。
  • sync_binlog = N(N>1) 时，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。但我建议你设置成 1，这样可以保证 MySQL 异常重启后 binlog 不丢失。

1. 二阶段提交

MySQL 事务在提交的时候，会记录事务日志和二进制日志，也就是 redo log 和 binlog。这里就存在一个问题：对于事务日志和二进制日志，MySQL 会先记录哪种呢？我们通过下面这个语句，来看一下 MySQL 在执行这个简单的 UPDATE 语句时的内部流程：

  1. mysql> update T set c=c+1 where ID=2;

执行流程如下图所示：
image.png
可以看到，MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交。两阶段提交的目的是为了让两份日志之间的逻辑一致。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者反过来。那这两种方式会有什么问题呢？假设在执行 UPDATE 语句的过程中在写完第一个日志后，第二个日志还没写完时发生了 crash，会出现什么情况？

假设先写 redo log，那么当 redo log 写完，binlog 还没有写完时发生了 crash。因为 MySQL 崩溃恢复时依赖的是 redo log 做数据恢复，所以恢复后存在这条更新语句。但由于 binlog 没写完就 crash 了，所以 binlog 里就没有这条语句。因为 MySQL 数据复制依赖的是 binlog，所以如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的数据就会与原库不同。

假设先写 binlog，那么当 binlog 写完，redo log 还没有写完时发生了 crash。崩溃恢复后这个事务是无效的。但 binlog 里已经记录了这个改动。所以，在之后用 binlog 来恢复时就多了一个事务出来，也与原库数据不同。

两阶段提交是怎么保证逻辑一致的呢？

当未开启 binlog 时，如果要执行一条 UPDATE 语句，MySQL 会先写 redo log buffer（便于事务回滚），然后再在 Buffer Pool 中修改对应的缓存页，当准备提交事物时会把 redo log 刷新到磁盘，然后事务就提交了。如果开启 binlog 后，我们就不能简单地写完 redo log 就提交事务了，否则 redo log 与 binlog 之间的逻辑是不一致的。此时，写完 redo log 文件后并不直接提交事务，而是将事务标记为处于 prepare 阶段，等到 binlog 也写入到文件后，再将事务标记为 commit 状态，表示可以提交事务了，此时才会提交事务。

当 binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复后 MySQL 如何处理？

MySQL 在崩溃恢复时会判断 redo log 中记录的事务日志是否完整，即是否有 commit 标识。如果有 commit 标识则直接提交事务，如果没有则需要判断对应的事务在 binlog 上是否存在并完整。如果在 binlog 上是完整的则也要提交事务（因为 binlog 已经写入了，之后会被从库用，所以主库也要提交这个事务），否则回滚事务。因此如果在上图中的时刻 B 发生了 crash，崩溃恢复后该事务会被提交。

因为每个事务都有一个唯一的事务 id，redo log 和 binlog 在记录日志时都会关联相应的事务 id，所以 redo log 和 binlog 就通过事务 id 关联了起来。当判断 binlog 是否完整时，可以检查其格式：

  • statement 格式的 binlog 最后会有 COMMIT；

image.png

  • row 格式的 binlog 最后会有一个 XID event。

image.png
另外，在 MySQL 5.6.2 版本后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 值来发现。

redo 与 binlog 的刷盘时机

在两阶段提交过程中，时序上 redo log 先 prepare，再写 binlog 文件，最后再把 redo log 修改为 commit。这个过程中 redo log 文件需要修改两次。如果把 innodb_flush_log_at_trx_commit 参数设置成 1，那么 redo log 在 prepare 阶段就要进行一次持久化，由于崩溃恢复逻辑可以依赖于 prepare 的 redo log 加上 binlog 来恢复，以及每秒一次的后台轮询对 redo log 的刷盘操作。因此，InnoDB 认为 redo log 在 commit 时就不需要再 fsync 了，只 write 到文件系统的 page cache 中就够了，所以，redo log 的 commit 阶段就不会刷盘了。

通常所说的 MySQL 的双 1 配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

能否只用 redo log 不要 binlog？

如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交的过程了，而系统依然是 crash-safe 的。但 binlog 有着 redo log 无法替代的功能。

  • 一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
  • 一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有一些数据分析系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。

总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog，所以单靠 redo log 还做不到。

2. binlog 组提交机制

若事务为非只读事务，则每次事务提交时需要进行一次 fsync 操作，以保证 redo log 都写入了磁盘。为了提高磁盘 fsync 的效率，MySQL 提供了组提交（group commit）功能，即一次 fsync 能够将多个事务的日志刷新到磁盘的日志文件中，而不用将每个事务的日志单独刷新到磁盘文件中，从而大大提升了日志刷盘的效率。

我们知道，如果开启了 binlog，则 MySQL 为了保证 binlog 和事务日志的一致性，使用了两阶段提交。在两阶段提交写 binlog 的过程中，实际上是分成两步的：

  • 先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件；
  • 调用 fsync 持久化。

下图详细展示了在二阶段提交过程中的日志写入时机：
image.png
MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的操作拖到了步骤 3 中。这么一来，binlog 也可以进行组提交了，因为在 binlog 的 write 和 fsync 操作之间有了一小段间隔，这允许其他提交的事务也将 binlog write 到操作系统缓存中，后续通过 fsync 一并刷新到磁盘中。这种实现方式称为二进制日志组提交（Binary Log Group Commit，BLGC）。

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 操作的间隔时间很短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。如果想提升 binlog 组提交的效果，可设置如下参数：

  • binlog_group_commit_sync_delay：表示延迟多少微秒后才调用 fsync，默认为 0
  • binlog_group_commit_sync_no_delay_count：表示累积多少次以后才调用 fsync，默认为 0

image.png
这两个条件是或的关系，即只要有一个满足条件就会调用 fsync 操作。注意，除非有大量的事务不断地进行写入和更新操作，否则不建议修改这个变量的值，这是因为修改后可能会导致事务的响应时间变长。

binlog 记录格式

在 binlog 文件中主要有三种记录模式，分别为：Row、Statement 和 Mixed。其中，Mixed 格式其实就是前两种格式的混合。binlog 的日志格式通过 binlog_format 参数进行控制，默认为 Row 格式：
image.png
二进制日志文件的文件格式为二进制，因此不能直接用 cat、head、tail 等系统命令来查看。要想查看二进制日志文件的内容，必须通过 MySQL 提供的工具 mysqlbinlog 来解析。

1. Row

Row 模式下的 binlog 文件会记录每一行数据被修改的情况，然后在 MySQL 从数据库中对相同的数据同步进行修改。当使用 mysqlbinlog 工具解析 Row 格式的二进制日志时，需要加上 -v 或 -vv 参数，-vv 会比 -v 多显示出更新的类型，否则日志输出的信息是不可读的。
image.png
可以看到，在 ROW 格式下，一个简单的 update 语句记录了对于整个行更改的信息。

Row 模式的优点是能够非常清楚地记录每一行数据的修改情况，完全实现主从数据库的同步和数据的恢复。但是 Row 模式的缺点是如果主数据库中发生批量操作，尤其是大批量的操作，会产生大量的二进制日志。比如，使用 alter table 操作修改拥有大量数据的数据表结构时，会使二进制日志的内容暴涨，产生大量的二进制日志，从而大大影响主从数据库的同步性能。

2. Statement

Statement 模式下的 binlog 文件会记录每一条修改数据的 SQL 语句，MySQL 从数据库在复制 SQL 语句时会通过 SQL 进程将 binlog 中的 SQL 语句解析成和 MySQL 主数据库上执行过的 SQL 语句相同的 SQL语句，然后在从数据库上执行该 SQL 语句。对于 Statement 格式的二进制日志文件，在使用 mysqlbinlog 解析后看到的就是执行的逻辑 SQL 语句，如：
image.png
可以看到，在真实执行的 update 命令之前，还有一个“use user”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 user 库的表 user_info。

Statement 模式的优点是由于不记录数据的修改细节，只是记录数据表结构和数据变更的 SQL 语句，因此产生的二进制日志数据量比较小，这样能够减少磁盘的 I/O 操作，提升数据存储和恢复的效率。Statement 模式的缺点是在某些情况下，可能会导致主从数据库中的数据不一致。例如，在 MySQL 主数据库中使用了 last_ insert_id() 和 now() 等函数，会导致 MySQL 主从数据库中的数据不一致。

3. Mixed

Mixed 模式下的 binlog 是 Row 模式和 Statement 模式的混用。在这种模式下，一般会使用 Statement 模式保存binlog，如果存在 Statement 模式无法复制的操作，例如在 MySQL 主数据库中使用了 last_insert_id() 和 now() 等函数，MySQL 会使用 Row 模式保存 binlog。 也就是说，如果将 binlog 的记录模式设置为 Mixed，MySQL 会根据执行的 SQL 语句选择写入的记录模式。

binlog 相关参数

在 MySQL 中，输入如下命令可以查看与 binlog 相关的参数。

  1. SHOW VARIABLES LIKE '%log _bin%';
  2. SHOW VARIABLES LIKE '%binlog%';

示例如下：
image.png
其中，几个重要的参数如下所示：

  • max_binlog_size：表示单个 binlog 文件的最大值，如果超过该值，则产生新的 binlog 文件，并且后缀名会加一。默认值为 1GB。

  • maxbinlog cache_size：表示 binlog 占用的最大内存。

  • binlog_cache_size：MySQL 执行事务时，所有未提交的二进制日志会被记录到缓冲区中，等该事务提交时直接将缓冲中的二进制日志刷新到磁盘。这个缓冲的大小就是由 binlog_cache_size 决定，默认 32 KB。并且该参数是基于会话的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。

  • binlog_cache_use：表示使用 binlog_cache 的事务数量。

  • binlogcache_disk_use：表示使用 binlog_cache 但超过 binlog_cache size 的值，并且使用临时文件来保存 SQL 语句中的事务数量。