知识库

Appearance

MySQL 日志系统 ​

  MySQL 的日志系统是保证数据一致性和实现高可用的核心基础设施。redo log 保证崩溃恢复，undo log 支持事务回滚和 MVCC，binlog 用于主从复制和数据恢复。理解日志系统对于性能调优和问题排查至关重要。

1. redo log（重做日志） ​

1.1 什么是 redo log ​

  redo log 是 InnoDB 存储引擎层的日志，用于保证事务的持久性。它记录的是数据页的物理修改，实现"先写日志，再写数据"（WAL, Write-Ahead Logging）机制。

为什么需要 WAL：

  • 数据页是随机写入磁盘，I/O 性能差。
  • redo log 是顺序写入磁盘，I/O 性能好。
  • 先将修改写入 redo log（顺序写），再在后台将数据页刷入磁盘（随机写）。
  • 崩溃恢复时，通过 redo log 恢复未写入磁盘的数据。

1.2 redo log 结构 ​

redo log 循环写入：
┌────────────────────────────────────────────────────────────┐
│                     redo log 文件组                         │
│                                                            │
│  ┌──────────────────────────┐  ┌──────────────────────────┐│
│  │    ib_logfile0           │  │    ib_logfile1           ││
│  │                          │  │                          ││
│  │  write pos ─┐            │  │                          ││
│  │  (写入位置)  │            │  │                          ││
│  │             ▼            │  │                          ││
│  │  ┌───────────────────┐   │  │                          ││
│  │  │  待写入的 log block   │   │  │                          ││
│  │  └───────────────────┘   │  │                          ││
│  │             ▲            │  │                          ││
│  │  checkpoint │            │  │                          ││
│  │  (刷盘位置)  │            │  │                          ││
│  └──────────────────────────┘  └──────────────────────────┘│
│                                                            │
│  write pos 和 checkpoint 之间的部分是空闲可写入的              │
│  如果 write pos 追上 checkpoint，需要等待刷盘                 │
└────────────────────────────────────────────────────────────┘

关键概念：

概念说明
write pos当前 redo log 写入位置
checkpoint当前数据页已刷盘的位置（之前的 redo log 可以覆盖）
LSNLog Sequence Number，日志序列号，单调递增

1.3 redo log 刷盘策略 ​

sql
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
值含义性能安全性
0每秒将 log buffer 写入 OS 缓存并刷盘最高可能丢失 1 秒数据
1每次提交都将 log buffer 写入 OS 缓存并刷盘最低最安全
2每次提交写入 OS 缓存，每秒刷盘中等MySQL 宕机不丢，系统宕机可能丢 1 秒

推荐配置：

  • 生产环境：innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1（双 1 配置）。
  • 对性能要求极高且允许少量数据丢失：innodb_flush_log_at_trx_commit = 2。

1.4 redo log 参数配置 ​

sql
-- redo log 文件大小（单个文件）
SHOW VARIABLES LIKE 'innodb_log_file_size';
-- 默认 48MB，建议 1-2GB

-- redo log 文件组数量
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
-- 默认 2，建议 3-4

-- redo log 总大小 = innodb_log_file_size * innodb_log_files_in_group

-- log buffer 大小
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
-- 默认 16MB，大事务场景可增大

-- 重做日志加密（MySQL 8.0.30+）
ALTER INSTANCE ENABLE INNODB REDO_LOG_ENCRYPTION;

1.5 崩溃恢复 ​

  MySQL 崩溃后重启，InnoDB 通过 redo log 进行崩溃恢复：

  1. 读取 redo log 中 checkpoint 之后的日志。
  2. 重放（Replay）所有已提交事务的 redo log。
  3. 通过 undo log 回滚未提交的事务。
  4. 恢复完成后，数据库达到一致性状态。

2. undo log（回滚日志） ​

2.1 什么是 undo log ​

  undo log 是 InnoDB 存储引擎层的日志，用于实现事务的原子性和 MVCC。它记录的是数据修改前的逻辑状态。

核心作用：

  • 事务回滚：ROLLBACK 时，通过 undo log 将数据恢复到修改前的状态。
  • MVCC：提供数据的历史版本，供快照读使用。

2.2 undo log 版本链 ​

每次修改都会生成 undo log 记录，通过 DB_ROLL_PTR 形成版本链：

