bin log、redo log、undo log 有什么区别？（完结）

球友们好，本来打算自己写一篇 MySQL 的 bin log、redo log、undo log 的，结果看到一篇

读者的文章，写得已经非常好了，这里就直接先分享给大家吧。后面写 MySQL 专栏的时

候再自己重写一版。

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 MySQL 日

志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开

发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括 redo log 和 undo log )。

bin log（二进制日志）

什么是 bin log？

记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。

由服务层产生，所有储存引擎都支持。

bin log 属于逻辑日志。

bin log 日志有三种格式：STATMENT、ROW、MIXED。MySQL5.7.7 之后默认是 ROW。

简单普及一下逻辑日志和物理日志：

• 逻辑日志：记录的 sql 语句。

• 物理日志：记录的数据页变更。

bin log 的使用场景？

• 主从复制：在 master 端开启 bin log，然后 master 将 bin log 发送到每个 slave 端，

slave 端重放 bin log 从而达到主从数据一致。

简单画个图来理解一下 MySQL 的主从复制流程：

e1d002be90e0a7f47b567a3aa28f2205.png

1. master 在准备提交事务之前，将变更记录到 bin log 中。

2. slave 启动一个 IO 线程来读取 bin log 中的事件，并记录到自己的 ready log（中继日

志）中。

3. 同时 slave 还会启动一个 SQL 线程，读取 ready log 中的事件在备库中执行，从而实

现备库的数据更新。

• 数据恢复：通过使用 mysqlbinlog 工具来恢复数据。

• 增量备份

如何开启 bin log？

# 查看是否开启 bin log，这里可以看到没有开启

mysql> show variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+-------+

6 rows in set (0.05 sec)

# 查看当前 MySQL 版本

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.7.36 |

+-----------+

1 row in set (0.20 sec)

# 确认已开启 bin log

mysql> show variables like '%log_bin%';

+---------------------------------+-----------------------------+

| Variable_name | Value |

+---------------------------------+-----------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog |

| log_bin_index | /var/lib/mysql/binlog.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+-----------------------------+

6 rows in set (0.03 sec)

如果没有开启，编辑/etc/my.cnf 文件，添加以下配置：

# 开启 bin log 日志

log-bin=binlog

# 配置 server-id

server-id=1

如何查看以及修改每个 bin log 文件大小最大值？

查看本机的 bin log 文件大小最大值，可以看到是 1073741824 字节，也就是 1G。

mysql> show variables like 'max_binlog_size';

+-----------------+------------+

| Variable_name | Value |

+-----------------+------------+

| max_binlog_size | 1073741824 |

+-----------------+------------+

1 row in set (0.00 sec)

添加或修改 MySQL 的 my.cnf 文件：

max_binlog_size=2G

如何使用 bin log 恢复数据？

查看 bin log：

# 查看当前 bin log 位置

mysql> show master status;

# 也可以刷新日志，方便后面筛选

mysql> flush logs;

# 查看二进制日志列表

mysql> show binary logs;

bin log 恢复数据：

# 根据事件位置恢复（--start-position 是开始位置，--stop-position 是结束位置）

mysqlbinlog --start-position=16275 --stop-position=16566 --database=just-test

/var/lib/mysql/binlog.000004 | mysql -uroot -p123456

# 根据指定时间恢复（--start-datetime 是开始时间，--stop-datetime 是结束时间）

mysqlbinlog --start-datetime="2022-02-17 12:00:00" --stop-datetime="2022-02-17

18:00:00" --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot

-p123456

# --database 是指定只恢复 just-test 数据库，/var/lib/mysql/binlog.000004 是 binlog 日志

文件路径

bin log 日志解码以及导出到服务器中：

mysqlbinlog -vvv --base64-output=decode-rows --start-position=154 --stop-

position=2150 --database=just-test /var/lib/mysql/binlog.000001 > /just-test.sql |

mysql -uroot -p123456 -f

-vvv：显示出执行的 SQL 以及 binlog_rows_query_log_events 参数。

--base64-output=decode-rows：如果 bin log 为 row 格式必须使用该选项对日志进行解

