跳至主要內容

InnoDB底层原理与MySQL日志机制

橘子coding2026/10/1约 4645 字

一条 update 语句在 MySQL 里究竟经历了什么？连接器、查询缓存、分析器、优化器、执行器这些 Server 层组件在 MySQL 体系结构概览 里已经聊过，这里就不再展开。这篇主要看看 InnoDB 的底层结构与一套日志机制：redo log、binlog、undo log，先上个更新流程总览图。

InnoDB底层原理与MySQL日志机制-SQL更新执行流程

从图中可以看到，一次更新操作除了读写 Buffer Pool，还会和 redo log、binlog、undo log 三份日志打交道，下面逐个来看。

1. redo log 重做日志

1.1 redo log 关键参数

参数默认值说明
innodb_log_buffer_size16Mredo log buffer 的大小，最小 1M，最大 4096M
innodb_log_group_home_dir./redo log 文件存储位置，默认就是 InnoDB 数据文件存储位置，目录下的 ib_logfile0 和 ib_logfile1 即为 redo log 文件
innodb_log_files_in_group2redo log 文件个数，命名方式如 ib_logfile0、ib_logfile1...ib_logfileN，最大 100 个
innodb_log_file_size48M单个 redo log 文件大小，最大 512G。注意这个最大值指的是整个 redo log 系列文件之和，即 innodb_log_files_in_group * innodb_log_file_size 不能大于 512G

查看这些参数的 SQL：

show variables like '%innodb_log_buffer_size%';
show variables like '%innodb_log_group_home_dir%';
show variables like '%innodb_log_files_in_group%';
show variables like '%innodb_log_file_size%';

1.2 redo log 的写入过程

redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下图所示。

InnoDB底层原理与MySQL日志机制-redo log 环形结构

  • write pos：当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
  • checkpoint：当前要擦除的位置，同样往后推移并且循环，擦除记录前要把记录更新到数据文件里。
  • write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示 redo log 写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

1.3 redo log 刷盘策略（innodb_flush_log_at_trx_commit）

innodb_flush_log_at_trx_commit 这个参数控制 redo log 的写入策略，它有三种可能取值。

  • 设置为 0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
  • 设置为 1（默认值）：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
  • 设置为 2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存 page cache 里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache 里的数据还没来得及写入磁盘文件的话就会丢失数据。

另外 InnoDB 有一个后台线程，每隔 1 秒就会把 redo log buffer 中的日志，调用操作系统的 write 函数写到文件系统的 page cache，然后调用 fsync 持久化到磁盘文件，写入策略如图所示。

InnoDB底层原理与MySQL日志机制-事务提交与 redo log 刷盘流程

# 查看 innodb_flush_log_at_trx_commit 参数值
show variables like 'innodb_flush_log_at_trx_commit';

# 设置 innodb_flush_log_at_trx_commit 参数值（也可以在 my.ini 或 my.cnf 文件里配置）
set global innodb_flush_log_at_trx_commit=1;

2. binlog 二进制归档日志

2.1 开启 binlog 及相关参数

binlog 二进制日志保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可以通过二进制日志文件排查用户操作或表结构操作，从而来恢复数据库数据。

启动 binlog 记录功能会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处大于对服务器的影响。

# 查看 binlog 相关参数
show variables like '%log_bin%';

MySQL 5.7 版本中，binlog 默认是关闭的，8.0 版本默认是打开的，如果 log_bin 的值为 OFF，就代表 binlog 处于关闭状态。开启 binlog 功能需要修改配置文件 my.ini(Windows) 或 my.cnf(Linux)，在 [mysqld] 部分增加如下配置后重启数据库。

[mysqld]
# log-bin 设置 binlog 的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则 binlog 文件默认会放在 data 数据目录下
log-bin=binlog

# Server Id 是数据库服务器 id，随便写一个数都可以，这个 id 用来在 MySQL 集群环境中标记唯一 MySQL 服务器，集群环境中每台 MySQL 服务器的 id 不能一样，不加启动会报错
server-id=1

# 其他配置
# 日志文件格式，下面会详细解释
binlog_format=row
# 执行自动删除 binlog 日志文件的天数，默认为 0，表示不自动删除
expire_logs_days=15
# 单个 binlog 日志文件的大小限制，默认为 1GB
max_binlog_size=200M

重启数据库后再去看 data 数据目录会多出两个文件，一个是 binlog 日志文件，另一个是 binlog 文件的索引文件，这个索引文件管理了所有的 binlog 文件的目录。当然也可以执行命令查看有多少 binlog 文件：

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000045 |   4322096 | No        |
| binlog.000046 |      1035 | No        |
| binlog.000047 |       180 | No        |
| binlog.000048 |      1890 | No        |
| binlog.000049 |    722632 | No        |
| binlog.000050 |    358027 | No        |
| binlog.000051 |    866272 | No        |
| binlog.000052 |       157 | No        |
| binlog.000053 |      1035 | No        |
| binlog.000054 |       180 | No        |
| binlog.000055 |    171157 | No        |
| binlog.000056 |      1890 | No        |
| binlog.000057 |   4529947 | No        |
+---------------+-----------+-----------+
13 rows in set (0.01 sec)

