MySQL常见的日志类型主要有以下几类：

1. 错误日志（error log）：记录 MySQL 服务在启动、运行或停止时出现的问题，以及运行期间发生的诊断消息，如错误、警告和通知等，对于诊断数据库问题，尤其是与 InnoDB 相关的故障排查非常有用。
2. 二进制日志（binary log，binlog）：记录所有更改数据的语句（DDL 和 DML），但不包括 SELECT 和 SHOW 等不修改数据的语句。主要用于数据恢复、复制和数据审计，是 MySQL 数据库进行主从复制的基础，从库通过读取主库的二进制日志来实现数据的同步，保证主从数据的一致性。
3. 一般查询日志（general query log）：已建立连接的客户端发送给MySQL服务器的所有SQL记录，因为SQL的量比较大，默认是不开启的，也不建议开启。
4. 事务日志-重做日志（redo log）：记录事务中对数据页的修改操作，在数据库发生故障（如宕机、掉电等）后，InnoDB 存储引擎可以根据重做日志将数据恢复到故障前的状态，确保事务的持久性与完整性，保证已提交的事务不会丢失。
   • 刷盘时机：通过 innodb_flush_log_at_trx_commit 参数配置，0 表示提交时不刷盘，每秒执行一次刷盘；1 表示每次事务提交时都将进行刷盘操作，是默认值；2 表示每次事务提交时只把 redo log buffer 内容写入 page cache，每秒执行一次刷盘。
5. 事务日志-撤销日志（undo log）：记录事务开始前或执行过程中产生的数据状态。在事务执行过程中，如果发生错误或者用户执行 ROLLBACK 操作，就可以使用撤销日志将数据回滚到事务开始前的状态，以保证事务的原子性。同时，它也用于实现 MVCC（多版本并发控制）功能，为事务提供一致性的视图。
   • 刷盘时机：先于 commit 数据落盘，在更改对应的 MySQL 数据文件之前保存。
6. 中继日志（relay log）：relay log是入职过程中产生的日志，很多方面都跟binlog差不多。不过，relay log针对的是主从复制中的从库。
7. DDL日志（metadata log）：DDL语句执行的元数据操作。

一般来说，其中binlog、redo log和undo log是比较重要的，需要重点关注。

1. 错误日志 #

1.1 是什么？ #

错误日志是 MySQL 中最重要的日志之一，它记录了 MySQL 服务器启动、运行和停止过程中发生的所有错误信息，同时也会记录一些重要的警告和提示信息。

1.2 有什么用？ #

• 记录MySQL服务启动/停止的关键信息
• 捕获运行时的严重错误（如内存不足）
• 存储警告信息和诊断消息

1.3 关键参数及作用 #

• log_error：指定错误日志的存储路径和文件名。默认情况下，错误日志会存储在 MySQL 数据目录下，文件名为 hostname.err。
• log_warnings：控制是否记录警告信息。设置为 1 表示记录警告信息，设置为 0 则不记录。

1.4 如何使用 #

• 查看日志路径：可以通过以下 SQL 语句查看当前错误日志的路径：

SHOW VARIABLES LIKE 'log_error';

• 查看日志内容：可以使用文本编辑器（如 vi、nano 等）直接打开错误日志文件查看其中的内容。

# 实时监控错误日志
tail -f /var/log/mysql/error.log

# 查找启动失败原因
grep -i 'error' /var/log/mysql/error.log

1.5 注意事项 #

• 日志文件大小：错误日志会不断记录新的信息，如果不及时清理，可能会占用大量的磁盘空间。建议定期备份和清理错误日志。
• 权限问题：确保 MySQL 进程有足够的权限读写错误日志文件，否则可能会导致日志记录失败。
• 默认开启，需定期归档清理
• 日志增长异常可能预示系统问题
• 生产环境建议设置verbosity=2

1.6 高频考点 #

• 如何快速定位数据库启动失败原因？
• 如何调整错误日志的详细级别？

2. 慢查询日志（Slow Query Log） #

2.1 是什么？ #

慢查询日志记录了执行时间超过指定阈值的 SQL 查询语句，这些查询通常是影响数据库性能的关键因素。

2.2 有什么用？ #

• 捕获执行效率低的SQL语句
• 发现索引缺失或设计缺陷
• 优化数据库性能的核心依据

2.3 关键参数及作用 #

