10187 字
约 33 分钟
1
MySQL：bin log、redo log、undo log 到底有什么区别？
2026-09-19
面试题库
面试题库

MySQL：bin log、redo log、undo log 到底有什么区别？

作者：沉默王二 发布时间：2022-12-06T16:57:36.280+0800

球友们好，本来打算自己写一篇 MySQL 的 bin log、redo log、undo log 的，结果看到一篇读者的文章，写得已经非常好了，这里就直接先分享给大家吧。后面写MySQL 专栏的时候再自己重写一版。

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括 redo log 和 undo log )。

内容收录在星球专栏《Java 面试指南》的《技术提升篇》中，专栏托管在语雀平台上（更方便你沉浸式阅读和做笔记），访问地址和密码：https://t.zsxq.com/6iuzn6I

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的主从复制流程：

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

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

  • 同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。

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

  • 增量备份

如何开启bin log？

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

+---------------------------------+-------+| 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 logmysql> 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日志

配置server-idserver-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> flush logs; # 查看二进制日志列表mysql> show binary logs;

bin log恢复数据：

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

# 根据指定时间恢复（--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格式必须使用该选项对日志进行解析，恢复数据时不能加该选项。

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

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

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 = 2mysql> 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 BEFORE '2022-02-18 18:30:00'

redo log（重做日志）

什么是redo log？

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

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

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

因为，这种改变记录不是说一定要全部保存下来，所以，redo log采用的是大小固定，循环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

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

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

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)要快很多。

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

还有一种比较特殊的情况，数据页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。

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是小花

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工作原理？

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

  • 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
  • 此时事务B查询，直接读取undo buffer缓存，因为这时候事务A还没提交且它需要回滚事务，所以，这时候事务B是不读取磁盘的，是直接从undo buffer缓存中读取。

参考链接：http://www.lzhpo.com/article/173

球友们好，本来打算自己写一篇 MySQL 的 bin log、redo log、undo log 的，结果看到一篇读者的文章，写得已经非常好了，这里就直接先分享给大家吧。后面写MySQL 专栏的时候再自己重写一版。

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括 redo log 和 undo log )。

内容收录在星球专栏《Java 面试指南》的《技术提升篇》中，专栏托管在语雀平台上（更方便你沉浸式阅读和做笔记），访问地址和密码：https://t.zsxq.com/6iuzn6I

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的主从复制流程：

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

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

  • 同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。

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

  • 增量备份

如何开启bin log？

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

+---------------------------------+-------+| 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 logmysql> 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日志

配置server-idserver-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> flush logs; # 查看二进制日志列表mysql> show binary logs;

bin log恢复数据：

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

# 根据指定时间恢复（--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格式必须使用该选项对日志进行解析，恢复数据时不能加该选项。

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

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

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 = 2mysql> 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 BEFORE '2022-02-18 18:30:00'

redo log（重做日志）

什么是redo log？

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

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

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

因为，这种改变记录不是说一定要全部保存下来，所以，redo log采用的是大小固定，循环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

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

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

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)要快很多。

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

还有一种比较特殊的情况，数据页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。

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是小花

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工作原理？

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

  • 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
  • 此时事务B查询，直接读取undo buffer缓存，因为这时候事务A还没提交且它需要回滚事务，所以，这时候事务B是不读取磁盘的，是直接从undo buffer缓存中读取。

参考链接：http://www.lzhpo.com/article/173

球友们好，本来打算自己写一篇 MySQL 的 bin log、redo log、undo log 的，结果看到一篇读者的文章，写得已经非常好了，这里就直接先分享给大家吧。后面写MySQL 专栏的时候再自己重写一版。

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括 redo log 和 undo log )。

内容收录在星球专栏《Java 面试指南》的《技术提升篇》中，专栏托管在语雀平台上（更方便你沉浸式阅读和做笔记），访问地址和密码：https://t.zsxq.com/6iuzn6I

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的主从复制流程：

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

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

  • 同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。

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

  • 增量备份

如何开启bin log？

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

+---------------------------------+-------+| 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 logmysql> 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日志

配置server-idserver-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> flush logs; # 查看二进制日志列表mysql> show binary logs;

bin log恢复数据：

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

# 根据指定时间恢复（--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格式必须使用该选项对日志进行解析，恢复数据时不能加该选项。

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

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

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 = 2mysql> 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 BEFORE '2022-02-18 18:30:00'

redo log（重做日志）

什么是redo log？

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

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

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

因为，这种改变记录不是说一定要全部保存下来，所以，redo log采用的是大小固定，循环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

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

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

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)要快很多。

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

还有一种比较特殊的情况，数据页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。

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是小花

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工作原理？

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

  • 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
  • 此时事务B查询，直接读取undo buffer缓存，因为这时候事务A还没提交且它需要回滚事务，所以，这时候事务B是不读取磁盘的，是直接从undo buffer缓存中读取。

参考链接：http://www.lzhpo.com/article/173 

            知识星球
            
    
    
        
        扫码加入星球
        查看更多优质内容
    
    https://wx.zsxq.com/mweb/views/joingroup/join_group.html?group_id=15522885221412
MySQL：bin log、redo log、undo log 到底有什么区别？
http://www.clxhxhhr.top/posts/2396/
作者
clxstart
发布于
2026-09-19
许可协议
CC BY-NC-SA 4.0
精选面试题篇：40 道精选 Kafka 面试题👍
技术提升篇：进制转换的原理是什么？
评论
0 条
还没有评论，先写一条吧。
☰ 文章目录
目录