当前行（最新版本）：
┌──────────────────────────────────────────────────┐
│  id=1, name='Alice', age=26                      │
│  DB_TRX_ID=103, DB_ROLL_PTR ────────────────┐    │
└────────────────────────────────────────────┬─┘    │
                                             │      │
                                             ▼      │
undo log 记录 1（UPDATE age 25 -> 26）：           │
┌──────────────────────────────────────────────────┐│
│  id=1, name='Alice', age=25                      ││
│  DB_TRX_ID=102, DB_ROLL_PTR ────────────────┐   ││
└────────────────────────────────────────────┬─┘   ││
                                             │     ││
                                             ▼     ││
undo log 记录 2（INSERT）：                        ││
┌──────────────────────────────────────────────────┐││
│  id=1, name='Alice'（原始 INSERT 记录）           │││
│  DB_TRX_ID=101, DB_ROLL_PTR = NULL              │││
└──────────────────────────────────────────────────┘││
                                                    ││
                                                    ▼▼

2.3 undo log 存储 ​

MySQL 5.6 及之前：

  • undo log 存储在共享表空间（ibdata1）中。
  • 共享表空间会不断增大，无法收缩。

MySQL 5.7+：

  • undo log 可以存储在独立的 undo 表空间中。
  • 支持在线收缩 undo 表空间。

MySQL 8.0：

  • 默认使用独立的 undo 表空间。
  • 支持自动截断 undo 表空间。
sql
-- 查看 undo 表空间
SELECT * FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'undo%';

-- 设置 undo 表空间数量
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';

2.4 undo log 的清理 ​

  undo log 由 Purge Thread 负责清理：

  1. 事务提交后，undo log 不再用于回滚。
  2. 但如果有其他事务需要通过 MVCC 读取历史版本，undo log 不能立即删除。
  3. Purge Thread 判断 undo log 不再被任何事务需要时，将其删除。
sql
-- 查看 purge 相关配置
SHOW VARIABLES LIKE 'innodb_purge_threads';
SHOW VARIABLES LIKE 'innodb_purge_batch_size';
SHOW VARIABLES LIKE 'innodb_max_purge_lag';

3. binlog（二进制日志） ​

3.1 什么是 binlog ​

  binlog（Binary Log）是 MySQL Server 层的日志，记录所有对数据库的修改操作（DDL 和 DML）。主要用于主从复制和数据恢复。

binlog 与 redo log 的区别：

特性binlogredo log
所属层次Server 层InnoDB 引擎层
记录内容逻辑日志（SQL 语句或行变更）物理日志（数据页修改）
写入方式追加写（文件写满后切换）循环写（固定大小，循环覆盖）
用途主从复制、数据恢复崩溃恢复
是否可读可解析（mysqlbinlog）不可直接解析
所有引擎所有引擎都支持只有 InnoDB 支持

3.2 binlog 格式 ​

sql
-- 查看 binlog 格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置 binlog 格式
SET GLOBAL binlog_format = 'ROW';
格式说明优点缺点
STATEMENT记录 SQL 语句日志量小主从不一致（如 NOW()、UUID()）
ROW记录行变更（默认 5.7.7+）数据一致性好日志量大
MIXED默认 STATEMENT，必要时 ROW综合两者不是所有情况都能覆盖

ROW 格式的三种记录方式：

sql
SHOW VARIABLES LIKE 'binlog_row_image';

-- FULL：记录所有列（默认）
-- MINIMAL：只记录修改的列和主键
-- NOBLOB：不记录 BLOB/TEXT 列（除非被修改）

3.3 binlog 参数配置 ​

sql
-- 开启 binlog
SHOW VARIABLES LIKE 'log_bin';

-- binlog 文件路径
SHOW VARIABLES LIKE 'log_bin_basename';

-- binlog 刷盘策略
SHOW VARIABLES LIKE 'sync_binlog';
-- 0：由 OS 控制刷盘
-- 1：每次提交刷盘（推荐，配合 innodb_flush_log_at_trx_commit=1）
-- N：每 N 次提交刷盘

-- binlog 文件大小
SHOW VARIABLES LIKE 'max_binlog_size';
-- 默认 1GB

-- binlog 过期时间（MySQL 8.0+）
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
-- 默认 2592000（30 天）

-- 查看 binlog 列表
SHOW BINARY LOGS;
SHOW MASTER STATUS;