• slow_query_log：控制是否启用慢查询日志。设置为 ON 表示启用，设置为 OFF 表示禁用。
• long_query_time：指定慢查询的时间阈值，单位为秒。默认值为 10 秒，即执行时间超过 10 秒的查询会被记录到慢查询日志中。
• log_queries_not_using_indexes：控制是否记录未使用索引的查询语句。设置为 ON 表示记录，设置为 OFF 表示不记录。

SET GLOBAL slow_query_log = ON; -- 开启日志
SET GLOBAL long_query_time = 1; -- 阈值设为1秒
SET GLOBAL log_queries_not_using_indexes = ON; -- 开启记录未使用索引的查询语句

2.4 如何使用 #

• 启用慢查询日志：可以通过以下 SQL 语句临时启用慢查询日志：

SET GLOBAL slow_query_log = 'ON';

若要永久启用，需要在 MySQL 配置文件（如 my.cnf）中添加或修改以下配置：

slow_query_log = 1
long_query_time = 2  # 设置慢查询时间阈值为 2 秒

• 查看慢查询日志内容：可以使用文本编辑器打开慢查询日志文件查看其中的内容，也可以使用 mysqldumpslow 工具对慢查询日志进行分析：

mysqldumpslow /var/lib/mysql/hostname-slow.log

• 日志分析：（使用Mysql官方的慢查询分析调优工具mysqldumpslow）

# 使用内置分析工具
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 生成可视化报告
pt-query-digest /var/log/mysql/mysql-slow.log

2.5 注意事项 #

• 对性能影响约2-5%，建议间歇开启
• 阈值设置需结合业务特点
• 注意log_queries_not_using_indexes参数影响

2.6 高频考点 #

• 如何快速定位全表扫描的SQL？
• 慢查询日志中Lock_time的含义？
• 如何分析慢查询日志。

2.6.1 如何查询当前慢查询语句的个数？ #

在MySQL中有一个变量专门记录当前慢查询语句的个数，可以通过show global status like '%Slow_queries%';命令查看。

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 10    |
+---------------+-------+

2.6.2 如何优化慢SQL？ #

MySQL 为我们提供了<font style="color:rgb(31, 31, 31);">EXPLAIN</font>命令，来获取执行计划的相关信息。

执行计划是一条SQL语句在经过MySQL查询优化器的优化后，具体的执行方式。执行计划通常用于SQL性能分析、优化等场景。通过<font style="color:rgb(31, 31, 31);">EXPLAIN</font>的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表会有多少行记录被查询等信息。

另外，为了更精准定位一条SQL语句的性能问题，需要清除地知道这条SQL语句运行消耗了多少系统资源。<font style="color:rgb(31, 31, 31);">SHOW PROFILE</font>和<font style="color:rgb(31, 31, 31);">SHOW PROFILES</font>展示SQL语句的资源使用情况，展示的消息包括CPU的使用、CPU上下文切换、IO等待、内存使用等。

3. 二进制日志（Binlog） #

3.1 是什么？ #

二进制日志是 MySQL 中一种非常重要的日志，它以二进制格式记录了所有对数据库数据进行修改的 SQL 语句，包括 INSERT、UPDATE、DELETE 等操作。

3.2 有什么用？ #

• 数据恢复：在数据库发生故障或数据丢失时，可以使用二进制日志进行数据恢复，将数据库恢复到某个时间点的状态。
• 主从复制：二进制日志是 MySQL 主从复制的基础，主服务器将二进制日志发送给从服务器，从服务器根据日志内容更新自己的数据。
• 审计：通过分析二进制日志，可以了解数据库中数据的修改历史，进行审计和合规性检查。

3.3 关键参数及作用 #

• log_bin：指定二进制日志的存储路径和文件名前缀。默认情况下，二进制日志文件会存储在 MySQL 数据目录下，文件名格式为 hostname-bin.xxxxxx。
• binlog_format：指定二进制日志的记录格式，有 STATEMENT、ROW 和 MIXED 三种可选。
  • STATEMENT：基于 SQL 语句记录，只记录执行的 SQL 语句。
  • ROW：基于行记录，记录每一行数据的变化。
  • MIXED：混合模式，根据不同的情况选择使用 STATEMENT 或 ROW 模式。

日志格式对比：