析，恢复数据时不能加该选项。

简单演示一下如何恢复被删掉的数据：

# 快速创建一个极其简单的数据表来用于测试

mysql> CREATE TABLE `quick-test-data` (

`id` bigint(20) DEFAULT NULL,

`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# 分别插入小明和小花两条数据

mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (1, '小明');

mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (2, '小花');

mysql> INSERT * FROM `quick-test-data`;

+------+--------+

| id | name |

+------+--------+

| 1 | 小明 |

| 2 | 小花 |

+------+--------+

# 删掉小花

mysql> DELETE FROM `quick-test-data` WHERE `id` = 2

mysql> INSERT * FROM `quick-test-data`;

+------+--------+

| id | name |

+------+--------+

| 1 | 小明 |

+------+--------+

# 查看 bin log 日志，确定从哪里开始恢复（数据比较多，我在此抽取一部分，binlog.000004

是刷新几次之后的日志文件，根据自己实际情况来）

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

+---------------+-------+----------------+-----------+-------------

+---------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info

|

+---------------+-------+----------------+-----------+-------------

+---------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

| binlog.000004 | 15636 | Anonymous_Gtid | 1| 15701 | SET

@@SESSION.GTID_NEXT= 'ANONYMOUS'

|

| binlog.000004 | 15701 | Query | 1| 15984 | use `just-test`; CREATE

TABLE `just-test`.`quick-test-data` (

`id` bigint NULL,

`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

NULL

) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci |

| binlog.000004 | 15984 | Anonymous_Gtid | 1| 16049 | SET

@@SESSION.GTID_NEXT= 'ANONYMOUS'

|

| binlog.000004 | 16049 | Query | 1| 16126 | BEGIN

|

| binlog.000004 | 16126 | Table_map | 1| 16192 | table_id: 116 (just-

test.quick-test-data)

|

| binlog.000004 | 16192 | Write_rows | 1| 16244 | table_id: 116 flags:

STMT_END_F

|

| binlog.000004 | 16244 | Xid | 1| 16275 | COMMIT /* xid=1088 */

|

| binlog.000004 | 16275 | Anonymous_Gtid | 1| 16340 | SET

@@SESSION.GTID_NEXT= 'ANONYMOUS'

|

| binlog.000004 | 16340 | Query | 1| 16417 | BEGIN

|

| binlog.000004 | 16417 | Table_map | 1| 16483 | table_id: 116 (just-

test.quick-test-data)

|

| binlog.000004 | 16483 | Write_rows | 1| 16535 | table_id: 116 flags:

STMT_END_F

|

| binlog.000004 | 16535 | Xid | 1| 16566 | COMMIT /* xid=1091 */

|

| binlog.000004 | 16566 | Anonymous_Gtid | 1| 16631 | SET

@@SESSION.GTID_NEXT= 'ANONYMOUS'

|

| binlog.000004 | 16631 | Query | 1| 16708 | BEGIN

|

| binlog.000004 | 16708 | Table_map | 1| 16774 | table_id: 116 (just-

test.quick-test-data)

|

| binlog.000004 | 16774 | Delete_rows | 1| 16826 | table_id: 116 flags:

STMT_END_F

|

| binlog.000004 | 16826 | Xid | 1| 16857 | COMMIT /* xid=1094 */

|

+---------------+-------+----------------+-----------+-------------

+---------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

### 分析此处的 bin log 日志 ###

# 15701~15984：建表 quick-test-data

# 15984~16275：插入第一条数据，即小明

# 16275~16566：插入第二条数据，即小花

# 16566~16857：删除小花

## 所以，因为数据表和小明都是存在的，所以，我们的 start-position 应该是 16275，stop-

position 应该是 16566

## 16566~16857 这里是删除小花的，所以，这个部分不能被包括进来，不然恢复之后又被删

除掉了

# 使用 mysqlbinlog 工具恢复被删除的小花数据

# 在 Linux 命令行执行（注意不是在 mysql 命令行）

[root@lzhpo-light ~]# mysqlbinlog --start-position=16275 --stop-position=16566 --

