当前位置：首页 > news >正文

MySQL调优实战：MySQL日志机制深入解析，redo/undo/binlog/slow/error日志底层全通透

news 2026/5/23 3:22:36

一、MySQL五大日志总览（全局认知）

MySQL 日志严格分为两层：Server层日志 + InnoDB引擎层日志。

这是90%人混淆的根源：

1.1 Server层日志（所有引擎通用）

Binlog（二进制日志）：主从复制、数据恢复、逻辑日志
Slow Query Log（慢查询日志）：SQL性能优化
Error Log（错误日志）：启动、崩溃、异常排查

1.2 InnoDB引擎层日志（仅InnoDB独有）

Undo Log（回滚日志）：事务回滚 + MVCC多版本
Redo Log（重做日志）：崩溃恢复 + 事务持久性

总结：
Binlog管逻辑、Redo管物理；Undo管版本与回滚，Slow管性能，Error管异常。

二、Undo Log 回滚日志（MVCC核心）

2.1 核心作用（必背）

Undo Log 只有两个作用：

事务回滚：保证事务原子性
MVCC多版本快照：保存历史数据版本，实现无锁读

2.2 存储内容

记录修改前的旧数据。

每次 update/delete 不会覆盖数据，而是把旧数据存入 undo log，通过行隐藏字段DB_ROLL_PTR串联成版本链。

2.3 管理方式

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

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

2.4 常用参数

2.5 生命周期

事务执行：生成 undo log
事务回滚：用 undo 恢复数据
事务提交：undo不会立刻删除,针对新增的记录,在事务提交后就可以删除
等待没有事务引用该版本后，后台线程 purge 清理

2.6 生产致命坑点

长事务 = undo 版本链无法清理 = 磁盘暴涨 + 查询变慢

长事务不提交，会导致大量 undo 堆积，MVCC 构造数据可视图时需要回溯超长版本链，SQL 越来越慢。

三、Redo Log 重做日志（崩溃恢复核心）

3.1 诞生目的

解决磁盘随机写太慢的问题。

MySQL 修改数据先改内存，不会立刻刷磁盘，依靠 redo log 保证宕机不丢数据。

3.2 核心特性

物理日志：记录「数据页第几偏移量、改成了什么」
环形循环写入：ib_logfile0、ib_logfile1 循环覆盖
只属于InnoDB
保证事务持久性

3.3 写入流程（核心）

WAL机制：先写日志，后写数据

修改 Buffer Pool 内存数据
写入 Log Buffer
按策略刷盘到 redo log 文件
后台线程空闲时刷盘真实数据页

3.4 崩溃恢复原理

重启 MySQL 时,扫描 redo 日志，区分事务状态：

已提交事务：redo 重做，把没刷盘的数据补上
未提交事务,未有prepare标志位：直接丢弃，保证数据一致性
未提交事务,有prepare标志位,且无binlog日志:直接丢弃，保证数据一致性
未提交事务,有prepare标志位,且有binlog日志:redo 重做，把没刷盘的数据补上

3.5 核心参数：innodb_flush_log_at_trx_commit

1（默认、最安全）：每次提交必刷盘，金融、支付必须用
2：写系统缓存、定时落盘，性能折中
0：每秒刷盘，性能最高、断电丢数据

3.6 关键参数

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。
innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

3.7redo log 写入磁盘过程分析

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

write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。

write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

3.8redo log写入策略参看下图：

四、Binlog 二进制日志（主从&恢复核心）

4.1 基本定位

Server层逻辑日志，所有存储引擎通用。

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作，不是物理页修改。

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。在配置文件中的[mysqld]部分增加如下配置

4.2 两大核心作用

主从复制：从库拉取 binlog 回放同步数据
数据恢复：误删数据可通过 binlog 时间点恢复

4.3 Binlog三种格式