SET GLOBAL binlog_format = 'ROW';
SET GLOBAL sync_binlog = 1; -- 每次提交同步
SET GLOBAL expire_logs_days = 7; -- 自动清理

3.4 如何使用 #

• 启用二进制日志：在 MySQL 配置文件（如 my.cnf）中添加或修改以下配置：

log_bin = mysql-bin
binlog_format = ROW

• 查看二进制日志列表：可以使用以下 SQL 语句查看当前 MySQL 服务器上的二进制日志列表：

SHOW BINARY LOGS;

• 查看二进制日志内容：可以使用 mysqlbinlog 工具将二进制日志文件转换为文本格式进行查看：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

3.4.1 数据恢复实战 #

# 导出特定时间点的日志
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
--stop-datetime="2023-01-01 12:00:00" binlog.000001 > recovery.sql

# 执行恢复
mysql < recovery.sql

3.5 注意事项 #

• 磁盘空间：二进制日志会不断记录新的信息，如果不及时清理，可能会占用大量的磁盘空间。建议定期备份和清理二进制日志。
• 性能影响：启用二进制日志会对数据库性能产生一定的影响，尤其是在高并发环境下。因此，需要根据实际情况合理配置二进制日志。

3.6 高频考点 #

• 二进制日志的记录格式及其优缺点。
• 如何启用和管理二进制日志。
• ROW格式下如何查看二进制日志内容？
• 如何保证主从数据强一致性？

3.6.1 binlog主要用来作用什么？ #

binlog 最主要的应用场景是 主从复制 ，主备、主主、主从都离不开binlog，需要依靠 binlog 来同步数据，保证数据一致性。

主从复制的原理如下图所示：

1. 主库将数据库中数据的变化写入到 binlog
2. 从库连接主库
3. 从库会创建一个 I/O 线程向主库请求更新的 binlog
4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
5. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
6. 从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

另外，常见的一些同步 MySQL 数据到其他数据源的工具（比如 Canal）的底层一般也是依赖 binlog 。

Canal 数据同步的原理如下图所示：

1. Canal 模拟 MySQL Slave 节点与 MySQL Master 节点的交互协议，把自己伪装成一个 MySQL Slave 节点，向 MySQL Master 节点请求 binlog；
2. MySQL Master 节点接收到请求之后，根据偏移量将新的 binlog 发送给 MySQL Slave 节点；
3. Canal 接收到 binlog 之后，就可以对这部分日志进行解析，获取主库的结构及数据变更。

除了主从复制之外，binlog 还能帮助我们实现 数据恢复 。当我们误删数据甚至是整个数据库的情况下，就可以使用 binlog 来帮助我们恢复数据。当然了，大前提是已经启用了 binlog 日志。

你可以使用show variables like ’log_bin’;查看数据库是否启用 binlog 日志，默认是开启的。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

3.6.2 binlog的刷盘时机如何选择？ #

对于InnoDB存储引擎而言，事务在执行过程中，会先把日志写入到binlog cache中，只有在事务提交的时候，才会把binlog cache中的日志持久化到磁盘上的binlog文件中。写入内存的速度更快，这样做也是为了效率考虑。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

那么 binlog 是什么时候刷到磁盘中的呢？ 可以通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N，默认为 0 ：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次提交事务的时候都要将binlog写入磁盘；
• N：每 N 个事务，才会将binlog写入磁盘。

MySQL5.7 之前， sync_binlog 默认值为 0。在 MySQL5.7 之后， sync_binlog默认值为 1。

Server version: 8.0.27 MySQL Community Server - GPL
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

通常情况下，不建议将 sync_binlog 的值设置为 0 。如果对性能要求比较高或者出现磁盘 IO 瓶颈的话，可以适当将sync_binlog 的值调大，不过，这样会增加数据丢失的风险。

3.6.3 什么情况下会重新生成binlog？ #

当遇到以下 3 种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

• MySQL服务器停止或重启；
• 使用 flush logs 命令后；
• binlog 文件大小超过 max_binlog_size变量的阈值后。

4. 重做日志（Redo Log） #

4.1 是什么？ #

重做日志是 InnoDB 存储引擎特有的日志，它记录了 InnoDB 存储引擎对数据页的修改操作，用于保证数据的持久性和事务的原子性。

4.2 有什么用？ #