3.4 binlog 操作命令 ​

sql
-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 使用 mysqlbinlog 解析
-- mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

-- 基于时间点恢复
-- mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
--              --stop-datetime="2024-01-01 11:00:00" \
--              mysql-bin.000001 | mysql -u root -p

-- 基于位置恢复
-- mysqlbinlog --start-position=100 --stop-position=200 mysql-bin.000001

-- 手动切换 binlog 文件
FLUSH LOGS;

-- 清理 binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
PURGE BINARY LOGS TO 'mysql-bin.000010';

4. 两阶段提交（Two-Phase Commit） ​

4.1 为什么需要两阶段提交 ​

  redo log 和 binlog 是两种独立的日志，如果它们在事务提交时不完全一致，将导致主从数据不一致或崩溃恢复错误。

问题场景：

  • 先写 redo log 后写 binlog：redo log 写入后崩溃，binlog 未写入，从库缺少该事务。
  • 先写 binlog 后写 redo log：binlog 写入后崩溃，redo log 未写入，主库缺少该事务，从库多了该事务。

4.2 两阶段提交流程 ​

事务提交流程：
┌──────────────────────────────────────────────────────────────┐
│  1. Prepare 阶段（准备阶段）                                    │
│     ├── 写入 undo log（准备回滚）                               │
│     ├── 写入 redo log（保证持久性）                             │
│     └── 将 redo log 标记为 prepare 状态                        │
│                                                              │
│  2. Commit 阶段（提交阶段）                                     │
│     ├── 写入 binlog                                           │
│     ├── 将 binlog 刷盘（sync_binlog = 1）                     │
│     ├── 将 redo log 标记为 commit 状态                         │
│     └── 返回客户端成功                                         │
└──────────────────────────────────────────────────────────────┘

崩溃恢复判断：

  1. 扫描 redo log 中处于 prepare 状态的事务。
  2. 检查 binlog 中是否完整记录了该事务。
  3. 如果 binlog 中有：提交事务（redo log 标记为 commit）。
  4. 如果 binlog 中没有：回滚事务。

4.3 两阶段提交的 XA 事务 ​

  MySQL 内部使用 XA 事务实现两阶段提交：

sql
-- 查看 XA 事务
XA START 'xa_transaction';
INSERT INTO users (name) VALUES ('Alice');
XA END 'xa_transaction';
XA PREPARE 'xa_transaction';
XA COMMIT 'xa_transaction';
-- 或 XA ROLLBACK 'xa_transaction';

5. 为什么有了 redo log 还需要 binlog ​

  这是一个常见问题：既然 redo log 已经能保证崩溃恢复，为什么还需要 binlog？

  1. redo log 是 InnoDB 特有的，binlog 是 Server 层的：binlog 可以记录所有引擎的操作。
  2. redo log 是循环写的，历史数据会被覆盖：binlog 是追加写的，可以保留完整的历史记录。
  3. binlog 用于主从复制：从库通过 binlog 同步主库的数据变更。
  4. binlog 用于数据恢复：通过 binlog 可以恢复到任意时间点。
  5. redo log 只记录物理修改：binlog 记录逻辑操作，可读性更好。

6. redo log 与 binlog 对比总结 ​

特性redo logbinlog
所属层次InnoDB 存储引擎层MySQL Server 层
记录内容物理日志：数据页的修改逻辑日志：SQL 语句或行变更
写入方式循环写（固定大小）追加写（不断增长）
文件大小有限（innodb_log_file_size * 文件数）无限（需要定期清理）
适用引擎仅 InnoDB所有存储引擎
主要用途崩溃恢复（crash-safe）主从复制、数据恢复
刷盘控制innodb_flush_log_at_trx_commitsync_binlog
是否可读不可直接解析可用 mysqlbinlog 解析
记录时机事务执行过程中不断写入事务提交时写入

7. 其他日志 ​

7.1 错误日志（Error Log） ​

sql
-- 查看错误日志路径
SHOW VARIABLES LIKE 'log_error';

-- 错误日志级别
SHOW VARIABLES LIKE 'log_error_verbosity';
-- 1: 只记录错误
-- 2: 记录错误和警告
-- 3: 记录错误、警告和注释信息（默认）

-- 错误日志中记录的重要信息
-- · 启动和关闭信息
-- · 错误和警告
-- · 崩溃信息
-- · 死锁信息
-- · 复制错误

