今天我们来聊一聊 MySQL 的 binlog 和 redo log。

redo log

redo log(重做日志) 是 InnoDB 引擎特有的日志，处于引擎层，主要负责存储相关的具体事宜。

在 MySQL 中，如果每一次更新操作都需要写入磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程的 IO 成本、查找成本都很高。

为了优化这个操作，MySQL 每次更新只需要更新内存，然后后续在恰当的时候，再把内存中的最新内容更新到磁盘里面。

但内存会有个问题，宕机重启后，内存中的数据就丢失了，为了使 MySQL 具备 "crash-safe" 的能力，InnoDB 采用了 WAL 技术，WAL 是 Write-Ahead Logging，它的关键点是先写日志，再写磁盘。对于 InnoDB，其 WAL 日志就是所谓的 redo log。

即每次更新操作，先写 redo log，然后更新内存，此时更新就算完成了，因为 redo log 是顺序写入，速度很快。InnoDB 引擎会在适当的时候，将操作的记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

有了 redo log，即使宕机重启了，内存中的那部分数据因为被预存到 redo log 中了，所以可以恢复过来，即具备了 "crash-safe" 的能力。

redo log 写入磁盘的全过程:

• 首先将操作日志写入 redo log buffer(内存，调和 CPU 和磁盘写入速度的矛盾)；
• redo log buffer 的数据写入 page cache(操作系统里的页，操作系统会找个时间自动刷到磁盘，或者调用 fsync 函数立即刷到磁盘)；
• page cache 的数据刷到磁盘。

2.jpg

InnoDB 引擎提供了 innodb_flush_log_at_trx_commit 配置来控制上述过程。

• innodb_flush_log_at_trx_commit=1

每次事务结束，强制将数据刷到磁盘，即强制性走完上述3个过程，性能较差，但最安全。

• innodb_flush_log_at_trx_commit=2

每次事务结束，只要保证数据写入到 page cache即可(前2步执行)。此时，MySQL 进程宕掉，但只要操作系统不宕，数据仍然可以恢复，等待后续落盘。

• innodb_flush_log_at_trx_commit=0

每次事务结束，数据写入到 redo log buffer 即可。后台线程会定周期(1秒)将 redo log buffer 中的数据写入 page cache，再调用 fsync 落盘。性能最好，但 MySQL 进程宕掉或者服务器宕机，会存在数据丢失的风险。

InnoDB 的 redo log 是固定大小的，比如可以配置1组4个文件，每个文件的大小是 1GB，那么 redo log 可以记录 4GB 的操作，从头开始写，写到末尾就又回到开头循环写。

1.jpg

write pos 是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到 0 号文件开头，checkpoint 是当前可以擦除的位置，也是往后推移并且循环的。

write pos 和 checkpoint 之间空着的部分，可以用来记录新的操作。

那 redo log 里哪些数据可以被标记为 checkpoint 呢?

前面讲过，每次更新操作，先写 redo log，然后更新内存，此时更新就算完成了。此时，内存页和磁盘页里的数据是不一致的，我们称之为"脏页"，当脏页中的数据刷到磁盘后(落袋为安)，既然都落到磁盘里，可以保证数据不丢了，该部分脏页对应的 redo log 记录也就没啥作用了，此时我们可以将这些记录标记为 checkpoint，代表这些记录占据的空间可以被复用。

如果 write pos 追上了 checkpoint，则说明 redo log 没有可用空间了，此时不能再执行新的更新，得停下来把部分内存"脏页"刷到磁盘里，将 checkpoint 指针往前推动一下。

需要注意的是，redo log 是物理日志，记录的是"在某个数据页上做了什么修改"，而不是记录语句的原始逻辑。

binlog

binlog(归档日志) 位于 Server 层，其主要负责 MySQL 功能层面的事情。binlog 会记录 DDL、DCL、DML(select除外)类的 SQL 语句。

binlog 写入的过程基本也有3步，先写入到 binlog cache，然后再到 page cache，最后持久化到磁盘。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