• 数据恢复：在数据库发生故障（如崩溃、断电等）时，InnoDB 存储引擎可以通过重做日志将未写入磁盘的数据页恢复到故障发生前的状态，保证数据的一致性。
• 提高性能：InnoDB 存储引擎采用了 Write-Ahead Logging（WAL）策略，即先将修改操作记录到重做日志中，然后再将数据页写入磁盘。这样可以减少磁盘 I/O 操作，提高数据库的性能。
• 保证事务的持久性（ACID中的D）
• 循环写入的高效设计

4.3 关键参数及作用 #

• innodb_log_file_size：指定每个重做日志文件的大小。默认值为 48MB，增大该值可以减少日志文件的切换次数，提高性能，但会增加恢复时间。
• innodb_log_files_in_group：指定重做日志文件组中的日志文件数量。默认值为 2，建议设置为 2 - 4 个。
• innodb_log_group_home_dir：指定重做日志文件的存储路径。默认情况下，重做日志文件会存储在 MySQL 数据目录下。

4.4 如何使用 #

重做日志是 InnoDB 存储引擎自动管理的，一般不需要用户手动干预。但在进行数据库配置时，可以根据实际情况调整重做日志的相关参数。例如，在 my.cnf 配置文件中添加或修改以下配置：

innodb_log_file_size = 256M
innodb_log_files_in_group = 3

4.5 注意事项 #

• 合理设置日志总大小（一般4-8G）：重做日志文件的大小应根据数据库的写入量和恢复时间要求进行合理配置。如果日志文件太小，会导致频繁的日志文件切换，影响性能；如果日志文件太大，会增加恢复时间。
• 磁盘空间：重做日志文件会占用一定的磁盘空间，需要确保磁盘有足够的空间存储重做日志。
• 监控日志写满频率：<font style="color:rgb(64, 64, 64);">SHOW STATUS LIKE 'Innodb_os_log%';</font>
• 避免SSD磁盘的写放大问题

4.6 刷盘策略 #

SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- 最高安全级别
/* 
参数说明：
0 - 每秒刷盘，可能丢失1秒数据
1 - 每次提交刷盘（默认）
2 - 提交后写入OS缓存
*/

4.7 高频考点 #

• 事务提交时redo log的写入流程？
• crash recovery的具体过程？
• 重做日志的作用和工作原理。
• 如何配置重做日志的相关参数。

4.7.1 redo log如何保证事务的持久性？ #

我们知道 InnoDB 存储引擎是以页为单位来管理存储空间的，我们往 MySQL 插入的数据最终都是存在于页中的，准确点来说是数据页这种类型。为了减少磁盘 IO 开销，还有一个叫做 Buffer Pool(缓冲池) 的区域，存在于内存中。当我们的数据对应的页不存在于 Buffer Pool 中的话， MySQL 会先将磁盘上的页缓存到 Buffer Pool 中，这样后面我们直接操作的就是 Buffer Pool 中的页，这样大大提高了读写性能。

一个事务提交之后，我们对 Buffer Pool 中对应的页的修改可能还未持久化到磁盘。这个时候，如果 MySQL 突然宕机的话，这个事务的更改是不是直接就消失了呢？

很显然是不会的，如果是这样的话就明显违反了事务的持久性。

MySQL InnoDB 引擎使用 redo log 来保证事务的持久性。redo log 主要做的事情就是记录页的修改，比如某个页面某个偏移量处修改了几个字节的值以及具体被修改的内容是什么。redo log 中的每一条记录包含了表空间号、数据页号、偏移量、具体修改的数据，甚至还可能会记录修改数据的长度（取决于 redo log 类型）。

在事务提交时，我们会将 redo log 按照刷盘策略刷到磁盘上去，这样即使 MySQL 宕机了，重启之后也能恢复未能写入磁盘的数据，从而保证事务的持久性。也就是说，redo log 让 MySQL 具备了崩溃恢复能力。

InnoDB 将 redo log 刷到磁盘上有几种情况：

1. 事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘（可以通过innodb_flush_log_at_trx_commit参数控制）。
2. log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
3. 事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
4. Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
5. 后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。也就是说，一个没有提交事务的 redo log 记录，也可能会被刷盘。
6. 正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

总之，InnoDB 在多种情况下会刷新 redo log 到磁盘，以保证数据的持久性和一致性。

我们要注意设置正确的刷盘策略innodb_flush_log_at_trx_commit 。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。