Statement：记录SQL语句，节省空间，但是对于一些执行过程中才能确定结果的函数，比如UUID()、 SYSDATE()等函数如果随sql同步到slave机器去执行，可能主从数据不一致
Row（生产推荐）：记录行变更，精准、无误差、数据安全
Mixed：混合模式，自动适配，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些
在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

4.4 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。

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

服务器启动或重新启动
服务器刷新日志，执行命令flush logs
日志文件大小达到 max_binlog_size 值，默认值为 1GB

4.5 删除Binlog日志文件

4.6 查看Binlog日志文件

4.7 Binlog数据恢复实操

前提是binlog=ON 且 binlog_format=ROW，如果不是 ROW 模式，只能恢复库 / 表级，无法精准恢复行数据。

少量数据删除恢复

定位误删的 binlog 区间（时间 / 位置）

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2026-05-22 10:00:00" --stop-datetime="2026-05-22 10:10:00" /var/lib/mysql/mysql-bin.000123 > delete_log.sql

我们先查看binlog日志文件

--base64-output=decode-rows -v：把行级事件翻译成可读伪 SQL（能看到被删行的完整数据）。
打开delete_log.sql查询对应的删除语句,找到事务的起止位置

把delete语句反写成insert语句,在数据库执行

如果是批量数据误删或者是删表删库

则需要通过最新的全量备份+备份时间点到当前的binlog来恢复数据,执行如下sql：

执行过程需要过滤掉误删除的sql,按时间段来执行binlog日志,具体的查询语句的时间节点上述已经描述,这就不错过多冗余介绍

4.8 Redo Log vs Binlog 面试终极区别

维度	Redo Log	Binlog
层级	InnoDB引擎层	Server服务层
类型	物理日志（页变更）	逻辑日志（SQL/行变更）
作用	崩溃恢复、持久化	复制、备份、恢复
引擎	仅InnoDB	所有引擎通用
写入方式	环形循环覆盖	追加写入，永不覆盖

4.9 为什么会有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。

4.10 两阶段提交（2PC）核心原理

为了保证 redo log 和 binlog数据一致，InnoDB 使用 2PC：

Prepare阶段：写 redo log（状态prepare）、写 binlog
Commit阶段：redo log 打上 commit 标记

宕机恢复判断：

有 prepare 无 commit：判断 binlog 是否完整
binlog 完整则提交，不完整则回滚

保证主从数据绝对一致

五、Slow Query Log 慢查询日志（性能优化核心）

5.1 作用

记录执行时间超过阈值的SQL，是慢SQL优化、排查数据库卡顿的核心依据。

5.2 核心参数

slow_query_log=ON：开启慢日志
long_query_time=1：超过1秒即为慢SQL
log_queries_not_using_indexes：记录未走索引SQL

5.3 生产价值

线上数据库 CPU 高、IO 高、接口超时，90%都能从慢日志找到根源。

六、Error Log 错误日志（故障排查核心）

6.1 记录内容

MySQL启动、关闭、重启信息
崩溃宕机原因
参数错误、权限错误、文件损坏
连接异常、引擎报错

数据库莫名其妙挂掉、启动失败，优先看 error log

七、通用查询日志

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

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

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

八、五大日志联动全局总结（终极闭环）

一条更新SQL完整日志流程：

执行更新，先写入undo log（用于回滚、MVCC）
修改内存数据，写入redo log prepare
写入binlog
redo log 标记 commit
后台刷盘数据页
SQL过慢则记录slow log
出现崩溃则记录error log

九、面试极简总结（背诵版）

MySQL 包含五大日志：undo log 实现事务回滚与MVCC多版本；redo log 基于WAL机制实现崩溃恢复，保障事务持久性；binlog 是Server层逻辑日志，支撑主从复制与数据恢复，通过两阶段提交保证与redo log一致；slow log 用于慢SQL性能排查；error log 用于故障定位。其中 redo/undo 为InnoDB独有，binlog/slow/error 为全局通用日志。