3.jpg

上图的write，指的就是指把日志由 binlog cache 写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。fsync 才是将数据由 page cache持久化到磁盘的操作。

MySQL 提供了 sync_binlog 配置来控制上述过程:

• sync_binlog=1

表示每次提交事务，都保证数据刷到磁盘，性能较差，但安全性最高；

• sync_binlog=0

表示每次提交事务，都保证数据写入到 page cache，MySQL 进程宕掉不会丢数据，但系统宕机可能会丢数据。

• sync_binlog=N

表示每次提交事务都 write 到 page cache，但累积N个事务后才将数据 fsync 到磁盘。

MySQL 数据库的"双1配置"指的就是将 innodb_flush_log_at_trx_commit 和 sync_binlog 的值均设置为1， 尽最大可能保证数据不丢失。

可以通过以下命令来查看 mysql 的 binlog 信息。

# 0代表未开启，1代表已开启
# 若需要开启 binlog，只需要在启动配置文件中加入 log_bin 配置，然后重启 mysql 实例即可。
mysql> select @@global.log_bin;
+------------------+
| @@global.log_bin |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

# 查看 binlog 的文件存储路径
mysql> select @@global.log_bin_basename;
+------------------------------+
| @@global.log_bin_basename    |
+------------------------------+
| /mnt/disk1/mysql/data/binlog |
+------------------------------+
1 row in set (0.00 sec)

# 查看目前的 binlog 文件列表
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      5542 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

# 查看当前 mysql 实例正在使用的 binlog 文件
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 157
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

binlog 有 statement level、row level、mixed level 三种格式。

其中，在 mysql 5.5 及以前的版本中，默认的格式是 statement level。mysql 5.5 以后的版本默认格式修改为 row level。mixed level 指的是 statement level 和 row level 的混合格式。

# 查看 binlog 的格式
mysql> select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW                    |
+------------------------+
1 row in set (0.00 sec)

首先将 binlog 格式修改为 statement level：

mysql> set global binlog_format="statement";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| STATEMENT              |
+------------------------+
1 row in set (0.00 sec)

需要注意的是: binlog_format 设置完成后，需要退出该客户端，然后重新登录后该配置才能生效。

创建 company 表并插入数据:

create table `company`(
    `id` int(11) not null,
    `name` char(30) not null,
    `country` char(30) not null,
    primary key (`id`),
    key `name` (`name`),
    key `country` (`country`)
)engine=InnoDB;

insert into company values(1, "Apple", "US");
insert into company values(2, "Alibaba", "China");
insert into company values(3, "Microsoft", "US");
insert into company values(4, "Facebook", "US");
insert into company values(5, "Baidu", "China");
insert into company values(6, "Google", "US");

4.jpg

查看一下 binlog 中的内容:

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     2401 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

mysql> show binlog events in 'binlog.000001';

12.jpg

接着看一下更新和删除:

mysql> update company set name="IBM" where id=7;
mysql> delete from company where id=7;
mysql> show binlog events in 'binlog.000001';

13.jpg

可以发现，无论是建表等 DDL 语句，还是插入、更新、删除等 DML 语句，binlog 中均是明文显示的。

还有另外一种方式查看 binlog 文件，就是使用 MySQL 安装目录 bin 下的 mysqlbinlog 脚本:

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog /mnt/disk1/mysql/data/binlog.000001

14.jpg

接着执行如下语句:

mysql> delete from company where id>3;

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog /mnt/disk1/mysql/data/binlog.000001

15.jpg

可以看到，binlog 格式为 statement 时，其仅记录执行的 SQL，不需要记录每一行数据的变化，可以极大减少 binlog 的日志量，避免大量的 IO 操作，提升系统的性能。

但是由于 statement 格式只记录 SQL，当 SQL 中包含了函数，可能会出现执行结果不一致的情况，比如 uuid() 函数，每次执行的时候均会生成1个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后再次执行，就获取到另外1个结果，出现了数据不一致的现象。