• 0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
• 1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
• 2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

性能：0 > 2 > 1 ，安全性：1 > 2 > 0 。

刷盘策略innodb_flush_log_at_trx_commit 的默认值为 1，设置为 1 的时候才不会丢失任何数据。为了保证事务的持久性，我们必须将其设置为 1。当然了，如果你的项目能容忍轻微的数据丢失的话，那将innodb_flush_log_at_trx_commit的值设置为 2 或许是更好的选择。

redo log 采用循环写的方式进行写入，如下图所示。

<font style="color:rgb(38, 38, 38);">write pos</font> 表示 redo log 当前记录写到的位置， <font style="color:rgb(38, 38, 38);">check point</font> 表示当前要擦除的位置。当<font style="color:rgb(38, 38, 38);">write pos</font>追上<font style="color:rgb(38, 38, 38);">check point</font>时，表示 redo log 文件被写满了。这个时候，MySQL 没办法执行更新操作，也就是说数据库更新操作会被阻塞，因为无法再写入 redo log 日志。为了保证 MySQL 更新操作的正常执行，需要执行 CheckPoint 刷盘机制。CheckPoint 会按照一定的条件将内存中的脏页刷到磁盘上。成功刷盘之后，checkpoint 会向后移动（顺时针方向）。这样的话，才能继续写入 redo log 日志，阻塞的更新操作才能继续执行。

4.7.2 什么情况下会出现数据丢失？ #

如果你理解了 redo log 原理的话，应该可以很容易想到下面这两种情况：

1. redo log 写入 log buffer 但还未写入 page cache ，此时数据库崩溃，就会出现数据丢失（刷盘策略<font style="color:rgb(38, 38, 38);">innodb_flush_log_at_trx_commit</font> 的值为 0 时可能会出现这种数据丢失）；
2. redo log 已经写入 page cache 但还未写入磁盘，操作系统奔溃，也可能出现数据丢失（刷盘策略<font style="color:rgb(38, 38, 38);">innodb_flush_log_at_trx_commit</font> 的值为 2 时可能会出现这种数据丢失）。

4.7.3 页修改之后为什么不直接刷盘呢？ #

很多人可能要问了：为什么每次修改 Buffer Pool 中的页之后不直接刷盘呢？这样不就不需要 redo log 了嘛！

这种方式必然是不行的，性能非常差。最大的问题就是 InnoDB 页的大小一般为 16KB，而页又是磁盘和内存交互的基本单位。这就导致即使我们只修改了页中的几个字节数据，一次刷盘操作也需要将 16KB 大小的页整个都刷新到磁盘中。而且，这些修改的页可能并不相邻，也就是说这还是随机 IO。

采用 redo log 的方式就可以避免这种性能问题，因为 redo log 的刷盘性能很好。首先，redo log 的写入属于顺序 IO。 其次，一行 redo log 记录只占几十个字节。

另外，Buffer Pool 中的页（脏页）在某些情况下（比如 redo log 快写满了）也会进行刷盘操作。不过，这里的刷盘操作会合并写入，更高效地顺序写入到磁盘。

4.7.4 binlog和redo log的区别是什么？ #

• binlog 主要用于数据库还原，属于数据级别的数据恢复，主从复制是 binlog 最常见的一个应用场景。redolog 主要用于保证事务的持久性，属于事务级别的数据恢复。
• redolog 属于 InnoDB 引擎特有的，binlog 属于所有存储引擎共有的，因为 binlog 是 MySQL 的 Server 层实现的。
• redolog 属于物理日志，主要记录的是某个页的修改。binlog 属于逻辑日志，主要记录的是数据库执行的所有 DDL 和 DML 语句。
• binlog 通过追加的方式进行写入，大小没有限制。redo log 采用循环写的方式进行写入，大小固定，当写到结尾时，会回到开头循环写日志。

核心区别：

本质区别：
Binlog用于数据归档和逻辑恢复，Redo Log用于物理数据页恢复，二者通过两阶段提交保证一致性

5. 撤销日志（Undo Log） #

5.1 是什么？ #

撤销日志是 InnoDB 存储引擎特有的日志，它记录了事务对数据进行修改之前的原始数据，用于实现事务的回滚和多版本并发控制（MVCC）。

5.2 有什么用？ #

