1 二进制日志（Binlog）：配置与核心作用

Binlog 是 MySQL 中跨存储引擎的核心日志，记录所有数据修改操作，主要用于主从复制、数据备份恢复与跨库迁移。

1.1 Binlog 核心操作

开启 Binlog

若需开启 Binlog，需在 MySQL 配置文件的 [mysqld] 节点下添加以下配置（配置文件路径需根据实际部署情况调整）：

# 编辑配置文件（以常见路径为例）
vim /data/mysql/conf/my.cnf# 添加 Binlog 配置：指定日志存储路径与前缀
log-bin = /data/mysql/binlog/mysql-bin

配置后需重启 MySQL 服务使设置生效：

/etc/init.d/mysql.server restart

验证 Binlog 状态

通过以下命令查看 Binlog 是否已开启：

show variables like '%log_bin%';

若结果中 log_bin 取值为 ON，表示 Binlog 已启用。

关闭 Binlog

关闭 Binlog 分为「全局关闭」与「当前会话关闭」两种场景：

• 全局关闭（永久生效）：

  # 1. 编辑配置文件
  vim /data/mysql/conf/my.cnf# 2. 注释原 log-bin 配置
  # log-bin = /data/mysql/binlog/mysql-bin# 3. 添加关闭参数（二选一即可）
  skip_log_bin
  # 或
  disable_log_bin# 4. 重启 MySQL 服务
  /etc/init.d/mysql.server restart# 5. 验证关闭结果
  show variables like '%log_bin%';
  

• 当前会话关闭（临时生效，重启后失效）：

  set sql_log_bin = 0;
  

配置 Binlog 大小限制

默认情况下，单个 Binlog 文件最大为 1G，可通过配置调整大小：

# 1. 编辑配置文件
vim /data/mysql/conf/my.cnf# 2. 添加大小限制（示例：设置为 512M）
max-binlog-size = 512M# 3. 重启 MySQL 服务
/etc/init.d/mysql.server restart# 4. 验证配置
show variables like 'max_binlog_size';# 5. （可选）单位换算：将字节数转为 GB（示例）
select @@max_binlog_size / 1024 / 1024 / 1024 as max_binlog_size_gb;

1.2 Binlog 的三大核心作用

1. 主从复制：主库将数据变更记录到 Binlog，从库通过 IO 线程读取主库 Binlog 并写入本地中继日志，再通过 SQL 线程回放中继日志，实现主从数据同步。
2. 数据备份与恢复：当发生误操作（如误删表、误更新）时，可通过「全量备份 + 增量 Binlog」恢复数据——先恢复全量备份，再执行从备份时间点到误操作前的 Binlog，排除错误操作即可还原数据。
3. 跨库迁移：迁移 MySQL 数据到其他数据库（如 TiDB、ClickHouse）时，可通过工具（如 Canal、Otter）监听 Binlog，捕捉增量数据变更并同步至目标数据库，实现无感知迁移。

2 二进制日志（Binlog）的记录格式

Binlog 支持三种记录格式，分别适用于不同场景，需根据业务需求选择。

2.1 三种记录格式说明

• STATEMENT：记录完整的 SQL 语句（如 update user set name='test' where id=1），不记录行级数据变化。
• ROW：记录行级数据的具体修改（如「将 id=1 的行的 name 从 ‘old’ 改为 ‘test’」），不记录原始 SQL。
• MIXED：默认使用 STATEMENT 格式，当 SQL 语句可能导致主从不一致（如使用 uuid()、now() 等非确定性函数）时，自动切换为 ROW 格式。

2.2 三种格式的优缺点对比

格式	优点	缺点	适用场景
STATEMENT	日志体积小、IO 开销低、易阅读、性能高	主从可能不一致、不支持闪回	无特殊函数的简单业务、对日志体积敏感场景
ROW	主从数据绝对一致、支持闪回	日志体积大、IO 开销高、不易直接阅读	主从强一致需求、需闪回数据的场景
MIXED	兼顾体积与一致性，自动适配场景	不支持闪回、部分高可用架构不兼容	大多数通用业务场景

2.3 修改 Binlog 记录格式

Binlog 格式修改支持「全局永久生效」与「会话临时生效」：

• 全局永久生效（需重启）：

  # 1. 编辑配置文件
  vim /data/mysql/conf/my.cnf# 2. 添加格式配置（取值：ROW/STATEMENT/MIXED）
  binlog_format = ROW# 3. 重启 MySQL 服务
  /etc/init.d/mysql.server restart
  

• 会话临时生效（仅当前连接）：

  set session binlog_format = 'ROW';
  

• 全局临时生效（所有新连接，重启后失效）：

  set global binlog_format = 'ROW';
  

3 二进制日志（Binlog）的解析方法

解析 Binlog 是定位数据变更、排查问题的关键操作，常见解析方式包括「基于位点」「基于时间」「基于 GTID」等。

3.1 基于位点解析

通过 Binlog 文件的「起始位点」定位解析范围，适用于已知具体变更位置的场景：

1. 查看当前 Binlog 文件、位点与 GTID：

   show master status\G
   

   结果中 File 为当前 Binlog 文件名，Position 为当前位点。
   

2. 执行测试操作（如创建表、插入数据），生成 Binlog 记录：

   # 示例：创建测试表并插入数据
   create table martin.binlog_test(id int);
   insert into martin.binlog_test select 1;
   

3. 再次查看位点，确认变更后的位点范围：

   show master status\G
   

   

4. 进入 Binlog 存储目录，执行解析命令：

   # 进入目录（路径与开启 Binlog 时配置一致）
   cd /data/mysql/binlog/# 解析：从起始位点 618 开始，输出到指定文件
   mysqlbinlog --start-position=618 mysql-bin.000019 -vv > /data/01.sql
   

   其中 -vv 表示输出详细行数据，便于查看具体变更。

5. 查看解析结果：

   cat /data/01.sql
   

   
   

3.2 基于时间解析

通过「起始时间」定位解析范围，适用于已知变更时间的场景：

# 解析 2023-05-08 22:30:00 之后的 Binlog 记录
mysqlbinlog --start-datetime="2023-05-08 22:30:00" mysql-bin.000019 > /data/02.sql# 查看解析结果
cat /data/02.sql

若需限定时间范围，可添加 --stop-datetime 参数（如 --stop-datetime="2023-05-08 23:00:00"）。

3.3 基于 GTID 解析

GTID（全局事务ID）是 MySQL 8.0 推荐的事务标识，通过 GTID 解析可精准定位单个事务的 Binlog：

1. 查看当前 GTID（需先开启 GTID 功能）：

   show master status\G
   

   结果中 Executed_Gtid_Set 包含已执行的 GTID。
   

2. 基于 GTID 解析 Binlog：

   # 解析指定 GTID 的事务（示例 GTID：6a116700-70e3-11f0-b555-00163e007cf5:1-675429）
   mysqlbinlog --include-gtids '6a116700-70e3-11f0-b555-00163e007cf5:1-675429' mysql-bin.000007 -vv > /data/03.sql# 查看解析结果
   cat /data/03.sql
   

3.4 仅解析指定数据库的 Binlog

通过 -d 参数指定数据库，仅解析该库的变更记录，适用于多库隔离场景：

1. 执行跨库测试操作，生成多库 Binlog：

   # 查看当前位点
   show master status\G# 在 martin 库创建表
   use martin;
   create table binlog_test_01(id int);# 在 test 库创建表
   use test;
   create table aaa(id int);# 查看变更后位点
   show master status\G
   

2. 仅解析 martin 库的 Binlog：

   # 解析 martin 库，限定位点范围（2147 至 2540）
   mysqlbinlog --start-position=2147 --stop-position=2540 -d martin mysql-bin.000012 -vv > /data/04.sql# 查看解析结果（仅包含 martin 库的变更）
   cat /data/04.sql
   

3.5 解析加密的 Binlog

MySQL 支持对 Binlog 加密存储，解析时需通过 MySQL 服务认证，无法直接读取本地文件。

步骤 1：开启 Binlog 加密