binlog 相关参数说明。

  • log_bin：binlog 日志是否打开状态。
  • log_bin_basename：binlog 日志的基本文件名，后面会追加标识来表示每一个文件，binlog 日志文件会滚动增加。
  • log_bin_index：指定的是 binlog 文件的索引文件，这个文件管理了所有的 binlog 文件的目录。
  • sql_log_bin：SQL 语句是否写入 binlog 文件，ON 代表需要写入，OFF 代表不需要写入。如果想在主库上执行一些操作，但不复制到 slave 库上，可以通过修改参数 sql_log_bin 来实现，比如模拟主从同步复制异常。

binlog 开启之后，再次查看相关参数会看到 log_bin 的值变成了 ON：

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.00 sec)

2.2 binlog 日志格式

用参数 binlog_format 可以设置 binlog 日志的记录格式，MySQL 支持三种格式类型。

  • STATEMENT：基于 SQL 语句的复制，每一条会修改数据的 SQL 都会记录到 master 机器的 binlog 中。这种方式日志量小，节约 IO 开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如 UUID()、SYSDATE() 等函数如果随 SQL 同步到 slave 机器去执行，则结果跟 master 机器执行的不一样。
  • ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改，记录下每一行数据修改的细节，可以解决函数、存储过程等在 slave 机器的复制问题，但这种方式日志量较大，性能不如 STATEMENT。举个例子，假设 update 语句更新 10 行数据，STATEMENT 方式就记录这条 update 语句，ROW 方式会记录被修改的 10 行数据。
  • MIXED：混合模式复制，实际就是前两种模式的结合。在 MIXED 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 STATEMENT 和 ROW 之间选择一种，如果 SQL 里有函数或一些在执行时才知道结果的情况，会选择 ROW，其它情况选择 STATEMENT，推荐使用这一种。

2.3 binlog 刷盘策略（sync_binlog）

binlog 写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

  • 为 0 的时候，表示每次提交事务都只 write 到 page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache 里面的 binlog 会丢失。
  • 也可以设置为 1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
  • 还有一种折中方式，可以设置为 N（N > 1），表示每次提交事务都 write 到 page cache，但累积 N 个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失 N 个事务的 binlog。

2.4 binlog 文件重新生成与删除

发生以下任何事件时，binlog 日志文件会重新生成。

  • 服务器启动或重新启动。
  • 服务器刷新日志，执行命令 flush logs。
  • 日志文件大小达到 max_binlog_size 值，默认值为 1GB。
# 删除所有 binlog 日志文件，重新开始记录
reset master;

# 删除指定日志文件之前的所有日志文件，下面这个是删除 50 之前的所有日志文件，当前这个文件不删除
purge master logs to 'binlog.000050';

# 删除指定日期前的日志索引中 binlog 日志文件
purge master logs before '2026-10-02 00:00:00';

2.5 查看 binlog 日志文件

可以用 MySQL 自带的命令工具 mysqlbinlog 查看 binlog 日志内容，能看到里面有具体执行的修改伪 SQL 语句以及执行时的相关情况。

# 查看 binlog 二进制文件（命令行方式，不用登录 mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/binlog.000050

# 查看 binlog 二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/binlog.000050 start-datetime="2026-10-02 00:00:00" stop-datetime="2026-10-03 00:00:00" start-position="5000" stop-position="20000"

2.6 用 binlog 恢复数据

用 binlog 日志文件恢复数据其实就是回放执行之前记录在 binlog 文件里的 SQL，举一个数据恢复的例子。

# 先执行刷新日志的命令生成一个新的 binlog 文件 binlog.000058，后面我们的修改操作日志都会记录在这个最新的文件里
flush logs;

# 执行两条插入语句
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhangsan', '666');
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'lisi', '888');

# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id > 3;

现在需要恢复被删除的两条数据，我们先查看 binlog 日志文件：

mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/binlog.000058

文件内容如下：