• 事务回滚：当事务发生错误或需要取消时，可以使用撤销日志将事务对数据的修改操作回滚到事务开始之前的状态。
• 多版本并发控制（MVCC）：撤销日志为 MVCC 提供了支持，使得不同事务可以同时访问同一数据的不同版本，提高了数据库的并发性能。
• 提供一致性读视图

5.3 关键参数及作用 #

• innodb_undo_logs：指定撤销日志文件的数量。默认值为 128，一般不需要修改。
• innodb_undo_tablespaces：指定撤销日志文件所在的表空间数量。默认值为 0，表示撤销日志存储在系统表空间中。

5.4 如何使用 #

撤销日志是 InnoDB 存储引擎自动管理的，一般不需要用户手动干预。但在进行数据库配置时，可以根据实际情况调整撤销日志的相关参数。例如，在 my.cnf 配置文件中添加或修改以下配置：

innodb_undo_logs = 256
innodb_undo_tablespaces = 2

5.5 注意事项 #

• 磁盘空间：撤销日志会占用一定的磁盘空间，需要确保磁盘有足够的空间存储撤销日志。
• 事务长时间未提交：如果事务长时间未提交，会导致撤销日志无法及时清理，从而占用大量的磁盘空间。因此，应尽量避免长时间未提交的事务。
• 长事务会导致undo空间膨胀
• 监控长事务：<font style="color:rgb(64, 64, 64);">SELECT * FROM information_schema.INNODB_TRX;</font>
• 避免超过<font style="color:rgb(64, 64, 64);">innodb_max_undo_log_size</font>限制

5.6 高频考点 #

• Undo log如何支持MVCC？
• 如何解决undo空间不足问题？
• 撤销日志的作用和工作原理。
• 撤销日志与多版本并发控制（MVCC）的关系。

5.6.1 undo log如何保证事务的原子性？ #

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，记录的是 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。并且，undo-log 本身是会被删除清理的，例如 INSERT 操作，在事务提交之后就可以清除掉了；UPDATE/DELETE 操作在事务提交不会立即删除，会加入 history list，由后台线程 purge 进行清理。

undo log 是采用 segment（段）的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment（undo 日志段），undo log segment 包含在 rollback segment（回滚段）中。事务开始时，需要为其分配一个 rollback segment。每个 rollback segment 有 1024 个 undo log segment，这有助于管理多个并发事务的回滚需求。

通常情况下， rollback segment header（通常在回滚段的第一个页）负责管理 rollback segment。rollback segment header 是 rollback segment 的一部分，通常在回滚段的第一个页。history list 是 rollback segment header 的一部分，它的主要作用是记录所有已经提交但还没有被清理（purge）的事务的 undo log。这个列表使得 purge 线程能够找到并清理那些不再需要的 undo log 记录。

5.6.2 除了保证事务的原子性，undo log还有什么用？ #

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

6. 中继日志（Relay Log） #

6.1 是什么？ #

中继日志是 MySQL 主从复制中从服务器使用的一种日志，它用于存储从主服务器接收到的二进制日志内容。

6.2 有什么用？ #

• 主从复制：中继日志是 MySQL 主从复制的中间桥梁，从服务器将主服务器发送的二进制日志内容存储在中继日志中，然后根据中继日志中的内容更新自己的数据。

6.3 关键参数及作用 #

• relay_log：指定中继日志的存储路径和文件名前缀。默认情况下，中继日志文件会存储在 MySQL 数据目录下，文件名格式为 hostname-relay-bin.xxxxxx。
• relay_log_purge：控制是否自动清理不再需要的中继日志文件。设置为 ON 表示自动清理，设置为 OFF 表示不自动清理。

6.4 如何使用？ #

中继日志是 MySQL 主从复制过程中自动生成和管理的，一般不需要用户手动干预。但在进行主从复制配置时，可以根据实际情况调整中继日志的相关参数。例如，在从服务器的 my.cnf 配置文件中添加或修改以下配置：

relay_log = mysql-relay-bin
relay_log_purge = ON

6.5 注意事项 #

• 磁盘空间：中继日志会不断记录从主服务器接收到的二进制日志内容，如果不及时清理，可能会占用大量的磁盘空间。建议启用 relay_log_purge 参数，自动清理不再需要的中继日志文件。
• 主从复制延迟：如果中继日志处理不及时，可能会导致主从复制延迟。需要监控主从复制的状态，及时处理中继日志积压的问题。