错误日志分析：

bash
# 查看 MySQL 错误日志
tail -f /var/log/mysql/error.log

# 搜索错误
grep -i "ERROR" /var/log/mysql/error.log

# 搜索死锁
grep -i "deadlock" /var/log/mysql/error.log

7.2 慢查询日志（Slow Query Log） ​

sql
-- 基本配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 记录慢管理语句
SET GLOBAL log_slow_admin_statements = ON;

-- MySQL 8.0.14+ 记录更多信息
SET GLOBAL log_slow_extra = ON;

7.3 通用查询日志（General Query Log） ​

sql
-- 会记录所有查询，性能影响大，不建议在生产环境开启
SHOW VARIABLES LIKE 'general_log%';

-- 临时开启（排查问题时使用）
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- 排查完成立即关闭
SET GLOBAL general_log = OFF;

7.4 中继日志（Relay Log） ​

  中继日志是从库用于记录从主库 binlog 接收到的日志。

sql
-- 查看 relay log 信息
SHOW SLAVE STATUS\G
-- relay_log_file, relay_log_pos

8. 日志刷盘策略与性能影响 ​

8.1 刷盘策略对比 ​

配置安全性性能适用场景
innodb_flush_log_at_trx_commit=1 + sync_binlog=1最高最低金融、支付等核心业务
innodb_flush_log_at_trx_commit=1 + sync_binlog=0高中等一般业务
innodb_flush_log_at_trx_commit=2 + sync_binlog=0中等高允许少量丢失的场景
innodb_flush_log_at_trx_commit=0 + sync_binlog=0低最高日志、埋点等高吞吐场景

8.2 组提交（Group Commit） ​

  MySQL 通过组提交优化日志刷盘性能：将多个事务的日志合并为一次刷盘操作，减少磁盘 I/O 次数。

sql
-- 组提交相关参数
SHOW VARIABLES LIKE 'binlog_group_commit_sync_delay';
-- 等待多少微秒后刷盘（默认 0，表示不等待）

SHOW VARIABLES LIKE 'binlog_group_commit_sync_no_delay_count';
-- 累积多少个事务后刷盘

8.3 日志相关的性能优化建议 ​

  1. redo log 文件大小：建议 1-2GB，减少 checkpoint 频率。
  2. redo log 文件数量：建议 3-4 个，增加总大小。
  3. log buffer 大小：大事务场景适当增大（128MB）。
  4. binlog 行格式：使用 binlog_row_image = MINIMAL 减少日志量。
  5. binlog 过期清理：设置合理的过期时间，避免磁盘写满。
  6. SSD 磁盘：将 redo log 和 binlog 放在 SSD 上，提高写入性能。
  7. 独立磁盘：redo log 和 binlog 与数据文件放在不同磁盘上，减少 I/O 竞争。

9. 生产环境日志管理 ​

9.1 日志监控 ​

sql
-- 查看 redo log 使用情况
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LOG" 部分

-- 查看 binlog 使用情况
SHOW BINARY LOGS;

-- 查看磁盘空间
-- 通过系统命令监控 /var/lib/mysql 目录大小

9.2 日志清理策略 ​

sql
-- 自动清理 binlog（MySQL 8.0+）
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 天

-- 手动清理 binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 定时任务清理（crontab）
-- 0 2 * * * mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

9.3 日志备份 ​

bash
# 备份 binlog
cp /var/lib/mysql/mysql-bin.* /backup/binlog/

# 使用 mysqlbinlog 备份
mysqlbinlog --read-from-remote-server \
    --host=localhost \
    --user=root \
    --password \
    --raw \
    --stop-never \
    mysql-bin.000001

10. 总结 ​

  • redo log 是 InnoDB 的物理日志，保证崩溃恢复，循环写入。
  • undo log 是 InnoDB 的逻辑日志，支持事务回滚和 MVCC。
  • binlog 是 Server 层的逻辑日志，用于主从复制和数据恢复，追加写入。
  • 两阶段提交确保 redo log 和 binlog 的一致性，避免主从数据不一致。
  • 生产环境建议使用双 1 配置（innodb_flush_log_at_trx_commit=1 + sync_binlog=1）。
  • 组提交优化可以减少日志刷盘的开销，提高并发性能。
  • 定期清理 binlog，避免磁盘空间耗尽。