以前聊过binlog和redo log，没有涉及binlog buffer和redo log buffer，主要是因为在核心脉络的理解上，buffer容易产生干扰。但buffer很重要，所以我们来看一下log和buffer之间的关系。

binlog简介

binlog用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

三种格式

binlog可以设置三种格式：

STATEMENT

STATEMENT格式记录的是日志的逻辑SQL语句。

• 优点：数据量小
• 缺点：为了语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；某些语句和函数如UUID, LOAD DATA INFILE等在复制过程可能导致数据不一致甚至出错

ROW

在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。

• 优点：日志内容会非常清楚的记录下每一行数据修改的细节
• 缺点：可能会产生大量的日志，如update全表，则每一条更改都需要记录

MIXED

MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但在一些情况下会使用ROW格式，可能的情况有：

1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录。

2）用UUID（）、USER（）、CUR-RENT_USER（）、FOUND_ROWS（）、ROW_COUNT（）等不确定函数。

3）使用了INSERT DELAY语句。

4）使用了用户定义函数（UDF）。

5）使用了临时表（temporary table）。

查看

日志格式

我们可用如下命令设置和查看binlog的格式。可按照会话级别设置，也可按照全局级别设置。

mysql> set @@session.binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.binlog_format;
+-------------------------+
| @@session.binlog_format |
+-------------------------+
| STATEMENT               |
+-------------------------+
1 row in set (0.00 sec)
mysql> set global binlog_format='ROW';

文件位置

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.01 sec)
mysql> system ls -lh /usr/local/var/mysql/
total 447360
-rw-r-----    1 bytedance  admin   5.9K  9 20 12:55 binlog.000001
-rw-r-----    1 bytedance  admin    16B  8 13 21:12 binlog.index

binlog.index为二进制索引文件，binlog.000001为二进制日志文件

文件内容

binlog的文件内容为二进制，不能直接查看，须通过MySQL提供的工具mysqlbinlog。

查看STATEMENT格式的命令：

mysqlbinlog --start-position=5910 /usr/local/var/mysql/binlog.000001

查看ROW格式命令：

mysqlbinlog -vv --start-position=3353 --stop-position=4478 /usr/local/var/mysql/binlog.000001

样例如下，可以看到具体的语句。

?  ~ mysqlbinlog -vv --start-position=3353 --stop-position=4478 /usr/local/var/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210813 21:12:05 server id 1  end_log_pos 125 CRC32 0xc29576e3  Start: binlog v 4, server v 8.0.23 created 210813 21:12:05 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
pW8WYQ8BAAAAeQAAAH0AAAABAAQAOC4wLjIzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAClbxZhEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB43aVwg==
'/*!*/;
# at 3353
#210912 12:00:11 server id 1  end_log_pos 4478 CRC32 0x110c4a8c   Query thread_id=21  exec_time=0 error_code=0  Xid = 134
use `testdb`/*!*/;
SET TIMESTAMP=1631419211/*!*/;
SET @@session.pseudo_thread_id=21/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `trace_sp_info2` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sp_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '服务id',
  `sp_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务名称',
  `type` tinyint DEFAULT '0' COMMENT '服务',
  `type_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务类型名称',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0未激活 1激活 2失效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `update_by` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_spid` (`sp_id`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='服务'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

redo log简介

redo log可用来做数据库crash recovery，是数据库保障数据安全的重要功能之一。它记录的关于每个页（Page）的更改的物理情况，一般默认包括2个日志文件，也可通过命令进行配置。

格式

redo log的日志文件，每个页面大小512字节，可通过参数调节。文件中的前四个页面，主要用于管理日志内容及整个数据库状态。在2KB（4*512字节）内容之后，就是正常的用来存储日志内容的部分。

格式如下图所示，图中的数值是指每一项在页面中的偏移位置。

蓝色为4个header页面，黄色为普通页面。在普通页面中中，都会有12个字节用来存储页面头信息，这些信息主要用于管理这个页面本身的数据存储方式。

LOG_BLOCK_HDR_NO：4字节，一个与LSN有关系的块号。

LOG_BLOCK_HDR_DATA_LEN：2字节，表示当前页面中存储的日志长度，这个值一般都等于512-12（12位页面头的大小），因为日志在相连的块中是连续存储的，中间不会存在空闲空间，所以如果这个长度不为500，表示此时日志已经扫描完成（Crash Recovery的工作）。

LOG_BLOCK_FIRST_REC_GROUP：2字节，表示在当前块中是不是有一个MTR的开始位置。因为一个MTR所产生的日志量有可能是超过一个块大小的，那么如果一个MTR跨多个块时，这个值就表示了这个MTR的开始位置究竟是在哪一个块中。如果为0，则表示当前块的日志都属于同一个MTR；而如果其值大于0并且小于上面LOG_BLOCK_HDR_DATA_LEN所表示的值，则说明当前块中的日志是属于两个MTR的，后面MTR的开始位置就是LOG_BLOCK_FIRST_REC_GROUP所表示的位置。