6.6 高频考点 #

• 中继日志的作用和工作原理。
• 如何配置和管理中继日志。
• 如何解决主从复制延迟问题。
• 主从复制延迟的主要原因？
• Relay Log损坏如何处理？

6.6.1 主从复制流程 #

1. Master写Binlog
2. Slave I/O线程拉取Binlog
3. 写入Relay Log
4. Slave SQL线程执行Relay Log

7. 日志体系全景图 #

graph TD
    A[客户端请求] --> B[查询解析]
    B --> C{写操作?}
    C -->|是| D[Undo Log记录旧值]
    C -->|否| E[执行查询]
    D --> F[更新数据页]
    F --> G[Redo Log记录变更]
    G --> H[Binlog记录逻辑操作]
    H --> I[返回客户端]
    I --> J[两阶段提交]

8. 问答 #

8.1 如何实现崩溃恢复的精确恢复？ #

三阶段恢复机制：

1. Prepare阶段扫描：
   • 检查Binlog最后一个完整事务
   • 通过XID（事务ID）关联Redo Log和Binlog
2. Redo Log重放：

/* 重做所有已提交和未提交的事务 */
redo log → 恢复数据页到崩溃前状态

3. Undo Log回滚：

/* 回滚所有未提交的事务 */
undo log → 保证事务原子性

关键保障：

• 两阶段提交（2PC）协议
• <font style="color:rgb(64, 64, 64);">innodb_support_xa=ON</font>（默认开启）
• Binlog的<font style="color:rgb(64, 64, 64);">sync_binlog=1</font>配置

8.2 大事务会对日志系统产生什么影响？ #

五大核心影响：

1. 日志膨胀：

• Binlog单文件可能超过<font style="color:rgb(64, 64, 64);">max_binlog_size</font>
• Undo Log空间占用激增（<font style="color:rgb(64, 64, 64);">innodb_max_undo_log_size</font>）

2. 锁竞争加剧：

SHOW ENGINE INNODB STATUS; 
/* 观察TRANSACTIONS部分的锁等待 */

3. 复制延迟：

• 主库并行写入 vs 从库串行执行
• Relay Log堆积导致<font style="color:rgb(64, 64, 64);">Seconds_Behind_Master</font>升高

4. 恢复时间激增：

• 崩溃恢复时需要扫描大量Redo Log
• PITR（时间点恢复）效率下降

5. 内存压力：

• 长事务导致Undo版本链过长
• 可能触发<font style="color:rgb(64, 64, 64);">innodb_rollback_segments</font>限制

解决方案：

• 拆分为小批量操作（<font style="color:rgb(64, 64, 64);">LIMIT 1000</font>分批提交）
• 使用<font style="color:rgb(64, 64, 64);">pt-online-schema-change</font>在线DDL
• 监控长事务：<font style="color:rgb(64, 64, 64);">SELECT * FROM information_schema.INNODB_TRX;</font>

8.3 主从复制中如何保证数据一致性？ #

四级保障体系：

1. 传输层保障：

/* 半同步复制 */
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_slave_enabled=1;

2. 日志格式选择：

• 生产环境推荐<font style="color:rgb(64, 64, 64);">binlog_format=ROW</font>
• 避免STATEMENT格式的函数不确定性

3. GTID机制：

/* 全局事务标识 */
SET GLOBAL gtid_mode=ON;
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

4. 校验机制：

# 数据一致性校验
pt-table-checksum --databases=test
pt-table-sync --execute

异常处理：

• 中继日志损坏：<font style="color:rgb(64, 64, 64);">STOP SLAVE; RESET SLAVE; CHANGE MASTER TO...</font>
• 数据漂移：<font style="color:rgb(64, 64, 64);">pt-table-sync</font>在线修复

8.4 如何配置安全的日志刷盘策略？ #

黄金配置组合：

/* Binlog安全配置 */
SET GLOBAL sync_binlog=1;  -- 每次提交同步

/* Redo Log安全配置 */
SET GLOBAL innodb_flush_log_at_trx_commit=1; 

/* 操作系统层配置 */
echo 'deadline' > /sys/block/sda/queue/scheduler  # I/O调度策略

不同场景优化方案：

监控指标：

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';  -- Redo Log等待次数
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';  -- Binlog缓存溢出