从 MySQL 5.15.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一行记录被修改成啥样子。

由于 Row 格式的日志会非常明确的记录下每一行数据的修改细节，故不会出现数据不一致的现象。

下面我们具体看一下 Binlog 的 Row 格式。

首先将 binlog 格式修改为 row level：

mysql> set global binlog_format="row";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW                    |
+------------------------+
1 row in set (0.00 sec)

退出当前客户端，然后重新登录:

mysql> exit
Bye
[root@master ~]# mysql -u root -p
Enter password: 

# 为了演示方便，刷新 binlog，生成1个新的log文件
mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 删除原来的 company 表，重新创建
mysql> drop table company;
Query OK, 0 rows affected (0.34 sec)

# 依次执行以下 SQL 语句
create table `company`(
    `id` int(11) not null,
    `name` char(30) not null,
    `country` char(30) not null,
    primary key (`id`),
    key `name` (`name`),
    key `country` (`country`)
)engine=InnoDB;

insert into company values(1, "Apple", "US");
insert into company values(2, "Alibaba", "China");
insert into company values(3, "Microsoft", "US");
insert into company values(4, "Facebook", "US");
insert into company values(5, "Baidu", "China");
insert into company values(6, "Google", "US");

查看一下 binlog 内容:

mysql> show binlog events in 'binlog.000002';

16.jpg

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog /mnt/disk1/mysql/data/binlog.000002

17.jpg

可以发现，binlog 格式为 row 时，建表语句是明文的，但 insert 语句是非明文的。

那如何明文查看 DML 语句呢?

可以将 binlog_rows_query_log_events 和 global.binlog_rows_query_log_events 参数均设置为1。

mysql> select @@binlog_rows_query_log_events,@@global.binlog_rows_query_log_events;
+--------------------------------+---------------------------------------+
| @@binlog_rows_query_log_events | @@global.binlog_rows_query_log_events |
+--------------------------------+---------------------------------------+
|                              0 |                                     0 |
+--------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> set global binlog_rows_query_log_events=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set session binlog_rows_query_log_events=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@binlog_rows_query_log_events,@@global.binlog_rows_query_log_events;
+--------------------------------+---------------------------------------+
| @@binlog_rows_query_log_events | @@global.binlog_rows_query_log_events |
+--------------------------------+---------------------------------------+
|                              1 |                                     1 |
+--------------------------------+---------------------------------------+
1 row in set (0.00 sec)

此时再插入一条新数据，然后查看 binlog:

mysql> insert into company values(7, "Github", "US");
Query OK, 1 row affected (0.06 sec)

mysql> show binlog events in 'binlog.000002';

7.jpg

可以发现，新的 insert 语句可以显示出来了，但是处于被"#"注释的状态。

接着看一下更新和删除:

mysql> update company set name="IBM" where id=7;
mysql> delete from company where id=7;
mysql> show binlog events in 'binlog.000002';

5.jpg

使用 mysqlbinlog 脚本试一下:

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog -vv /mnt/disk1/mysql/data/binlog.000002

6.jpg

8.jpg

同样的，建表等 DDL 语句是明文显示的，但 insert、update、delete 等 DML 语句就是非明文的。

要想明文查看 DML 语句，需要添加 -vv 参数:

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog -vv /mnt/disk1/mysql/data/binlog.000002

9.jpg

接着执行如下 SQL:

mysql> delete from company where id>3;

使用 mysqlbinlog 脚本查看 binlog：

[root@master ~]# /usr/lib/huluwa/mysql-8.0.28-el7-x86_64/bin/mysqlbinlog -vv /mnt/disk1/mysql/data/binlog.000002

10.jpg

可以发现，delete 多行记录时，Row 格式的 binlog 不单单记录了执行 SQL，还把每行数据的变化均记录了下来。

所以，Row 格式最大的问题就是日志量较大，特别是执行批量 update、delete、alter等操作时，由于需要记录每一行数据的变化，会产生大量的日志，进而带来 IO 性能问题。