database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456

# 恢复之后的数据表

mysql> select * from `quick-test-data`;

+------+--------+

| id | name |

+------+--------+

| 1 | 小明 |

| 2 | 小花 |

+------+--------+

如何正确删除 bin log 日志?

• reset master 命令：虽然可以清空所有 bin log 文件，但是会导致从库异常，主从架

构下无法使用。

• expire_logs_days 变量：通过该变量可以指定自动删除日期，如果日志过多，在删

除时会有 IO 过高问题，可能导致性能抖动。

• purge 命令：推荐方法，可以快速删除指定 bin log。

# 删除 bin log 到指定的文件为止

mysql> PURGE MASTER LOGS TO 'binlog.000004'

# 删除指定日期之前的文件

mysql> PURGE MASTER LOGS BEFORE '2022-02-18 18:30:00'

redo log（重做日志）

什么是 redo log？

简单一句话：redo log 就是记录数据页的变更。

redo log 由 InnoDB 的存储引擎层产生，是 InnoDB 存储引擎特有的。

redo log 属于物理日志，因为，它记录的是数据页的变更。

因为，这种改变记录不是说一定要全部保存下来，所以，redo log 采用的是大小固定，循

环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

redo log 记录日志的方式（原理）？

emm…图丑，但能帮助理解就好。

df00344e9f379846337c49b2811dc607.png

InnoDB 的 redo log 文件大小是固定的，假设我这里 redo log 大小为 4G，并且我划分为 4

个部分，redo log 就会从 ib_logfile_0 开始写 ib_logfile_1、ib_logfile_2、ib_logfile_3，

直到 4 个部分都写满为止，再重新回到第一个部分 ib_logfile_0 开始写。

write position：当前记录的 LSN（Log Sequence Number，日志序列号）位置。一边写，

一边顺时针移动（向前移动）。

check point：当前数据页更改记录刷盘之后所处的 LSN（Log Sequence Number，日志序

列号）位置。一边写，一边顺时针移动（向前移动）。

check point 到 write position 部分就是待落盘的数据页更改记录。

write position 到 check point 部分就是 redo log 空闲的部分，用来记录新的操作日志。

当 write position 追上 check point 的时候，会先推动 check point 顺时针移动（向前移

动），等到有空闲的 redo log 位置的时候再记录新的操作日志。

redo log 如何进行异常崩溃数据恢复的？

每当启动 InnoDB 的时候，不管上次是正常关闭还是异常关闭，都会进行恢复操作。

因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 bin log)

要快很多。

在此，我画一个简单的流程图方便理解

17b438079dbb64066896622218fd8ca2.png

还有一种比较特殊的情况，数据页 LSN 也会大于日志 LSN：

当宕机的时候，正在处理 check point 刷盘过程，且数据页的刷盘进度超过了日志页的刷

盘进度，此时，也会出现数据页 LSN 也会大于日志 LSN 的情况。

这种情况的话，数据页剩下的这点 redo log 日志将不会重做，会正常启动。

如何查看以及修改 redo log 的大小？

mysql> show variables like 'innodb_log%';

+------------------------------------+----------+

| Variable_name | Value |

+------------------------------------+----------+

| innodb_log_buffer_size | 16777216 |

| innodb_log_checksums | ON |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 50331648 |

| innodb_log_files_in_group |2 |

| innodb_log_group_home_dir | ./ |

| innodb_log_spin_cpu_abs_lwm | 80 |

| innodb_log_spin_cpu_pct_hwm | 50 |

| innodb_log_wait_for_flush_spin_hwm | 400 |

| innodb_log_write_ahead_size | 8192 |

| innodb_log_writer_threads | ON |

+------------------------------------+----------+

11 rows in set (0.00 sec)

innodb_log_file_size 就是 redo log 文件大小，50331648 字节，也就是 48MB。

要修改的话，打开 MySQL 的 my.cnf 文件，添加或编辑配置，比如，我修改为 1G：

innodb_log_file_size=1G

如何自定义在事务提交的时候将 log buffer 中的日志刷入 log file 中的时机？