# 1. 编辑配置文件
vim /data/mysql/conf/my.cnf# 2. 添加加密配置
early-plugin-load = keyring_file.so  # 加载密钥文件插件
keyring_file_data = /data/mysql/keyring  # 密钥存储路径
binlog_encryption = on  # 开启 Binlog 加密# 3. 重启 MySQL 服务
/etc/init.d/mysql.server restart# 4. 验证加密是否开启
show binary logs;

步骤 2：解析加密 Binlog

直接读取加密文件会报错，需通过 --read-from-remote-server 参数从 MySQL 服务读取：

# 解析加密 Binlog（需输入 MySQL 用户名与密码）
mysqlbinlog --read-from-remote-server -uroot -p123456 --start-position=155 mysql-bin.000020 -vv > /data/05.sql# 查看解析结果
cat /data/05.sql

其中 --read-from-remote-server 表示从服务端读取 Binlog，而非本地文件。

步骤 3：关闭 Binlog 加密

# 1. 编辑配置文件，移除加密相关配置
vim /data/mysql/conf/my.cnf
# 删除以下行：
# early-plugin-load = keyring_file.so
# keyring_file_data = /data/mysql/keyring
# binlog_encryption = on# 2. 备份旧加密 Binlog（避免重启失败）
# 把当前的Binlog移除，因为重启MySSQL会加载之前的Binlog，而之前的Binlog是加密的，会重启失败
# 重建备份目录backup，并且把Binlog移到该目录下
mkdir /data/mysql/backup
mv /data/mysql/binlog/* /data/mysql/backup/# 3. 重启 MySQL 服务
/etc/init.d/mysql.server restart# 4. 验证加密关闭
show binary logs;

4 二进制日志（Binlog）的清除策略

Binlog 会持续占用磁盘空间，需定期清除无用日志，避免磁盘满载。

4.1 三种清除方式

方式 1：自动清除（推荐）

通过配置 Binlog 保留时间，MySQL 会自动删除过期日志：

1. 查看当前保留时间配置：

   # 查看保留天数（未来将废弃）
   show global variables like "expire_logs_days";# 查看保留秒数（推荐使用，MySQL 8.0+ 默认）
   show global variables like "binlog_expire_logs_seconds";
   

2. 动态调整保留时间（示例：保留 7 天）：

   # 先将 expire_logs_days 设为 0（避免与秒数参数冲突）
   set global expire_logs_days = 0;# 设置保留 7 天（7*24*60*60 = 604800 秒）
   set global binlog_expire_logs_seconds = 604800;
   

3. 触发自动清除（可选，默认由 MySQL 定期执行）：

   flush logs;  # 切换新 Binlog 文件，触发旧日志检查与删除
   

方式 2：删除指定文件之前的日志

通过文件名精准删除，适用于需保留特定文件的场景：

1. 查看当前 Binlog 文件列表：

   show binary logs;
   

2. 删除指定文件之前的所有日志（示例：保留 mysql-bin.000002 及之后的文件）：

   purge binary logs to 'mysql-bin.000002';
   

3. 验证删除结果：

   show binary logs;
   

方式 3：删除指定时间之前的日志

通过时间删除，适用于需清理某个时间点前日志的场景：

# 删除 2030-02-02 00:00:00 之前的所有 Binlog
purge binary logs before '2030-02-02 00:00:00';# 验证删除结果（查看文件列表或磁盘占用）
ll /data/mysql/binlog/

4.2 清除注意事项

1. 优先使用自动清除：手动清除易误删有用日志，自动清除更安全可控。
2. 确认从库已同步：主从架构中，需确保所有从库已读取并回放待清除的 Binlog，避免从库同步中断（可通过 show slave status\G 查看从库已读取的 Binlog 位点）。
3. 监控磁盘空间：建议配置磁盘监控，当使用率超过 80% 时触发告警，及时排查 Binlog 占用问题（避免因 Binlog 暴涨导致服务不可用）。

5 二进制日志（Binlog）的落盘机制

Binlog 并非直接写入磁盘，而是先写入缓冲区，再通过配置的策略同步至磁盘，平衡性能与数据安全性。

5.1 落盘流程

1. 创建 Binlog 缓冲区：MySQL 启动时创建 Binlog 缓冲区（内存区域），用于临时存储数据变更记录。
2. 写入缓冲区：执行 DML/DQL 操作时，MySQL 将变更记录写入 Binlog 缓冲区。
3. 同步至磁盘：根据 sync_binlog 参数配置的策略，将缓冲区数据刷入磁盘文件。

5.2 落盘频率控制参数（sync_binlog）

sync_binlog 决定 Binlog 缓冲区同步至磁盘的频率，直接影响数据安全性与性能：

# 查看当前配置
show global variables like "sync_binlog";

参数取值及含义：

• sync_binlog = 0：依赖操作系统刷盘（操作系统会定期将内存数据写入磁盘）。
  ✅ 优点：性能最优（减少磁盘 IO）；
  ❌ 缺点：系统崩溃时可能丢失缓冲区中未刷盘的事务。
• sync_binlog = 1：每次事务提交后立即刷盘。
  ✅ 优点：数据最安全（无丢失风险）；
  ❌ 缺点：性能最差（每次事务都触发磁盘 IO）。
• sync_binlog = N（N>1）：每提交 N 次事务后刷盘。
  ✅ 优点：平衡性能与安全性；
  ❌ 缺点：系统崩溃时可能丢失最近 N 个事务。

5.3 实验：验证 sync_binlog 对性能的影响

实验目的

对比不同 sync_binlog 值下，批量插入数据的耗时，验证落盘频率对性能的影响。

实验步骤

1. 创建测试表与批量插入存储过程：

   # 创建测试表
   CREATE TABLE test_table (id INT PRIMARY KEY,data VARCHAR(255)
   );# 创建存储过程：插入 10 万行数据
   DELIMITER //
   CREATE PROCEDURE insert_data()
   BEGINDECLARE i INT DEFAULT 1;WHILE (i <= 100000) DOINSERT INTO test_table (id, data) VALUES (i, CONCAT('Test data ', i));SET i = i + 1;END WHILE;
   END//
   DELIMITER ;
   

2. 分别设置 sync_binlog 为 0、1、100，执行存储过程并记录耗时：

   • 场景 1：sync_binlog = 0

     set global sync_binlog = 0;
     CALL insert_data();  # 记录耗时（示例：约 10 秒）
     truncate table test_table;  # 清空表，准备下一场景
     

   • 场景 2：sync_binlog = 1

     set global sync_binlog = 1;
     CALL insert_data();  # 记录耗时（示例：约 30 秒）
     truncate table test_table;
     

   • 场景 3：sync_binlog = 100

     set global sync_binlog = 100;
     CALL insert_data();  # 记录耗时（示例：约 15 秒）
     truncate table test_table;
     

实验结论

• sync_binlog = 0 性能最优，但安全性最低；
• sync_binlog = 1 安全性最高，但性能最差；
• sync_binlog = N 需根据业务对「性能」与「安全性」的权衡选择（如金融场景建议设为 1，非核心场景可设为 100~1000）。

6 查询日志（General Log）：配置与作用

General Log 记录 MySQL 所有操作（包括查询、连接、断开等），主要用于诊断问题与审计，但性能开销较大，生产环境需谨慎启用。

6.1 General Log 配置

方式 1：动态开启（临时生效）

# 查看当前状态
show global variables like "general%";# 开启 General Log
set global general_log = on;# （可选）修改日志存储路径（默认路径可通过 general_log_file 查看）
set global general_log_file = '/data/mysql/log/mysql-general.log';

方式 2：永久开启（需重启）

# 1. 编辑配置文件
vim /data/mysql/conf/my.cnf# 2. 添加配置
general_log = on  # 开启日志
general_log_file = /data/mysql/log/mysql-general.log  # 日志路径# 3. 重启 MySQL 服务
/etc/init.d/mysql.server restart

配置日志输出方式

General Log 支持输出到「文件」或「表」，可通过 log_output 配置：

# 查看当前输出方式
show global variables like "log_output";# 设置输出方式（FILE：文件；TABLE：表；FILE,TABLE：同时输出）
set global log_output = 'FILE,TABLE';

• 输出到表时，日志存储在 mysql.general_log 表中，可通过 SQL 查询：

  select * from mysql.general_log limit 10;
  

6.2 查看 General Log

# 查看文件日志（示例：查看最后 10 行）
tail -n 10 /data/mysql/log/mysql-general.log# 实时查看日志（跟踪最新操作）
tail -f /data/mysql/log/mysql-general.log

6.3 General Log 的作用与缺点

核心作用

1. 问题诊断：记录所有操作，可定位慢查询、连接失败、SQL 语法错误等问题（如排查「某条 SQL 为何未执行」）。
2. 性能调优：分析操作执行频率与耗时，识别高频低效率操作（如频繁执行无索引的查询）。
3. 安全审计：追踪用户操作记录，排查恶意操作（如谁删除了关键表、谁执行了高危 SQL）。

主要缺点

1. 性能开销大：高并发场景下，General Log 会产生大量 IO 操作，导致 MySQL 性能下降（甚至翻倍增加 CPU/IO 使用率）。
2. 日志体积大：日志会快速膨胀（如每秒 thousands 条记录），短时间内占满磁盘空间。
3. 敏感信息泄露风险：日志中包含用户名、密码、业务数据等敏感信息，若未做好权限控制（如仅允许 root 访问），可能导致数据泄露。

注意：生产环境建议关闭 General Log，仅在排查特定问题时临时开启，问题解决后立即关闭。

7 慢查询日志（Slow Log）：定位低效 SQL

Slow Log 记录执行时间超过阈值的 SQL 语句，是优化查询性能的核心工具，生产环境建议长期开启。

7.1 开启 Slow Log

方式 1：动态开启（临时生效）

# 查看当前配置
show global variables like "slow_query_log%";
show global variables like "long_query_time";# 开启慢查询日志
set global slow_query_log = 1;# 设置日志存储路径
set global slow_query_log_file = '/data/mysql/log/mysql-slow.log';# 设置慢查询阈值（单位：秒，示例：超过 1 秒的 SQL 记录）
set global long_query_time = 1;

• 注意：long_query_time 生效后，仅新连接的 SQL 会采用新阈值，现有连接需重新连接才生效。

方式 2：永久开启（需重启）

# 1. 编辑配置文件
vim /data/mysql/conf/my.cnf# 2. 添加配置
slow_query_log = 1  # 开启慢查询日志
slow_query_log_file = /data/mysql/log/mysql-slow.log  # 日志路径
long_query_time = 1  # 慢查询阈值（秒）# 3. 重启 MySQL 服务
/etc/init.d/mysql.server restart

7.2 Slow Log 进阶配置

除基础阈值外，还可配置以下参数增强慢查询记录能力：

1. 记录管理语句（如 ALTER、DROP）：

   # 动态开启（永久开启需添加到配置文件）
   set global log_slow_admin_statements = on;
   

   作用：管理语句执行时间可能较长（如 ALTER TABLE 加索引），记录后便于排查运维操作导致的性能问题。

2. 记录未使用索引的查询：

   set global log_queries_not_using_indexes = on;
   

   作用：即使查询执行时间未超过阈值，若未使用索引（可能导致全表扫描），也会记录到 Slow Log，提前发现低效 SQL。

3. 设置检查行数阈值：

   # 查看当前配置
   show global variables like "min_examined_row_limit";# 设置为 0（记录所有符合条件的慢查询，不限制检查行数）
   set global min_examined_row_limit = 0;
   

   作用：避免因「检查行数少」而忽略慢查询（如全表扫描 100 行但耗时 2 秒的 SQL）。

7.3 查看与解析 Slow Log

步骤 1：生成测试慢查询

# 执行 sleep(2)，模拟耗时 2 秒的慢查询
select sleep(2);

步骤 2：查看慢查询日志

# 查看日志内容
tail /data/mysql/log/mysql-slow.log

日志示例（关键信息说明）：

# Time: 2024-05-20T10:00:00.000000Z  # 执行时间
# User@Host: root[root] @ localhost []  # 执行用户与主机
# Query_time: 2.000000  Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1  # 耗时、锁时间、返回行数、检查行数
SET timestamp=1716223200;  # 时间戳
select sleep(2);  # 具体 SQL

步骤 3：使用官方工具分析 Slow Log

MySQL 自带 mysqldumpslow 工具，可统计慢查询的频率、耗时等，快速定位TOP低效SQL：

1. 基础分析（按默认规则排序）：

   # 进入日志目录
   cd /data/mysql/log# 分析慢查询日志
   mysqldumpslow mysql-slow.log
   

2. 按耗时排序（显示TOP5）：

   # -s t：按耗时（time）排序；-t 5：显示前 5 条
   mysqldumpslow -s t -t 5 mysql-slow.log
   

3. 按执行次数排序（显示TOP5）：

   # -s c：按执行次数（count）排序
   mysqldumpslow -s c -t 5 mysql-slow.log
   

4. 过滤特定数据库的慢查询：

   # -d martin：仅分析 martin 库的慢查询
   mysqldumpslow -d martin mysql-slow.log
   

7.4 慢查询额外信息输出（MySQL 8.0.14+）

MySQL 8.0.14 新增 log_slow_extra 参数，开启后可记录更多慢查询细节（如全表扫描标记、临时表使用情况）：

# 开启额外信息输出
set global log_slow_extra = on;# 生成慢查询
select sleep(2);# 查看日志（新增信息如 "Rows_affected: 0"、"Full_scan: Yes"）
tail /data/mysql/log/mysql-slow.log

8 错误日志（Error Log）：排查服务异常

Error Log 记录 MySQL 启动、关闭、运行过程中的错误、警告与调试信息，是解决服务启动失败、崩溃、连接异常等问题的核心依据，默认开启。

8.1 Error Log 配置与查看

查看日志路径

# 查看错误日志存储路径
show global variables like "log_error";

默认路径通常为 data/mysql/hostname.err（如 /data/mysql/localhost.err），也可手动配置路径：

# 永久配置路径（需编辑配置文件并重启）
vim /data/mysql/conf/my.cnf
# 添加：log-error = /data/mysql/log/mysql.err
/etc/init.d/mysql.server restart

配置日志记录级别

通过 log_error_verbosity 控制日志记录的详细程度：

# 查看当前级别
show global variables like "log_error_verbosity";

参数取值及含义：

• 1：仅记录错误信息（Error）；
• 2：记录错误与警告信息（Error + Warning）；
• 3：记录所有信息（Error + Warning + Note + 调试信息）。

查看 Error Log

# 查看最后 50 行日志（定位最新错误）
tail -n 50 /data/mysql/log/mysql.err# 实时跟踪日志（监控服务运行状态）
tail -f /data/mysql/log/mysql.err

8.2 Error Log 的核心作用

作用 1：定位服务启动异常

若 MySQL 启动失败（如执行 service mysql start 后提示失败），可通过 Error Log 查看具体原因：

1. 模拟启动异常（高危操作：仅限测试环境！）：

   # 故意修改 redo log 文件属主，导致启动失败
   chown root.root /data/mysql/data/ib_logfile0# 尝试启动 MySQL
   /etc/init.d/mysql.server restart  # 启动失败
   

2. 查看 Error Log 定位原因：

   tail -n 50 /data/mysql/log/mysql.err
   

   日志会显示类似错误：[ERROR] [MY-012884] [InnoDB] /data/mysql/data/ib_logfile0 can't be opened in read-write mode.（权限不足）。
   

3. 修复问题并重启：

   # 恢复文件属主
   chown mysql.mysql /data/mysql/data/ib_logfile0# 重新启动
   /etc/init.d/mysql.server start  # 启动成功
   

作用 2：诊断连接异常

当用户无法连接 MySQL 时，Error Log 会记录连接失败原因（如密码错误、账户锁定）：

1. 创建测试用户并设置锁定规则：

   # 创建用户：试错 4 次后锁定 3 天
   create user 'test_pass'@'localhost' identified by '123456' 
   failed_login_attempts 4 password_lock_time 3;# 授予查询权限
   grant select on *.* to 'test_pass'@'localhost';# 开启详细日志（记录连接失败信息）
   set global log_error_verbosity = 3;
   

2. 故意用错误密码连接：

   mysql -utest_pass -p'wrong_pass'  # 连接失败
   

3. 查看 Error Log 中的失败信息：

   tail -f /data/mysql/log/mysql.err
   

   日志会显示：[Note] [MY-010926] [Server] Access denied for user 'test_pass'@'localhost' (using password: YES)。

作用 3：记录死锁信息

InnoDB 死锁时，Error Log 会详细记录死锁事务的 SQL、锁类型、等待关系，便于排查死锁原因：

1. 模拟死锁（需两个会话）：

   • 创建测试表并写入测试数据

     use martin;
     create table errlog_t1(id int primary key, a varchar(10)) engine=InnoDB;
     insert into errlog_t1 values (1,'a'),(2,'b');
     

   • 进行死锁实验

     session1	session2
     begin;	begin;
     delete from errlog_t1 where id=1;	delete from errlog_t1 where id=2;
     delete from errlog_t1 where id=2;	delete from errlog_t1 where id=1;
     commit;	commit;

2. 查看 Error Log 中的死锁信息：

   tail -n 100 /data/mysql/log/mysql.err
   

   日志会显示死锁事务的 TRANSACTION 信息、锁等待关系（如 WAITING FOR THIS LOCK TO BE GRANTED）。
   

8.3 MySQL 8.0+ 错误日志过滤（进阶）

MySQL 8.0 支持通过过滤器组件控制 Error Log 输出，避免日志中充斥无用信息（如频繁的连接失败日志）。

步骤 1：开启日志过滤

# 1. 加载过滤器组件（dragnet：MySQL 官方过滤组件）
INSTALL COMPONENT 'file://component_log_filter_dragnet';# 2. 配置日志服务：先过滤再输出
SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';

步骤 2：配置过滤规则（示例）

需求：限制「信息类日志（Note）」每分钟最多记录 1 条，避免刷屏：

# 设置过滤规则：信息类日志每分钟不超过 1 条
SET GLOBAL dragnet.log_error_filter_rules = 'IF prio>=INFORMATION THEN throttle 1/60.';

• prio>=INFORMATION：匹配信息类及以上级别的日志（Note、Warning、Error）；
• throttle 1/60：每分钟最多记录 1 条。

步骤 3：验证过滤效果

1. 短时间内多次触发信息类日志（如多次用错误密码连接）：

   mysql -utest_pass -p'wrong_pass'  # 第 1 次：记录日志
   mysql -utest_pass -p'wrong_pass'  # 第 2 次（1 分钟内）：不记录
   

2. 查看 Error Log：仅记录第 1 次连接失败信息，第 2 次不记录。

步骤 4：关闭日志过滤

# 1. 恢复日志服务：直接输出，不经过滤
SET GLOBAL log_error_services = 'log_sink_internal';# 2. 卸载过滤器组件
UNINSTALL COMPONENT 'file://component_log_filter_dragnet';# 3. 恢复日志级别（避免过多调试信息）
set global log_error_verbosity = 2;

8.4 从表中查询错误日志（MySQL 8.0.22+）

MySQL 8.0.22 开始支持将 Error Log 存储到 performance_schema.error_log 表中，便于通过 SQL 查询（无需操作文件）。

查看表中日志

# 查看最新 10 条错误日志
select * from performance_schema.error_log order by LOGGED desc limit 10;# 查看级别为 ERROR 的日志
select * from performance_schema.error_log where PRIO = 'ERROR'\G;# 查看指定时间范围的日志
select * from performance_schema.error_log 
where LOGGED between '2024-05-20 10:00:00' and '2024-05-20 11:00:00';

注意事项

• performance_schema.error_log 是系统表，仅支持查询，不支持 INSERT/UPDATE/DELETE；
• 表中日志与文件日志内容一致，可根据需求选择查询方式（文件适合实时跟踪，表适合复杂条件过滤）；
• 无法通过 TRUNCATE 清空表，需通过配置 log_error_services 重置日志。

9 重做日志（Redo Log）：保障事务持久性

Redo Log 是 InnoDB 存储引擎特有的物理日志，记录数据页的修改（如「将数据页 X 的偏移量 Y 的值从 A 改为 B」），核心作用是保障事务的持久性（ACID 中的 D）——即使 MySQL 崩溃，重启后可通过 Redo Log 恢复未刷盘的事务。

9.1 Redo Log 的核心概念

为什么需要 Redo Log？

InnoDB 采用「缓冲池（Buffer Pool）」机制：数据读写先操作缓冲池中的数据页，再定期将脏页（修改过但未刷盘的数据页）刷入磁盘。若 MySQL 崩溃时脏页未刷盘，会导致数据丢失。Redo Log 记录了数据页的修改，崩溃后可通过 Redo Log 重放修改，恢复脏页数据，避免丢失。

Redo Log 记录的内容

Redo Log 是物理日志，记录「数据页的变更」，而非 SQL 语句或行数据。例如：

• 执行 update user set name='test' where id=1 后，Redo Log 会记录：
  数据页号：100，偏移量：200，修改前值：'old'，修改后值：'test'

9.2 两阶段提交与双写（Redo Log 关键机制）

两阶段提交（保障 Binlog 与 Redo Log 一致性）

MySQL 崩溃时，需确保「Binlog 与 Redo Log 记录的事务一致」（避免主从数据不一致），因此采用两阶段提交：

1. 准备阶段（Prepare）：
   • 事务执行完成后，将修改记录写入 Redo Log，并标记为「Prepare 状态」；
   • 此时 Redo Log 已刷盘，但 Binlog 未写入。
2. 提交阶段（Commit）：
   • 将事务记录写入 Binlog 并刷盘；
   • 若 Binlog 刷盘成功，将 Redo Log 标记为「Commit 状态」；
   • 若 Binlog 刷盘失败，事务回滚，Redo Log 的 Prepare 状态记录会被忽略。

双写（避免数据页损坏）

Redo Log 重放时需基于完整的数据页，若数据页刷盘过程中崩溃（如断电），会导致数据页损坏（部分写入），Redo Log 无法重放。InnoDB 通过「双写缓冲区（Doublewrite Buffer）」解决该问题：

1. 脏页刷盘前，先将完整的数据页写入「双写缓冲区」（内存区域）；
2. 将双写缓冲区的数据刷入磁盘的「双写文件」（物理文件，位于共享表空间）；
3. 确认双写文件写入成功后，再将脏页刷入实际数据文件；
4. 若刷盘过程中崩溃，重启后可从双写文件读取完整数据页，修复损坏的实际数据页，再重放 Redo Log。

9.3 Redo Log 的配置

配置 Redo Log 容量

Redo Log 容量直接影响性能：容量过小会导致频繁切换日志文件（触发 checkpoint，刷盘频繁）；容量过大则崩溃恢复时间变长，建议配置为 4G~8G。

• MySQL 8.0.30 之前版本：
  通过 innodb_log_file_size（单个文件大小）与 innodb_log_files_in_group（文件数量）控制总容量：

  # 查看当前配置
  show global variables like "innodb_log_file%";# 配置示例（总容量 = 2 * 2G = 4G）
  vim /data/mysql/conf/my.cnf
  [mysqld]
  innodb_log_file_size = 2G  # 单个文件大小
  innodb_log_files_in_group = 2  # 文件数量（默认 2，建议保持）# 重启 MySQL 服务（需先备份旧日志文件，避免启动失败）
  mv /data/mysql/data/ib_logfile* /data/mysql/backup/
  /etc/init.d/mysql.server restart
  

• MySQL 8.0.30 及之后版本：
  新增 innodb_redo_log_capacity 参数，直接控制总容量（简化配置）：

  # 查看当前总容量
  show global variables like "innodb_redo_log_capacity";# 配置总容量为 4G（无需关心文件数量，MySQL 自动管理）
  vim /data/mysql/conf/my.cnf
  [mysqld]
  innodb_redo_log_capacity = 4G# 重启 MySQL 服务
  /etc/init.d/mysql.server restart
  

配置 Redo Log 存储路径

默认情况下，Redo Log 文件（ib_logfile0、ib_logfile1）存储在数据目录（如 /data/mysql/data/），可通过以下参数修改路径：

# 查看当前路径
show global variables like "innodb_log_group_home_dir";# 配置新路径（建议放在独立磁盘，减少 IO 竞争）
vim /data/mysql/conf/my.cnf
[mysqld]
innodb_log_group_home_dir = /data/mysql/redo_log/  # 新路径# 创建目录并授权
mkdir -p /data/mysql/redo_log/
chown -R mysql.mysql /data/mysql/redo_log/# 备份旧日志并重启
mv /data/mysql/data/ib_logfile* /data/mysql/redo_log/
/etc/init.d/mysql.server restart

9.4 Redo Log 在崩溃恢复中的作用

当 MySQL 崩溃后重启，InnoDB 会自动执行崩溃恢复，核心流程依赖 Redo Log：

1. 分析阶段：扫描 Redo Log，识别所有处于「Prepare 状态」和「Commit 状态」的事务。
2. 重做阶段：重放所有「Prepare 状态且 Binlog 已写入」和「Commit 状态」的事务（通过 Redo Log 恢复数据页修改）。
3. 回滚阶段：回滚所有「Prepare 状态但 Binlog 未写入」的事务（通过 Undo Log 撤销修改）。

通过以上流程，确保崩溃后数据既不丢失（已提交事务通过 Redo Log 恢复），也不出现脏数据（未提交事务通过 Undo Log 回滚）。

9.5 Redo Log 最佳实践

1. 配置合适的容量：总容量建议为 4G~8G，单个文件不超过 4G（避免恢复时间过长）。
2. 独立磁盘存储：将 Redo Log 放在独立的 SSD 磁盘（IO 性能高），避免与数据文件、Binlog 共享磁盘，减少 IO 竞争。
3. 监控 Redo Log 使用情况：

   # 查看 Redo Log 使用率（Used %）
   select variable_value as redo_log_used_pct 
   from information_schema.global_status 
   where variable_name = 'Innodb_redo_log_used_percent';
   

   若使用率长期超过 80%，需增大 Redo Log 容量。
4. 避免频繁切换日志：若 Innodb_redo_log_switches（日志切换次数）过高（如每分钟多次），说明容量不足，需扩容。

10 重做日志（Redo Log）的落盘机制

Redo Log 同样采用「缓冲区 + 磁盘」的存储方式，落盘频率通过 innodb_flush_log_at_trx_commit 控制，直接影响事务持久性与性能。

10.1 innodb_flush_log_at_trx_commit 配置说明

# 查看当前配置
show global variables like "innodb_flush_log_at_trx_commit";

参数取值及落盘规则：

• 0：事务提交时不刷盘，依赖操作系统每秒自动刷盘（将 Redo Log 缓冲区数据写入磁盘文件）。
• 1：事务提交时，立即将 Redo Log 缓冲区数据写入磁盘文件，并调用 fsync() 确保数据刷入物理磁盘（不依赖操作系统缓存）。
• 2：事务提交时，将 Redo Log 缓冲区数据写入磁盘文件（但仅写入操作系统缓存），操作系统每秒自动刷入物理磁盘。

10.2 不同配置对性能的影响（实验验证）

实验目的

对比 innodb_flush_log_at_trx_commit 取 0、1、2 时，批量插入数据的耗时，验证落盘频率对性能的影响。

实验步骤

1. 创建测试表与批量插入存储过程：

   # 选择数据库
   use martin;# 创建测试表
   create table redo_t1(id int not null auto_increment,a varchar(20) default null,b int default null,c datetime not null default current_timestamp,primary key(id)
   )engine=innodb charset=utf8mb4;# 创建存储过程：插入 10 万行数据
   delimiter ;;
   create procedure insert_t1()
   begindeclare i int;set i=1;while(i<=100000)doinsert into redo_t1(a,b) values (i,i);set i=i+1;end while;
   end;;
   delimiter ;
   

2. 分别设置参数并执行存储过程，记录耗时：

   • 场景 1：innodb_flush_log_at_trx_commit = 0

     set global innodb_flush_log_at_trx_commit = 0;
     call insert_t1();  # 记录耗时（示例：约 8 秒）
     truncate table redo_t1;  # 清空表
     

   • 场景 2：innodb_flush_log_at_trx_commit = 1

     set global innodb_flush_log_at_trx_commit = 1;
     call insert_t1();  # 记录耗时（示例：约 25 秒）
     truncate table redo_t1;
     

   • 场景 3：innodb_flush_log_at_trx_commit = 2

     set global innodb_flush_log_at_trx_commit = 2;
     call insert_t1();  # 记录耗时（示例：约 10 秒）
     truncate table redo_t1;
     

实验结论

参数值	性能（耗时）	数据安全性（崩溃时丢失风险）	适用场景
0	最优	高（丢失 0~1 秒内的事务）	非核心业务（如日志存储）
1	最差	无丢失（完全符合 ACID）	核心业务（如金融、支付）
2	中等	低（仅丢失操作系统缓存数据）	非核心但需低丢失场景

注意：若 MySQL 部署在虚拟机或云服务器，innodb_flush_log_at_trx_commit = 2 可能因虚拟机崩溃导致操作系统缓存数据丢失，此时建议设为 1。

10.3 innodb_flush_log_at_trx_commit 配置不同值的对比

设置的值	特点	优点	缺点
0	每秒将日志缓冲区写入日志文件一次，并在日志文件上执行磁盘刷新操作	写入性能最好	MySQL崩溃，可能会丢失最后一秒的事务
1	在每次提交事务时，日志缓冲区都会写入日志文件中，并在日志文件上执行磁盘刷新操作	最安全的	性能最差
2	在每次提交事务后写入日志，并且日志每秒刷新一次到磁盘	比设置为0更安全，比设置为1性能更好	操作系统崩溃或者断电，可能会丢失最后一秒的事务

11 MySQL 8.0 重做日志（Redo Log）的归档与禁用

MySQL 8.0 新增 Redo Log 归档与禁用功能，分别适用于备份场景与数据导入场景。

11.1 Redo Log 归档（MySQL 8.0.17+）

归档的作用

Redo Log 采用「循环写」机制（文件写满后覆盖旧内容），备份数据时若需同步增量 Redo Log，可能因覆盖导致日志丢失。归档功能可将 Redo Log 按顺序写入归档文件（追加写，不覆盖），确保备份时能获取完整的增量日志。

开启 Redo Log 归档

1. 创建归档目录并授权：

   # 创建归档目录（示例：按日期命名）
   mkdir -p /data/mysql/redolog-archiving/redo-20240520# 授权（确保 MySQL 有权读写）
   chown -R mysql.mysql /data/mysql/redolog-archiving/
   chmod 700 /data/mysql/redolog-archiving/redo-20240520
   

2. 配置归档路径并激活：

   # 设置归档目录映射（格式：别名:实际路径）
   set global innodb_redo_log_archive_dirs = "arch_dir:/data/mysql/redolog-archiving/";# 激活归档（参数：目录别名、归档子目录）
   do innodb_redo_log_archive_start("arch_dir", "redo-20240520");# 验证归档是否开启（查看归档文件）
   ll -h /data/mysql/redolog-archiving/redo-20240520/
   

   归档目录下会生成 archive_xxx 文件，持续写入 Redo Log 内容。
   

停止 Redo Log 归档

# 停止归档
do innodb_redo_log_archive_stop();# （可选）删除归档目录（备份完成后）
rm -rf /data/mysql/redolog-archiving/redo-20240520/

11.2 Redo Log 禁用（MySQL 8.0.21+）

禁用的作用

Redo Log 会带来一定的 IO 开销，当执行大量数据导入（如初始化新实例、批量导入历史数据）时，可临时禁用 Redo Log，提升导入速度（通常可提升 2~5 倍）。

禁用 Redo Log

# 1. 禁用 Redo Log（仅支持全局禁用，需 root 权限）
alter instance disable innodb redo_log;# 2. 验证禁用状态（Value 为 OFF 表示已禁用）
show global status like "innodb_redo_log_enabled";

性能对比实验

1. 禁用 Redo Log 后导入数据：

   # 清空测试表
   truncate table martin.redo_t1;# 执行批量插入（禁用 Redo Log）
   call martin.insert_t1();  # 记录耗时（示例：约 3 秒）
   

2. 启用 Redo Log 后导入数据：

   # 启用 Redo Log
   alter instance enable innodb redo_log;# 清空测试表
   truncate table martin.redo_t1;# 执行批量插入（启用 Redo Log）
   call martin.insert_t1();  # 记录耗时（示例：约 25 秒）
   

实验结论

禁用 Redo Log 后，数据导入速度大幅提升（示例中从 25 秒缩短至 3 秒），但需注意：

• 禁用期间不保证持久性：若 MySQL 崩溃，未刷盘的数据会丢失，因此仅建议在「数据可重新导入」的场景（如初始化实例、导入历史备份）中禁用。
• 禁用后需及时启用：数据导入完成后，立即启用 Redo Log，确保后续业务符合 ACID 特性。

12 回滚日志（Undo Log）：事务回滚与 MVCC 基础

Undo Log 是 InnoDB 特有的逻辑日志，记录事务修改前的数据状态，核心作用是支持事务回滚与多版本并发控制（MVCC）。

12.1 Undo Log 的三大核心作用

1. 事务回滚：当事务执行 ROLLBACK 或崩溃时，InnoDB 通过 Undo Log 恢复数据到事务开始前的状态（如执行 update 后，Undo Log 记录修改前的旧值，回滚时用旧值覆盖新值）。
2. 支持 MVCC：多事务并发读写时，读事务通过 Undo Log 读取数据的历史版本（而非当前修改后的版本），实现「非锁定读」（如 SELECT 无需加锁，不阻塞写事务）。
3. 崩溃恢复：MySQL 崩溃后重启，未提交的事务需回滚，InnoDB 通过 Undo Log 撤销这些事务的修改，确保数据一致性。

12.2 不同操作的 Undo Log 内容

Undo Log 按操作类型分为三类，记录内容不同：

• INSERT 操作：记录「删除该插入行」的逻辑（因插入的行仅当前事务可见，回滚时直接删除即可）。
• DELETE 操作：记录「重新插入该删除行」的逻辑（回滚时需恢复被删除的行，因此记录行的完整旧值）。
• UPDATE 操作：记录「将修改后的列恢复为旧值」的逻辑（如 update user set name='test' where id=1，Undo Log 记录 name 的旧值，回滚时将 name 改回旧值）。

注意：SELECT 操作不产生 Undo Log（无需回滚，也不修改数据）。

12.3 Undo Log 在 MVCC 中的作用

MVCC 是 InnoDB 实现并发控制的核心机制，其核心是「读事务看到数据的历史版本」，而历史版本正是通过 Undo Log 构建的：

1. 事务执行 UPDATE 时，InnoDB 会：
   • 为该行生成一个新的版本（记录新值）；
   • 生成 Undo Log，记录该行的旧版本（供其他读事务访问）；
   • 将新版本的 roll_pointer 指向 Undo Log 中的旧版本（形成版本链）。
2. 读事务执行 SELECT 时，InnoDB 会：
   • 根据事务隔离级别（如 REPEATABLE READ），通过版本链找到符合条件的历史版本；
   • 若当前版本被其他事务修改，通过 roll_pointer 追溯 Undo Log 中的旧版本，直到找到可见版本。

例如：

• 事务 A 执行 update user set name='new' where id=1（修改前 name 为 ‘old’）；
• 事务 B 执行 select name from user where id=1（隔离级别为 REPEATABLE READ）；
• 事务 B 会通过 Undo Log 读取到 name 的旧值 ‘old’，而非事务 A 修改后的 ‘new’，实现非锁定读。

12.4 Undo Log 在崩溃恢复中的作用

MySQL 崩溃后重启，InnoDB 会执行崩溃恢复，Undo Log 用于回滚未提交的事务：

1. 分析 Redo Log，识别所有未提交的事务（处于 Prepare 状态但未 Commit 的事务）；
2. 对每个未提交的事务，通过 Undo Log 执行反向操作（如 UPDATE 回滚为旧值，DELETE 回滚为插入，INSERT 回滚为删除）；
3. 回滚完成后，删除这些事务的 Undo Log，释放空间。

12.5 Undo Log 最佳实践

1. 独立存储 Undo Log：将 Undo Log 放在 SSD 磁盘（IO 性能高），避免与数据文件、Redo Log 共享磁盘，减少 IO 竞争（配置参数：innodb_undo_directory）。
2. 控制 Undo Log 大小：通过 innodb_max_undo_log_size 限制单个 Undo 表空间大小（默认 1G），避免 Undo Log 过度膨胀（配置参数：innodb_undo_log_truncate 开启自动截断）。
3. 避免长事务：长事务会持续占用 Undo Log（需保留历史版本供 MVCC 访问），导致 Undo Log 体积增大、回滚时间变长，甚至引发磁盘空间不足。建议将事务拆分为短事务，避免执行耗时超过 10 秒的事务。

13 回滚日志（Undo Log）的清理（Purge）

Undo Log 不会在事务提交后立即删除（可能有其他读事务通过 MVCC 访问其历史版本），InnoDB 会定期清理无用的 Undo Log，该过程称为 Purge。

13.1 Purge 的核心逻辑

什么是 Purge？

Purge 是 InnoDB 后台线程（Purge Thread）执行的清理操作，用于删除「不再被任何事务访问的 Undo Log」，释放磁盘空间。

什么时候触发 Purge？

Undo Log 需满足以下条件才会被 Purge：

1. 生成该 Undo Log 的事务已提交；
2. 所有读事务都已不需要访问该 Undo Log 对应的历史版本（即所有读事务的「最早可见版本」都晚于该 Undo Log 对应的版本）。

Purge 的触发时机

• 定时触发：InnoDB 定期（默认每 1 秒）检查是否有可 Purge 的 Undo Log；
• 事务提交触发：当事务提交时，若 Undo Log 数量达到阈值，触发 Purge；
• Checkpoint 触发：Redo Log 执行 Checkpoint 时，若 Undo Log 占用空间过大，触发 Purge。

13.2 如何判断 Undo Log 可被 Purge？

InnoDB 通过「Read View」判断 Undo Log 是否可被 Purge：

1. Read View：每个读事务启动时生成的「可见事务 ID 范围」，记录当前活跃事务的最小 ID（min_trx_id）与最大 ID（max_trx_id）。
2. 判断逻辑：
   • 若 Undo Log 对应的事务 ID（trx_id）小于 min_trx_id：表示该事务在所有活跃事务之前提交，其历史版本不再被任何读事务访问，可 Purge；
   • 若 Undo Log 对应的事务 ID 大于等于 max_trx_id：表示该事务在当前读事务之后启动，其历史版本可能被其他读事务访问，不可 Purge；
   • 若事务 ID 在 min_trx_id 与 max_trx_id 之间：需进一步检查该事务是否仍活跃，若已提交且无其他读事务访问，可 Purge。

13.3 Purge 优化策略

1. 调整 Purge 线程数

Purge 线程数默认为 4（MySQL 8.0+），若 Undo Log 堆积严重（如长事务多、并发高），可增加线程数提升清理速度：

# 查看当前 Purge 线程数
show global variables like "innodb_purge_threads";# 调整为 8 个线程（永久调整需添加到配置文件）
set global innodb_purge_threads = 8;

2. 配置回滚段清理频率

innodb_purge_rseg_truncate_frequency 控制每执行多少次 Purge 操作后，检查并删除无用的回滚段（回滚段是 Undo Log 的存储单元）：

# 查看当前配置
show global variables like "innodb_purge_rseg_truncate_frequency";# 调整为每 10 次 Purge 检查一次回滚段（默认 128）
set global innodb_purge_rseg_truncate_frequency = 10;

• 数值越小：回滚段清理越频繁，Undo 表空间截断越及时，但可能增加 CPU 开销；
• 数值越大：回滚段清理频率低，可能导致 Undo 表空间膨胀，但 CPU 开销小。

3. 监控长事务

长事务会导致 Read View 的 min_trx_id 长期不变，使大量 Undo Log 无法被 Purge（需等待长事务结束），因此需监控并优化长事务：

# 查看执行时间过长的事务
show processlist;# 查看执行时间超过 60 秒的事务
select trx_id, trx_started, timestampdiff(second, trx_started, now()) as trx_duration_seconds,trx_query 
from information_schema.innodb_trx 
where timestampdiff(second, trx_started, now()) > 60;

对执行时间过长的事务，分析其 SQL 逻辑，拆分为短事务或优化执行效率（如添加索引、减少数据扫描量）。

4. 开启 Undo Log 自动截断

当 Undo 表空间大小超过 innodb_max_undo_log_size 时，自动截断表空间，释放空间：

# 查看自动截断配置
show global variables like "innodb_undo_log_truncate";
show global variables like "innodb_max_undo_log_size";# 开启自动截断（永久开启需添加到配置文件）
set global innodb_undo_log_truncate = on;# 设置 Undo 表空间最大大小为 2G（默认 1G）
set global innodb_max_undo_log_size = 2147483648;  # 2G = 2*1024*1024*1024

14 回滚日志（Undo Log）的相关配置

InnoDB 提供多个参数用于配置 Undo Log 的存储、大小、清理等，需根据业务场景调整。

14.1 核心配置参数说明

参数名称	作用	默认值	建议配置
innodb_undo_directory	Undo 表空间的存储目录	数据目录（如 /data/mysql/data/）	独立 SSD 目录（如 /data/mysql/undo/）
innodb_rollback_segments	回滚段数量（每个回滚段可支持多个事务）	128	保持默认（足够支持高并发）
innodb_undo_log_encrypt	是否加密 Undo Log（防止数据泄露）	OFF	核心业务建议开启（ON）
innodb_max_undo_log_size	单个 Undo 表空间的最大大小（超过后触发截断）	1G（1073741824 字节）	2G~4G（根据磁盘空间调整）
innodb_undo_log_truncate	是否自动截断过大的 Undo 表空间	ON（MySQL 8.0+）	保持开启
innodb_purge_threads	Purge 线程数（影响清理速度）	4	高并发场景设为 8~16
innodb_purge_rseg_truncate_frequency	每执行多少次 Purge 检查并删除无用回滚段	128	10~50（平衡清理频率与 CPU 开销）

14.2 配置示例（优化 Undo Log 性能）

1. 配置 Undo Log 独立存储目录：

   # 1. 编辑配置文件
   vim /data/mysql/conf/my.cnf# 2. 添加配置
   [mysqld]
   innodb_undo_directory = /data/mysql/undo/  # 独立存储目录
   innodb_rollback_segments = 128  # 回滚段数量
   innodb_undo_log_encrypt = on  # 开启加密
   innodb_max_undo_log_size = 2147483648  # 单个表空间最大 2G
   innodb_undo_log_truncate = on  # 自动截断
   innodb_purge_threads = 8  # Purge 线程数 8
   innodb_purge_rseg_truncate_frequency = 20  # 每 20 次 Purge 检查回滚段# 3. 创建目录并授权
   mkdir -p /data/mysql/undo/
   chown -R mysql.mysql /data/mysql/undo/
   chmod 700 /data/mysql/undo/# 4. 重启 MySQL 服务
   /etc/init.d/mysql.server restart
   

2. 查看 Undo Log 表空间信息：

   # 查看 Undo 表空间列表与路径
   select tablespace_name, file_name, engine from information_schema.files where file_type = 'UNDO LOG';# 查看 Undo 表空间大小（单位：MB）
   select tablespace_name,round((EXTENT_SIZE * TOTAL_EXTENTS) / 1024 / 1024, 2) as total_size_mb
   from information_schema.files 
   where file_type = 'UNDO LOG';
   

3. 手动创建 Undo 表空间（MySQL 8.0.14+）：
   若默认 Undo 表空间不足，可手动创建额外表空间：

   # 创建 Undo 表空间（指定数据文件）
   create undo tablespace tmp_undo_001 add datafile 'tmp_undo_001.ibu';# 查看新增的表空间
   select tablespace_name from information_schema.files where file_type = 'UNDO LOG';
   

4. 删除 Undo 表空间（需先设为 inactive）：

   # 1. 将表空间设为 inactive（停止写入新的 Undo Log）
   alter undo tablespace tmp_undo_001 set inactive;# 2. 等待表空间中的 Undo Log 被 Purge（可通过监控表空间大小确认）# 3. 删除表空间
   drop undo tablespace tmp_undo_001;
   

14.3 Undo Log的相关配置（续）

• innodb_rollback_segments：配置回滚段的数量。在 MySQL 8.0.2 之前，配置回滚段的数量的参数是 innodb_undo_logs，MySQL 8.0.2 开始就改成了innodb_rollback_segments；回滚段包含撤销日志的存储区域，从 MySQL 5.6 开始，回滚段可以驻留在 undo 表空间中，从 MySQL 5.7 开始，回滚段可以分配给全局临时表空间；InnoDB 使用多个回滚段来组成 undo log，可以保证 MySQL 的并发写入和持久化。可以这样理解，一个 undo 表空间里有多个回滚段，当并发执行事务时，不同时候可能执行不同回滚段，该参数可以配置的范围是 1-128

• innodb_undo_log_encrypt：控制是否对Undo Log进行加密，默认值为OFF（不加密）。开启后可增强数据安全性，防止Undo Log文件被未授权访问时泄露敏感数据。
  配置方式：

  # 动态开启（需重启MySQL生效，且需确保MySQL支持加密功能）
  set global innodb_undo_log_encrypt = ON;
  # 永久配置（在my.cnf的[mysqld]中添加）
  innodb_undo_log_encrypt = ON
  

  注意：开启加密后，Undo表空间文件会以加密格式存储，需确保密钥管理安全（如使用MySQL密钥环插件）。

• innodb_max_undo_log_size：定义单个独立Undo表空间的最大阈值，默认值为1G。当Undo表空间大小超过该值，且innodb_undo_log_truncate开启时，MySQL会自动截断Undo表空间，释放磁盘空间。
  配置示例：

  # 动态调整（需重启MySQL生效）
  set global innodb_max_undo_log_size = 2G;
  # 永久配置
  innodb_max_undo_log_size = 2G
  

• innodb_undo_log_truncate：控制是否自动截断过大的Undo表空间，默认值为ON（开启）。仅对独立Undo表空间生效（系统表空间中的Undo Log不支持截断），截断操作依赖于回滚段的释放（由innodb_purge_rseg_truncate_frequency控制）。
  注意：若关闭该参数，Undo表空间会持续增长，需手动清理，可能导致磁盘空间耗尽。

• innodb_purge_threads：定义后台Purge线程的数量，默认值在MySQL 8.0中为4（早期版本可能为1）。Purge线程负责清理不再需要的Undo Log（如已提交事务的旧版本数据），多线程可提升高并发场景下的Purge效率，避免Undo Log堆积。
  配置示例：

  # 动态调整（需重启MySQL生效）
  set global innodb_purge_threads = 8;
  # 永久配置（根据CPU核心数调整，建议不超过CPU核心数的1/2）
  innodb_purge_threads = 8
  

• innodb_purge_rseg_truncate_frequency：控制Purge操作执行多少次后，检查并释放“不再被使用的回滚段”，默认值为128。回滚段释放后，对应的Undo表空间才能被innodb_undo_log_truncate截断。
  调整原则：值越低，回滚段释放频率越高，Undo表空间截断越及时，但频繁检查会增加CPU开销；值越高，CPU开销越低，但可能导致Undo表空间临时膨胀。

15 Binlog、Redo Log、Undo Log 的区别

Binlog、Redo Log、Undo Log 是 MySQL 中最核心的三类日志，作用与机制差异显著，需明确区分。

15.1 Binlog 和 Redo Log 的区别

对比维度	Binlog（二进制日志）	Redo Log（重做日志）
记录对象	所有存储引擎（如InnoDB、MyISAM）的修改操作	仅InnoDB存储引擎的修改操作
记录内容	逻辑日志，记录“做了什么修改”（如UPDATE table SET ...）	物理日志，记录“数据页做了什么变更”（如“页123的偏移量456值从A改为B”）
记录时机	仅在事务提交时写入（一次性写入事务的所有操作）	事务执行过程中持续写入（每执行一个修改操作就写一次）
写入方式	追加写（文件满后自动切换到新文件，不覆盖旧日志）	循环写（固定大小的日志文件组，写满后覆盖最早的文件）
核心用途	主从复制（从库通过Binlog回放变更）、数据备份与恢复（全量+Binlog增量）	保证事务持久性（崩溃后通过Redo Log恢复未刷盘的已提交事务）
崩溃恢复角色	不参与InnoDB崩溃恢复（依赖Redo/Undo Log）	核心恢复角色（恢复已提交但未刷盘的数据）

15.2 Undo Log 和 Redo Log 的区别

对比维度	Undo Log（回滚日志）	Redo Log（重做日志）
记录内容	反向修改记录（如“插入了一行id=1，回滚时需删除id=1”）	正向变更记录（如“id=1的字段值从A改为B，恢复时需重新执行该变更”）
写入顺序	随机读写（Undo Log存储在表空间中，修改时可能需定位旧记录）	顺序写（固定日志文件组，按操作顺序连续写入，性能高）
核心作用	1. 事务回滚（恢复到修改前状态） 2. 支持MVCC（提供数据历史版本）	1. 保证事务持久性（崩溃后恢复未刷盘的已提交事务） 2. 避免频繁刷盘（仅需写日志，无需立即刷数据页）
生命周期	事务提交后需保留（直到无事务依赖历史版本），由Purge线程清理	事务提交后，日志可被覆盖（数据页刷盘后，对应的Redo Log失效）

15.3 更新数据时各种日志的写入时机

当执行一条UPDATE语句（如UPDATE user SET name='test' WHERE id=1）时，日志写入顺序如下：

1. 写Undo Log：在修改数据前，先记录Undo Log（如“id=1的name原值为’old’，回滚时需改回’old’”），确保后续可回滚。
2. 修改内存数据页：直接更新InnoDB缓冲池中的数据页（不立即刷盘）。
3. 写Redo Log：记录数据页的变更（如“user表id=1的数据页，name字段从’old’改为’test’”），标记为“prepare”状态，确保崩溃后可恢复该变更。
4. 事务提交：
   • 写Binlog：将整个UPDATE语句的逻辑日志写入Binlog，并刷盘。
   • 写Redo Log：将Redo Log的状态从“prepare”改为“commit”，完成事务提交。

关键结论：Undo Log“先于修改”，Redo Log“伴随修改”，Binlog“最后提交时写入”，三者配合确保事务的ACID特性。

16 日志相关问题总结

16.1 MySQL 为什么不用 Redo Log 来进行主从复制

核心原因在于Redo Log的“引擎依赖性”和“写入特性”不适合跨实例同步：

1. 引擎局限性：Redo Log是InnoDB特有的物理日志，而MySQL支持MyISAM、Memory等多种存储引擎，若用Redo Log复制，非InnoDB引擎的变更无法同步。
2. 写入方式问题：Redo Log是“循环写”（写满后覆盖旧日志），而主从复制需要“追加写”的日志（从库需按顺序回放所有变更，不能丢失历史日志），Redo Log的覆盖特性无法满足。
3. 同步工具兼容性：主流主从同步工具（如MySQL原生复制、MGR）均基于Binlog的逻辑日志设计，可解析SQL语句或行级变更，而Redo Log的物理格式（数据页偏移量、二进制值）无法被非InnoDB实例解析。

16.2 其它有关 MySQL 日志相关的问题

问题1：Redo Log 在 prepare 阶段是否开始刷盘？

是的。Redo Log在事务执行过程中会“持续刷盘”：当执行数据修改时，Redo Log先写入内存中的“Redo Log Buffer”，然后通过“后台线程”或“事务提交触发”刷盘；在二阶段提交的“prepare”阶段，Redo Log会确保已刷盘（仅标记为prepare状态），而Binlog仅在“commit”阶段刷盘——这种设计可避免“Redo Log已提交但Binlog未写”的数据不一致问题。

问题2：二阶段提交（2PC）为什么能保证数据一致性？

二阶段提交通过“prepare→commit”两步确保Redo Log和Binlog的一致性：

• prepare阶段：写Redo Log并刷盘（标记prepare），此时事务未完成，若崩溃，重启后检查Binlog：若Binlog未写，则回滚；若Binlog已写，则提交。
• commit阶段：先写Binlog并刷盘，再将Redo Log标记为commit。此时两日志均已持久化，事务完成。
  核心作用：避免“Redo Log已提交但Binlog未写”（从库无法同步）或“Binlog已写但Redo Log未提交”（主库崩溃后丢失数据）的不一致场景。

问题3：MySQL Binlog 里面的时间为什么有些不是顺序的？

Binlog记录的是“事务提交时间”，而非“事务开始/执行时间”，若多个事务的“执行顺序”与“提交顺序”不一致，就会出现Binlog时间不连续的情况。
示例：

• 事务A：10:00开始执行，10:05提交，Binlog记录时间为10:05。
• 事务B：10:02开始执行，10:03提交，Binlog记录时间为10:03。
  此时Binlog中事务B的时间（10:03）会早于事务A的时间（10:05），但事务A的执行开始时间更早，导致Binlog时间“看似无序”，实则按“提交顺序”有序记录，不影响主从复制（从库按Binlog顺序回放即可）。

17 章末总结

知识点	需要掌握的内容
Binlog	Binlog的配置、Binlog的作用、Binlog的记录格式、Binlog内容解析、Binlog的清除、Binlog的落盘
General Log	General Log的配置、查看General Log、General Log的作用及不足
Slow Log	Slow log的开启、一些特殊设置、Slow Log的解析、8.0 慢查询额外信息、mysqldumpslow
Error Log	Error Log的配置和查看、Error Log的作用、Error Log的过滤、通过表记录Error Log
Redo Log	Redo Log 是什么？如何配置、在崩溃恢复中的作用、Redo Log的最佳实践、Redo Log的落盘、Redo Log的归档、Redo Log的禁用
Undo Log	Undo Log的作用、Undo Log中的内容、不同语句类型Undo Log的区别、Undo Log 在奔溃恢复过程的作用、Undo Log的最佳实践、Undo Log的Purge、Undo Log的配置
Binlog、Redo Log、Undo Log的区别	Binlog和Redo Log的区别、Undo Log和Redo Log的区别、更新数据时各种日志的写入时机

本章核心围绕MySQL的5类关键日志展开，各类日志的核心定位与最佳实践可概括为：

1. Binlog：“全局变更日志”，核心用于主从复制和增量备份，关键配置包括log-bin（开启）、binlog_format（推荐row模式保证主从一致）、binlog_expire_logs_seconds（控制保留时间，避免磁盘溢出）。
2. Redo Log：“InnoDB的持久性保障”，关键配置innodb_flush_log_at_trx_commit=1（确保事务提交即刷盘，不丢数据）、innodb_redo_log_capacity（根据业务写入量调整日志大小，避免频繁切换）。
3. Undo Log：“回滚与MVCC的基础”，需配置独立Undo表空间（innodb_undo_directory）、开启自动截断（innodb_undo_log_truncate=ON），并监控长事务（避免Undo Log堆积）。
4. General Log：“全量操作日志”，生产环境建议关闭（性能开销大），仅在排查特殊问题时临时开启。
5. Slow Log：“性能优化工具”，关键配置long_query_time=1（捕获1秒以上的慢查询）、log_queries_not_using_indexes=ON（捕获无索引查询），配合mysqldumpslow工具分析优化。

各类日志的协同作用：Binlog保证“跨实例一致性”（复制），Redo/Undo Log保证“InnoDB内部一致性”（崩溃恢复、回滚），Slow Log保证“性能可控”，共同支撑MySQL的稳定运行。