有些 statement 格式的 binlog 可能会导致主备不一致，而 row 格式又太占空间。MySQL 额外提供了折中的方案，即 mixed 格式的 binlog。mixed 格式的意思是 MySQL 自己会判断某条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则使用 statement 格式。

如果你的线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。

接下来，我们就分别从 delete、insert和update 这三种 SQL 语句的角度，来看看数据恢复的问题。

即使我执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了 insert 语句呢？那就更直接了。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。

如果执行的是 update 语句的话，binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

两阶段提交

假设存储引擎为 InnoDB，执行如下 SQL 语句:

update student set name="Bob" where id=9

执行过程如下:

• 执行器先找引擎取 id=6 这一行。id 是主键，引擎直接通过 B+ 树搜索找到这一行所在的页，如果该行数据所在的页本来就在内存中，则直接返回给执行器；否则，需要先将该数据页从磁盘读取到内存中再返回；
• 执行器拿到引擎返回的行数据，将 name 字段修改为 "Bob"，再调用引擎接口写入这行新数据；
• 引擎将这行新数据更新到内存，同时将更新操作记录到 redo log 里，此时 redo log 处于 prepare 状态，并告知执行器操作已完成，随时准备提交事务；
• 紧接着执行器生成这个操作的 binlog，并把 binlog 写入磁盘；
• 执行器调用引擎的提交事务接口，引擎就把刚刚写入的 redo log 状态修改为 commit，更新完成。

可以发现，redo log的写入包含 prepare 和 commit 两个步骤，即"两阶段提交"。

之所以采用"两阶段提交"，其实主要是为了保证 redo log 和 binlog 状态数据的逻辑一致性。

假设 redo log 和 binlog 各管各的，以上面的 SQL 语句为例:

# 假设原先 id 为9的用户 name 为 Jack
update student set name="Bob" where id=9

现在有4种情况:

• ① redo log 和 binlog 均更新成功；
• ② redo log 更新成功，binlog 更新失败；
• ③ redo log 更新失败，binlog 更新成功；
• ④ redo log 和 binlog 均更新失败；

当情况是①和④时，redo log 和 binlog 的数据状态均是一致的。

但情况是②和③时，redo log 和 binlog 的数据状态，id 为9的用户 name，一个为 Jack，一个为 Bob，出现了不一致。

所以必须将 binlog 和 redo log 糅合到一个过程中全盘考虑，整个过程精简下来其实就3个状态:

redo log(prepare)-->binlog-->redo log(commit)。

第1步就失败的话，redo log 和 binlog 相当与均没有数据更新，不存在数据不一致；

第2步失败的话，binlog 没有更新，此时 redo log 状态为 prepare，所以异常重启进行状态恢复的时候，该条日志会被忽略(相当于回滚)，也不存在数据不一致；

第3步失败的话，binlog 中该步操作已经被记录，所以基于 redo log 进行数据状态恢复的时候，如果某条日志处于 prepare 状态，需要检查与该条日志属于同一事务的 binlog 是否存在，若存在，则进行 commit 提交(相当于重试)，否则回滚(第2步就失败的情况)。

可以看到，第3步失败的话，必须同时查看 binlog 中是否存在该事务的日志，来决定继续提交或回滚，一阶段提交也可以满足一致性需求呀!

比如，先写 binlog，然后再写 redo log，每次写 redo log 的时候都首先检查属于该事务的 binlog 是否存在，若存在，则提交，否则不提交。

之所以不采用这种方案，我猜测是性能上的考虑，因为上述方案在写 redo log 的时候，都必须同步检索 binlog，而这是个较为耗时的操作。

采用两阶段提交方案，只有当 redo log 中某条事务的日志只有 prepare 状态时，才需要同步检索 binlog(该种情况发生的概率极小)。只要 redo log 中的日志是 commit 状态，则同一事务的 binlog 必然已经写入了，不用再去同步检索 binlog 确认，在保证一致性的同时，极大得提高了写入性能。

本文到此结束，感谢你的阅读!