MySQL教程 / 第 150 节
第15章：MySQL日志系统 ⭐⭐⭐
本章是MySQL专家必须精通的核心内容，尤其是binlog机制
15.1 MySQL日志系统概述
MySQL有多种日志类型，每种日志都有其特定的用途：
| 日志类型 | 作用 | 重要程度 |
|---|---|---|
| binlog（二进制日志） | 记录所有DDL和DML语句，用于复制和恢复 | ⭐⭐⭐⭐⭐ |
| redo log（重做日志） | InnoDB引擎特有，保证事务持久性 | ⭐⭐⭐⭐⭐ |
| undo log（回滚日志） | 保证事务原子性，实现MVCC | ⭐⭐⭐⭐⭐ |
| slow query log（慢查询日志） | 记录慢查询，用于性能优化 | ⭐⭐⭐⭐ |
| error log（错误日志） | 记录MySQL启动、运行、停止时的错误信息 | ⭐⭐⭐⭐ |
| general log（通用查询日志） | 记录所有SQL语句 | ⭐⭐ |
| relay log（中继日志） | 主从复制时，从库使用 | ⭐⭐⭐⭐ |
15.2 binlog（二进制日志）详解 ⭐⭐⭐⭐⭐
15.2.1 binlog的作用
binlog是MySQL最重要的日志之一，主要用途：
- 主从复制：主库的binlog传输到从库进行重放，实现数据同步
- 数据恢复：通过binlog进行时间点恢复（Point-In-Time Recovery）
- 数据审计：记录所有数据变更操作
15.2.2 binlog vs redo log
这是面试高频问题，必须理解：
| 特性 | binlog | redo log |
|---|---|---|
| 层级 | MySQL Server层，所有引擎都可用 | InnoDB引擎层 |
| 内容 | 逻辑日志，记录SQL语句或行变更 | 物理日志，记录数据页的修改 |
| 写入方式 | 追加写入，不会覆盖 | 循环写入，空间固定 |
| 用途 | 复制、恢复 | 崩溃恢复 |
| 格式 | ROW/STATEMENT/MIXED | 固定格式 |
两阶段提交： 为了保证binlog和redo log的一致性，MySQL使用两阶段提交：
1. prepare阶段：写入redo log，状态为prepare
2. commit阶段：写入binlog，然后提交redo log
15.2.3 binlog的三种格式
1. STATEMENT格式（语句模式）
特点：
- 记录执行的SQL语句
- 日志量小
- 可能导致主从数据不一致
示例：
UPDATE users SET login_count = login_count + 1 WHERE id = 100;
binlog记录：
UPDATE users SET login_count = login_count + 1 WHERE id = 100
问题场景：
-- 主库执行
DELETE FROM users WHERE create_time < NOW() LIMIT 10;
-- 如果主从库的数据顺序不同，删除的数据可能不一样
-- 使用UUID()、RAND()等函数也会导致不一致
2. ROW格式（行模式）⭐ 推荐
特点：
- 记录每一行数据的变化
- 日志量大
- 数据一致性最好
- MySQL 5.7默认格式
示例：
UPDATE users SET login_count = login_count + 1 WHERE id = 100;
binlog记录（简化）：
### UPDATE `test`.`users`
### WHERE
### @1=100 /* id */
### @2=5 /* login_count */
### SET
### @1=100 /* id */
### @2=6 /* login_count */
优点：
- 任何情况下都能保证主从一致
- 可以精确恢复每一行数据
- 适合数据审计
缺点：
- 批量操作时日志量大
- 例如：
UPDATE users SET status=1更新100万行，会记录100万行的变化
3. MIXED格式（混合模式）
特点：
- 默认使用STATEMENT格式
- 遇到可能导致不一致的语句时，自动切换到ROW格式
- 平衡了日志大小和一致性
自动切换到ROW的场景：
- 使用UUID()、USER()、CURRENT_USER()等函数
- 使用AUTO_INCREMENT列
- 使用LIMIT且没有ORDER BY
- 使用触发器或存储过程
15.2.4 binlog的配置
开启binlog
配置文件（my.cnf/my.ini）：
[mysqld]
# 开启binlog（必须）
log-bin=mysql-bin
# binlog格式（推荐ROW）
binlog_format=ROW
# 每个binlog文件的最大大小（默认1G）
max_binlog_size=1G
# binlog过期时间（天），0表示不自动删除
expire_logs_days=7
# MySQL 8.0使用这个参数（秒）
# binlog_expire_logs_seconds=604800
# 同步binlog到磁盘的策略
# 0: 由操作系统决定何时刷新（性能最好，安全性最差）
# 1: 每次事务提交都刷新（最安全，性能较差）⭐ 推荐
# N: 每N个事务刷新一次
sync_binlog=1
# binlog缓存大小
binlog_cache_size=4M
# 单个事务的binlog缓存大小
max_binlog_cache_size=512M
# 指定哪些数据库记录binlog（可选）
# binlog-do-db=mydb
# 指定哪些数据库不记录binlog（可选）
# binlog-ignore-db=test
# server_id（主从复制必须，每个MySQL实例唯一）
server-id=1
重启MySQL使配置生效：
sudo systemctl restart mysqld
查看binlog配置
-- 查看binlog是否开启
SHOW VARIABLES LIKE 'log_bin';
-- 查看binlog格式
SHOW VARIABLES LIKE 'binlog_format';
-- 查看所有binlog相关配置
SHOW VARIABLES LIKE 'binlog%';
-- 查看sync_binlog配置
SHOW VARIABLES LIKE 'sync_binlog';
15.2.5 binlog的管理
查看binlog文件
-- 查看所有binlog文件
SHOW BINARY LOGS;
-- 或
SHOW MASTER LOGS;
-- 输出示例：
-- +------------------+-----------+
-- | Log_name | File_size |
-- +------------------+-----------+
-- | mysql-bin.000001 | 177 |
-- | mysql-bin.000002 | 1547 |
-- | mysql-bin.000003 | 154 |
-- +------------------+-----------+
-- 查看当前正在写入的binlog
SHOW MASTER STATUS;
-- 输出示例：
-- +------------------+----------+--------------+------------------+
-- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-- +------------------+----------+--------------+------------------+
-- | mysql-bin.000003 | 154 | | |
-- +------------------+----------+--------------+------------------+
查看binlog内容
使用mysqlbinlog工具：
# 查看binlog内容（基本格式）
mysqlbinlog mysql-bin.000001
# 查看binlog内容（详细格式，ROW格式必须加-v）
mysqlbinlog -v mysql-bin.000001
# 查看binlog内容（超详细格式）
mysqlbinlog -vv mysql-bin.000001
# 指定开始位置
mysqlbinlog --start-position=154 mysql-bin.000001
# 指定结束位置
mysqlbinlog --stop-position=500 mysql-bin.000001
# 指定时间范围
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
--stop-datetime="2024-01-01 23:59:59" \
mysql-bin.000001
# 输出到文件
mysqlbinlog mysql-bin.000001 > binlog.sql
# 查看多个binlog文件
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > all_binlog.sql
# 只查看指定数据库的binlog
mysqlbinlog --database=mydb mysql-bin.000001
# 解析ROW格式的binlog（必须加-v或-vv）
mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.000001
binlog内容示例：
STATEMENT格式：
# at 123
#241101 10:30:45 server id 1 end_log_pos 250 Query thread_id=5
SET TIMESTAMP=1698825045/*!*/;
UPDATE users SET login_count = login_count + 1 WHERE id = 100
/*!*/;
ROW格式（需要-v参数）：
# at 123
#241101 10:30:45 server id 1 end_log_pos 250 Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`users`
### WHERE
### @1=100 /* INT meta=0 nullable=0 is_null=0 */
### @2='张三' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=5 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=100
### @2='张三'
### @3=6
删除binlog
-- 删除指定binlog之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000003';
-- 删除指定时间之前的binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 删除所有binlog（危险操作！）
RESET MASTER;
-- 查看binlog占用空间
SELECT
CONCAT(ROUND(SUM(File_size)/1024/1024, 2), 'MB') AS binlog_size
FROM information_schema.BINARY_LOGS;
自动清理配置：
# 配置文件中设置
expire_logs_days=7 # MySQL 5.7
binlog_expire_logs_seconds=604800 # MySQL 8.0（7天）
刷新binlog
-- 手动切换到新的binlog文件
FLUSH LOGS;
-- 或使用命令行
mysqladmin -u root -p flush-logs
使用场景：
- 备份前刷新，方便管理
- binlog文件过大时手动切换
- 定期归档binlog
15.2.6 binlog实战案例
案例1：查看某个表的所有变更
# 查看users表的所有变更
mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.* | grep -A 20 "users"
案例2：统计某个时间段的操作
# 统计今天的INSERT、UPDATE、DELETE数量
mysqlbinlog --start-datetime="2024-11-01 00:00:00" \
--stop-datetime="2024-11-01 23:59:59" \
mysql-bin.* | grep -E "INSERT|UPDATE|DELETE" | wc -l
案例3：找出误删除的数据
# 假设在10:30误删了数据，查看10:00-11:00的binlog
mysqlbinlog -vv --start-datetime="2024-11-01 10:00:00" \
--stop-datetime="2024-11-01 11:00:00" \
mysql-bin.000003 | grep -A 50 "DELETE FROM users"
案例4：恢复误删除的数据
# 1. 找到误删除的位置
mysqlbinlog -vv mysql-bin.000003 | grep -B 5 -A 20 "DELETE FROM users"
# 2. 提取误删除之前的binlog（假设误删除位置是500）
mysqlbinlog --stop-position=500 mysql-bin.000003 > before_delete.sql
# 3. 提取误删除之后的binlog（假设误删除结束位置是600）
mysqlbinlog --start-position=600 mysql-bin.000003 > after_delete.sql
# 4. 恢复数据
mysql -u root -p < before_delete.sql
# 手动恢复被删除的数据
mysql -u root -p < after_delete.sql
15.3 redo log（重做日志）详解 ⭐⭐⭐⭐⭐
15.3.1 redo log的作用
redo log是InnoDB存储引擎特有的日志，用于实现事务的持久性（Durability）。
核心问题： 如果每次事务提交都将数据刷新到磁盘，性能会很差。redo log解决了这个问题。
工作原理：
- 事务提交时，先写redo log（顺序写，速度快）
- 数据页的修改可以延迟写入磁盘（随机写，速度慢）
- MySQL崩溃后，通过redo log恢复未刷盘的数据
这就是**WAL（Write-Ahead Logging）**机制。
15.3.2 redo log的结构
物理结构：
- redo log由一组文件组成（默认2个）
- 文件名：
ib_logfile0、ib_logfile1 - 循环写入，空间固定
逻辑结构：
+---+---+---+---+---+---+---+---+
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ← redo log文件（循环）
+---+---+---+---+---+---+---+---+
↑ ↑
write pos checkpoint
write pos: 当前写入位置
checkpoint: 当前擦除位置（已刷盘的数据）
状态：
write pos和checkpoint之间：可以写入的空间checkpoint和write pos之间：已写入但未刷盘的数据- 如果
write pos追上checkpoint，需要先刷盘，腾出空间
15.3.3 redo log的配置
[mysqld]
# redo log文件大小（每个文件）
innodb_log_file_size=512M
# redo log文件数量
innodb_log_files_in_group=2
# redo log缓冲区大小
innodb_log_buffer_size=16M
# redo log刷盘策略
# 0: 每秒刷一次（性能最好，可能丢失1秒数据）
# 1: 每次事务提交都刷盘（最安全）⭐ 推荐
# 2: 每次事务提交写到OS缓存，每秒刷盘
innodb_flush_log_at_trx_commit=1
查看配置：
SHOW VARIABLES LIKE 'innodb_log%';
15.3.4 redo log与binlog的两阶段提交
为什么需要两阶段提交？
假设执行：UPDATE users SET age=20 WHERE id=1
如果没有两阶段提交：
先写redo log，再写binlog
- 如果写完redo log后崩溃，重启后数据已更新，但binlog没记录
- 主从复制时，从库不会执行这个更新，导致主从不一致
先写binlog，再写redo log
- 如果写完binlog后崩溃，重启后数据未更新，但binlog已记录
- 主从复制时，从库会执行这个更新，导致主从不一致
两阶段提交流程：
1. prepare阶段：
- 写入redo log，标记为prepare状态
- 此时事务还未提交
2. commit阶段：
- 写入binlog
- 提交redo log，标记为commit状态
- 事务提交完成
崩溃恢复时：
- 如果redo log是prepare状态，且binlog完整 → 提交事务
- 如果redo log是prepare状态，但binlog不完整 → 回滚事务
- 如果redo log是commit状态 → 事务已提交
图示：
事务执行流程：
┌─────────┐
│ 执行SQL │
└────┬────┘
│
▼
┌─────────────────┐
│ 写入redo log │ ← prepare阶段
│ (prepare状态) │
└────┬────────────┘
│
▼
┌─────────────────┐
│ 写入binlog │
└────┬────────────┘
│
▼
┌─────────────────┐
│ 提交redo log │ ← commit阶段
│ (commit状态) │
└────┬────────────┘
│
▼
┌─────────────────┐
│ 事务提交完成 │
└─────────────────┘
15.3.5 redo log性能优化
参数调优：
# 增大redo log文件大小，减少checkpoint频率
innodb_log_file_size=1G
# 增大redo log缓冲区
innodb_log_buffer_size=32M
# 根据业务场景调整刷盘策略
# 金融系统：innodb_flush_log_at_trx_commit=1（最安全）
# 一般系统：innodb_flush_log_at_trx_commit=1（推荐）
# 高性能要求：innodb_flush_log_at_trx_commit=2（可能丢失1秒数据）
监控redo log：
-- 查看redo log使用情况
SHOW ENGINE INNODB STATUS\G
-- 关注以下指标：
-- Log sequence number: 当前LSN
-- Log flushed up to: 已刷盘的LSN
-- Pages flushed up to: 已刷盘的页LSN
-- Last checkpoint at: 最后一次checkpoint的LSN
15.4 undo log（回滚日志）详解 ⭐⭐⭐⭐⭐
15.4.1 undo log的作用
undo log有两个重要作用：
- 事务回滚：保证事务的原子性（Atomicity）
- MVCC（多版本并发控制）：实现一致性非锁定读
15.4.2 undo log的工作原理
记录内容：
- INSERT操作：记录主键，回滚时删除这条记录
- DELETE操作：记录整行数据，回滚时插入这条记录
- UPDATE操作：记录修改前的值，回滚时恢复
示例：
-- 原始数据
id=1, name='张三', age=20
-- 执行UPDATE
UPDATE users SET age=30 WHERE id=1;
-- undo log记录
UPDATE users SET age=20 WHERE id=1; -- 回滚时执行这个
15.4.3 undo log与MVCC
MVCC原理： 每行数据都有两个隐藏列：
DB_TRX_ID：最后修改该行的事务IDDB_ROLL_PTR：指向undo log的指针
版本链：
当前版本: id=1, name='张三', age=30, trx_id=100
↓ (DB_ROLL_PTR)
undo log: id=1, name='张三', age=20, trx_id=90
↓
undo log: id=1, name='张三', age=10, trx_id=80
读取数据时：
- 根据事务隔离级别和事务ID
- 通过版本链找到对应版本的数据
- 实现了不加锁的一致性读
详细内容见：第08章：事务与并发控制
15.4.4 undo log的配置
[mysqld]
# undo表空间数量（MySQL 5.7）
innodb_undo_tablespaces=2
# undo日志目录
innodb_undo_directory=/var/lib/mysql/undo
# undo日志回收（MySQL 5.7.5+）
innodb_undo_log_truncate=ON
# undo表空间最大大小
innodb_max_undo_log_size=1G
查看undo log：
-- 查看undo log配置
SHOW VARIABLES LIKE 'innodb_undo%';
-- 查看undo log使用情况
SELECT
tablespace_name,
file_name,
file_size/1024/1024 AS size_mb
FROM information_schema.FILES
WHERE tablespace_name LIKE 'innodb_undo%';
15.5 慢查询日志（Slow Query Log）⭐⭐⭐⭐
15.5.1 慢查询日志的作用
记录执行时间超过阈值的SQL语句，用于性能优化。
15.5.2 配置慢查询日志
[mysqld]
# 开启慢查询日志
slow_query_log=1
# 慢查询日志文件
slow_query_log_file=/var/lib/mysql/slow.log
# 慢查询时间阈值（秒）
long_query_time=2
# 记录没有使用索引的查询
log_queries_not_using_indexes=1
# 限制每分钟记录的未使用索引的查询数量
log_throttle_queries_not_using_indexes=10
# 记录管理语句（如ALTER TABLE）
log_slow_admin_statements=1
# 记录从库上的慢查询
log_slow_slave_statements=1
动态开启：
-- 开启慢查询日志
SET GLOBAL slow_query_log=1;
-- 设置慢查询时间阈值
SET GLOBAL long_query_time=2;
-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
15.5.3 分析慢查询日志
查看慢查询日志：
# 直接查看
tail -f /var/lib/mysql/slow.log
# 使用mysqldumpslow工具分析
# 按查询次数排序，显示前10条
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 按查询时间排序
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/lib/mysql/slow.log
# 按返回记录数排序
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 组合使用
mysqldumpslow -s t -t 10 -g "SELECT" /var/lib/mysql/slow.log
使用pt-query-digest工具（推荐）：
# 安装percona-toolkit
yum install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log
# 输出到文件
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt
# 只分析最近1小时的日志
pt-query-digest --since=1h /var/lib/mysql/slow.log
# 分析并输出到数据库
pt-query-digest --review h=localhost,D=test,t=query_review \
--history h=localhost,D=test,t=query_history \
/var/lib/mysql/slow.log
慢查询日志示例：
# Time: 2024-11-01T10:30:45.123456Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.123456 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000
SET timestamp=1698825045;
SELECT * FROM users WHERE age > 20 ORDER BY create_time DESC LIMIT 100;
15.6 错误日志（Error Log）⭐⭐⭐⭐
15.6.1 错误日志的作用
记录MySQL启动、运行、停止过程中的错误、警告和注意信息。
15.6.2 配置错误日志
[mysqld]
# 错误日志文件
log-error=/var/log/mysqld.log
# 日志级别（MySQL 8.0）
# 1: ERROR
# 2: ERROR, WARNING
# 3: ERROR, WARNING, INFORMATION
log_error_verbosity=2
查看错误日志位置：
SHOW VARIABLES LIKE 'log_error';
查看错误日志：
# 查看最新的错误
tail -f /var/log/mysqld.log
# 查看最近100行
tail -n 100 /var/log/mysqld.log
# 搜索错误
grep -i error /var/log/mysqld.log
# 搜索警告
grep -i warning /var/log/mysqld.log
15.7 通用查询日志（General Query Log）
15.7.1 通用查询日志的作用
记录所有SQL语句，包括SELECT、INSERT、UPDATE、DELETE等。
注意： 生产环境不建议开启，会严重影响性能！
15.7.2 配置通用查询日志
[mysqld]
# 开启通用查询日志
general_log=1
# 日志文件
general_log_file=/var/lib/mysql/general.log
动态开启（临时调试用）：
-- 开启
SET GLOBAL general_log=1;
-- 关闭
SET GLOBAL general_log=0;
-- 查看配置
SHOW VARIABLES LIKE 'general_log%';
15.8 中继日志（Relay Log）
15.8.1 中继日志的作用
主从复制时，从库使用中继日志：
- 从库的IO线程从主库读取binlog
- 写入从库的relay log
- 从库的SQL线程读取relay log并执行
详细内容见：第22章：主从复制
15.9 日志系统最佳实践
15.9.1 生产环境推荐配置
[mysqld]
# binlog配置
log-bin=mysql-bin
binlog_format=ROW
sync_binlog=1
expire_logs_days=7
max_binlog_size=1G
# redo log配置
innodb_log_file_size=1G
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
# 慢查询日志
slow_query_log=1
long_query_time=2
log_queries_not_using_indexes=1
# 错误日志
log-error=/var/log/mysqld.log
15.9.2 日志监控
监控指标：
- binlog生成速度
- binlog磁盘占用
- 慢查询数量
- 错误日志中的ERROR和WARNING
监控脚本示例：
#!/bin/bash
# 检查binlog大小
binlog_size=$(mysql -e "SELECT CONCAT(ROUND(SUM(File_size)/1024/1024, 2), 'MB') FROM information_schema.BINARY_LOGS;" -N)
echo "Binlog total size: $binlog_size"
# 检查慢查询数量
slow_queries=$(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" -N | awk '{print $2}')
echo "Slow queries: $slow_queries"
# 检查错误日志
error_count=$(grep -c "ERROR" /var/log/mysqld.log)
echo "Error count: $error_count"
15.9.3 日志归档
binlog归档脚本：
#!/bin/bash
# binlog归档脚本
BACKUP_DIR="/backup/binlog"
MYSQL_BIN_DIR="/var/lib/mysql"
DATE=$(date +%Y%m%d)
# 创建归档目录
mkdir -p $BACKUP_DIR/$DATE
# 刷新binlog
mysql -e "FLUSH LOGS;"
# 获取当前binlog文件
current_binlog=$(mysql -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
# 复制除当前binlog外的所有binlog
for binlog in $(ls $MYSQL_BIN_DIR/mysql-bin.* | grep -v $current_binlog); do
cp $binlog $BACKUP_DIR/$DATE/
done
# 删除7天前的归档
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;
echo "Binlog archived to $BACKUP_DIR/$DATE"
15.10 小结
本章学习了MySQL的日志系统：
- ✅ binlog：主从复制和数据恢复的核心，必须精通
- ✅ redo log：保证事务持久性，理解WAL机制
- ✅ undo log：事务回滚和MVCC的基础
- ✅ 慢查询日志：性能优化的重要工具
- ✅ 错误日志：故障排查的第一手资料
- ✅ 两阶段提交：保证binlog和redo log的一致性
重点掌握：
- binlog的三种格式及应用场景
- binlog的查看和管理
- redo log与binlog的区别
- 两阶段提交的原理
- 慢查询日志的分析
下一章预告： 备份策略与实践
练习题
- 配置MySQL开启binlog，格式为ROW
- 执行一些DML操作，使用mysqlbinlog查看binlog内容
- 配置慢查询日志，找出执行时间超过1秒的SQL
- 理解并画出两阶段提交的流程图
- 编写脚本定期归档binlog
继续学习： 第16章：备份策略与实践