LOG_BLOCK_CHECKPOINT_NO：4字节，存储的是检查点的序号。

这里简单说明一下LSN，LSN全名叫Log Sequence Number，用来精确记录日志位置信息，且是连续增长的。

上面所讲述的就是日志文件的组织结构，只有前面2KB是日志头，后面所有的都是一个个连续的、用来存储MTR产生的日志页面。

MTR

MTR也叫Mini-transaction，被称作“物理事务”，用于保证物理操作的完整性。比如在底层页面插入一条记录，如果只修改页头信息而没有修改页尾信息，那对这个页面来说是不完整的，这就需要用事务进行保证。

MTR有开始与提交阶段，保证物理事务一致性。其作用部位如下图所示：

对页面page进行修改时，MTR会生成redo log record，当MTR提交的时候，会将redo log record拷贝到redo log buffer。而且MTR提交的时候，会给每个log record生成一个lsn，此lsn确定了其在log file中的位置。所以在redo log普通页面中的数据，都会有对应的lsn。

缓存

上面聊完binlog和redo log的基础知识，现在看一下和binlog cache、redo log cache的关系。

write与fsync

把buffer里的数据写到磁盘需要几步？主要分两步，write和fsync。

write：指把buffer中日志写入到文件系统的 page cache，这个操作并没有把数据持久化到磁盘，所以速度比较快，但断电丢失。

fsync：真正将数据持久化到磁盘，不会丢失。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

当然对于buffer中的数据，mysql异常重启就会丢失。

所以write和fsync的时机，控制了binlog和redo log的持久化。

binlog刷盘

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交时，再把 binlog cache 里的完整事务写到 binlog 文件中。

binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

参数 sync_binlog 控制binlog的 write 与 fsync 时机：

1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync。如果主机发生异常重启，会丢失未fsync的 binlog 日志。
2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才fsync。如果主机发生异常重启，会丢失最近N个事务的 binlog 日志。

redo log刷盘

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

innodb_flush_log_at_trx_commit 参数控制redo log的write与fsync：

1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，MySQL异常重启丢失数据；
2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，主机发生异常重启，丢失数据；

当然，对于binlog和redo log刷盘时机，除了参数外还受其它配置控制。如InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

但这些内容会影响我们理解，所以本次主要理解提交过程中的刷盘时机。

流程

以前画过更新一行数据的流程，如下图所示，里面没有涉及binlog cache和redo log cache，这次给增加上。在介绍两阶段提交的时候说过，时序上 redo log 先 prepare， 再写binlog，最后再把 redo log commit。

在sync_binlog 和innodb_flush_log_at_trx_commit 都为1的情况下，更新流程如下图所示：

对redo log的两个阶段prepare和commit需要说明一下：

• 在prepare阶段执行write和fsync操作，redo log完成了持久化
• 在commit阶段只执行write操作，这是因为redo log在prepare阶段已经持久化，只是状态未变更为commit。如果发生异常，根据binlog的写入情况，是能够实现数据恢复的。大家如果忘记这部分内容，可以重看一下InnoDB redo、undo、binlog，是如何合作的

所以大家能够发现，sync_binlog 和innodb_flush_log_at_trx_commit控制的就是write和fsync操作，哪些在提交过程中执行。

从流程图上看，双1情况，即sync_binlog 和innodb_flush_log_at_trx_commit都为1，MySQL持久化是最及时的。这意味一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog，相应刷盘QPS也会增高。

对于这两个配置如何选择，看大家业务具体情况。

总结

最近事情比较多，写这篇文章用了差不多一个星期，完成之后还是挺开心的。对于自己以前一些模糊的知识点也梳理清晰了。希望这篇文章能够帮助到大家。

资料

1. MySQL探秘(三):InnoDB的内存结构和特性
2. MySQL共享表空间概念
3. linux 同步IO: sync、fsync与fdatasync
4. Mysql Binlog三种格式详细介绍
5. MySQL binlog格式解析
6. MySQL redo log 格式解析
7. redo log日志文件
8. mysqlbinlog基于某个偏移量进行数据的恢复(重做),--start-position,--stop-position的使用方法
9. mysqlbinlog 技巧
10. MySQL redo log及recover过程浅析

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

往期文章回顾：

1. 设计模式
2. 招聘
3. 思考
4. 存储
5. 算法系列
6. 读书笔记
7. 小工具
8. 架构
9. 网络
10. Go语言