可以通过配置 innodb_flush_log_at_trx_commit 参数来更改刷入时机。

查看当前 MySQL 的配置时机：

mysql> show variables like 'innodb_flush_log_at_trx_commit';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

1 row in set (0.00 sec)

参数值解释：

• 0：延迟写，延迟刷。

事务提交的的时候不会将 redo log buffer 中的日志写入到 os buffer，而是每秒写入 os

buffer 并调用 fsync()写入到 redo log file 中。

这样子可能会导致，当系统崩溃，可能丢失 1 秒的数据。

• 1：实时写，实时刷。

事务每次提交都会将 redo log buffer 中的日志写入 os buffer 并调用 fsync()刷到 redo

log file 中。

这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO 的性能

较差。

• 2：实时写，延迟刷。

事务每次提交都仅写入到 os buffer，然后是每秒调用 fsync()将 os buffer 中的日志写入

到 redo log file。

0ca95518de6e735eb2acf7d7889dbf20.png

undo log（回滚日志）

什么是 undo log？

MySQL（存储引擎需要能支持事务）在修改记录之前（提交事务之前），会把

原先记录的值先保存起来（也就是写入到 undo log），然后再修改（提交事

务），当出问题的时候 MySQL 可以利用 undo log 来回滚事务，即恢复原先的记

录值。

undo log 由 InnoDB 的存储引擎层产生，是 InnoDB 存储引擎特有的（和 redo log

一样）。

undo log 属于逻辑日志。

MySQL 的事务四大特性：原子性、隔离性、持久性、一致性。其中，原子性的底层就是

靠 undo log 实现的。

undo log 的作用？

1.事务回滚

前面我们说到，当出问题需要回滚事务的时候，可以利用 MySQL 的 undo log 来回滚事

务，以保证事务的一致性。

比如：执行一条 delete 语句:

DELETE FROM sys_log WHERE id = 1;

undo log 就会记录一条与它相反的日志，即记录它的 insert 语句。

update 语句也是同理，记录与它相反的 update 语句。

执行一条 update 语句：

# 假设，之前 username 是小花

UPDATE sys_user SET username = '小明' WHERE id = 1;

undo log 就会记录与它相反的 update 语句：

UPDATE sys_user SET username = '小花' WHERE id = 1;

2.多版本控制（MVCC）

MVCC 全称即 Multi-Version Concurrency Control。

在 MySQL 的 InnoDB 存储引擎中，就是用 undo log 来实现 MVCC 的。

举个例子，当我们读取的某一行被其它事务锁定的时候，InnoDB 可以从 undo log 中分析

出该记录历史版本的数据，从而让我们可以读取到当前事务操作之前的数据（也就是快照

读）。

普及一下快照读和当前读：

• 快照读：读取的历史版本数据，不会加锁。

普通的 select 就是快照读。

• 当前读：读取的是最新版本, 会对读取的记录加锁, 以保证其它事务无法对此记录进行

变更，保证安全性。

属于当前读的：update、delete、insert、select … for update、select … lock in share mode

（共享读锁）等等。

undo log 的工作机制？

在 MySQL 的 InnoDB 存储引擎中，undo log 是采用分段（segment）的方式保存的，简单

来说，就是一种命名为 rollback segment 的回滚段，每个回滚段中有 1024 个 undo log

segment。

在 MySQL5.5 之后，能支持 128 个回滚段，也就是能支持 128*1024 个 undo log

segment，在此之前是只支持 1 个回滚段，也就是 1024 个 undo log segment。

undo log 工作原理？

在此，简单画一张图来理解一下：

ff63322167d06228d575ff4f6ab74795.png

1. 事务 A 提交之前，会备份之前的数据到对应的 undo buffer，然后 undo log 保存之前

的记录数据，然后再将最新的数据持久化到 ibd 文件。

2. 此时事务 B 查询，直接读取 undo buffer 缓存，因为这时候事务 A 还没提交且它需要

回滚事务，所以，这时候事务 B 是不读取磁盘的，是直接从 undo buffer 缓存中读

取。