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

一、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*10242.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_commit1默认、最安全每次提交必刷盘金融、支付必须用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(N1)表示每次提交事务都write 到page cache但累积N个事务后才 fsync 写入磁盘这种如果机器宕机会丢失N个事务的binlog。发生以下任何事件时, binlog日志文件会重新生成服务器启动或重新启动服务器刷新日志执行命令flush logs日志文件大小达到 max_binlog_size 值默认值为 1GB4.5 删除Binlog日志文件4.6 查看Binlog日志文件4.7 Binlog数据恢复实操前提是binlogON 且 binlog_formatROW如果不是 ROW 模式只能恢复库 / 表级无法精准恢复行数据。少量数据删除恢复定位误删的 binlog 区间时间 / 位置mysqlbinlog --base64-outputdecode-rows -v --start-datetime2026-05-22 10:00:00 --stop-datetime2026-05-22 10:10:00 /var/lib/mysql/mysql-bin.000123 delete_log.sql我们先查看binlog日志文件--base64-outputdecode-rows -v把行级事件翻译成可读伪 SQL能看到被删行的完整数据。打开delete_log.sql查询对应的删除语句,找到事务的起止位置把delete语句反写成insert语句,在数据库执行如果是批量数据误删或者是删表删库则需要通过最新的全量备份备份时间点到当前的binlog来恢复数据,执行如下sql执行过程需要过滤掉误删除的sql,按时间段来执行binlog日志,具体的查询语句的时间节点上述已经描述,这就不错过多冗余介绍4.8 Redo Log vs Binlog 面试终极区别维度Redo LogBinlog层级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 logInnoDB 就可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash safe。4.10 两阶段提交2PC核心原理为了保证 redo log 和 binlog数据一致InnoDB 使用 2PCPrepare阶段写 redo log状态prepare、写 binlogCommit阶段redo log 打上 commit 标记宕机恢复判断有 prepare 无 commit判断 binlog 是否完整binlog 完整则提交不完整则回滚保证主从数据绝对一致五、Slow Query Log 慢查询日志性能优化核心5.1 作用记录执行时间超过阈值的SQL是慢SQL优化、排查数据库卡顿的核心依据。5.2 核心参数slow_query_logON开启慢日志long_query_time1超过1秒即为慢SQLlog_queries_not_using_indexes记录未走索引SQL5.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写入binlogredo 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 为全局通用日志。十、全文终极口诀Undo回滚存版本MVCC靠它撑场Redo重做防宕机WAL机制保高强Binlog复制做恢复逻辑日志主从帮Slow查慢优性能Error排错救故障两层日志分清楚底层通透不慌张。