。。。。。。
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#261002 18:48:00 server id 1 end_log_pos 291 CRC32 0x4528234f Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1790938080/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#261002 18:48:00 server id 1 end_log_pos 345 CRC32 0x7482741d Table_map: `test`.`account` mapped to number 99
# at 345
#261002 18:48:00 server id 1 end_log_pos 396 CRC32 0x5e443cf0 Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
### @1=4
### @2='zhangsan'
### @3=666
# at 396
#261002 18:48:00 server id 1 end_log_pos 427 CRC32 0x8a0d8a3c Xid = 56
COMMIT/*!*/;
# at 427
#261002 18:48:16 server id 1 end_log_pos 492 CRC32 0x5261a37e Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 492
#261002 18:48:16 server id 1 end_log_pos 564 CRC32 0x01086643 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1790938096/*!*/;
BEGIN
/*!*/;
# at 564
#261002 18:48:16 server id 1 end_log_pos 618 CRC32 0xc26b6719 Table_map: `test`.`account` mapped to number 99
# at 618
#261002 18:48:16 server id 1 end_log_pos 670 CRC32 0x8e272176 Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
### @1=5
### @2='lisi'
### @3=888
# at 670
#261002 18:48:16 server id 1 end_log_pos 701 CRC32 0xb5e63d00 Xid = 58
COMMIT/*!*/;
# at 701
#261002 18:49:59 server id 1 end_log_pos 766 CRC32 0xa0844501 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 766
#261002 18:49:59 server id 1 end_log_pos 838 CRC32 0x687bdf88 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1790938199/*!*/;
BEGIN
/*!*/;
# at 838
#261002 18:49:59 server id 1 end_log_pos 892 CRC32 0x4f7b7d6a Table_map: `test`.`account` mapped to number 99
# at 892
#261002 18:49:59 server id 1 end_log_pos 960 CRC32 0xc47ac777 Delete_rows: table id 99 flags: STMT_END_F
### DELETE FROM `test`.`account`
### WHERE
### @1=4
### @2='zhangsan'
### @3=666
### DELETE FROM `test`.`account`
### WHERE
### @1=5
### @2='lisi'
### @3=888
# at 960
#261002 18:49:59 server id 1 end_log_pos 991 CRC32 0x386699fe Xid = 65
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
。。。。。。

找到两条插入数据的 SQL，每条 SQL 的上下都有 BEGIN 和 COMMIT，我们找到第一条 SQL BEGIN 前面的文件位置标识 at 219（这是文件的位置标识），再找到第二条 SQL COMMIT 后面的文件位置标识 at 701，就可以根据文件位置标识来恢复数据了。

mysqlbinlog --no-defaults --start-position=219 --stop-position=701 --database=test /var/lib/mysql/binlog.000058 | mysql -uroot -p123456 -v test

补充一个根据时间来恢复数据的命令，我们找到第一条 SQL BEGIN 前面的时间戳标记 SET TIMESTAMP=1790938080，再找到第二条 SQL COMMIT 后面的时间戳标记 SET TIMESTAMP=1790938199，转成 datetime 格式：

mysqlbinlog --no-defaults --start-datetime="2026-10-02 18:48:00" --stop-datetime="2026-10-02 18:49:59" --database=test /var/lib/mysql/binlog.000058 | mysql -uroot -p123456 -v test

被删除的数据就恢复回来了。

注意：如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复呢？如果数据库之前没有备份，所有的 binlog 日志都在的话，就从 binlog 第一个文件开始逐个恢复每个 binlog 文件里的数据，这种一般不太可能，因为 binlog 日志比较大，早期的 binlog 文件会定期删除，所以一般不可能用 binlog 文件恢复整个数据库。

一般推荐的是每天（在凌晨后）做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的 binlog 来恢复数据。备份数据库一般可以用 mysqldump 命令工具。

mysqldump -u root 数据库名 > 备份文件名;            # 备份整个数据库
mysqldump -u root 数据库名 表名字 > 备份文件名;      # 备份整个表

mysql -u root test < 备份文件名                     # 恢复整个数据库，test 为数据库名称，需要自己先建一个数据库 test

3. undo log 回滚日志

InnoDB 对 undo log 文件的管理采用段的方式，也就是回滚段（rollback segment）。每个回滚段记录了 1024 个 undo log segment，每个事务只会使用一个 undo log segment。

在 MySQL 5.5 的时候，只有一个回滚段，那么最大同时支持的事务数量为 1024 个。在 MySQL 5.6 开始，InnoDB 支持最大 128 个回滚段，故其支持同时在线的事务限制提高到了 128 * 1024。

undo log 相关参数。

  • innodb_undo_directory：设置 undo log 文件所在的路径，该参数的默认值为 "./"，即 InnoDB 数据文件存储位置，目录下 ibdata1 文件就是 undo log 存储的位置。
  • innodb_undo_logs：设置 undo log 文件内部回滚段的个数，默认值为 128。
  • innodb_undo_tablespaces：设置 undo log 文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径 innodb_undo_directory 看到 undo 为前缀的文件。

undo log 什么时候删除

  • 新增类型的，在事务提交之后就可以清除掉了。
  • 修改类型的，事务提交之后不能立即清除掉，这些日志会用于 MVCC。只有当没有事务用到该版本信息时才可以清除。

4. 为什么会有 redo log 和 binlog 两份日志

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统 —— 也就是 redo log 来实现 crash-safe 能力。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

5. 为什么 MySQL 不能直接更新磁盘上的数据

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。

磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

MySQL 这套机制看起来复杂，但它可以保证每个更新请求都是更新内存 BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的 MySQL 数据库在较高配置的机器上每秒可以抗下几千甚至上万的读写请求。

6. error log 错误日志

MySQL 还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在 MySQL 数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置
show variables like '%log_error%';

7. general log 通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭 MySQL 服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如 select、show 等，无论 SQL 的语法正确还是错误、也无论 SQL 执行成功还是失败，MySQL 都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

  • general_log：是否开启日志参数，默认为 OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。
  • general_log_file：通用查询日志记录的位置参数。
show variables like '%general_log%';

# 打开通用查询日志
SET GLOBAL general_log=on;
编辑此页
最近更新:
贡献者: juzicoding