MySQL日志

MySQL 日志系统全解析：类型、作用与实战配置

MySQL 的日志系统是数据库运维、故障排查和数据恢复的核心支撑，包含六种关键日志：事务日志（redo log、undo log）、二进制日志（binlog）、错误日志、慢查询日志、一般查询日志、中继日志（relay log）。每种日志各司其职，共同保障数据库的稳定性、可追溯性和一致性。

事务日志：保障事务的 ACID 特性

事务日志是 InnoDB 引擎特有的日志，专门用于保证事务的原子性、一致性和持久性，分为重做日志（redo log）和回滚日志（undo log）。

1. 重做日志（redo log）：保证持久性与原子性

核心作用：

当 MySQL 宕机时，通过 redo log 恢复未刷盘的脏页数据，确保事务提交后的数据不丢失（持久性）。

工作原理：

InnoDB 采用 WAL（Write-Ahead Logging，预写式日志） 机制：

数据修改时，先写入内存中的 redo log buffer，再异步 / 同步刷入磁盘的 redo log file；
同时修改内存缓冲池（Buffer Pool）中的数据页（脏页），脏页会定期异步刷入磁盘；
若宕机，重启时通过 redo log 回放未刷盘的脏页操作，恢复数据。

redo log 记录的是物理日志（如 “数据页 X 的偏移量 Y 处修改为 Z”），与具体 SQL 无关。

存储与配置：

存储位置：默认在数据目录下，以 ib_logfile0、ib_logfile1 命名（循环写入，满后覆盖旧日志）。

关键配置：

# 日志文件组路径（默认数据目录）
innodb_log_group_home_dir = ./  
# 日志文件数量（默认 2 个）
innodb_log_files_in_group = 2  
# 单个日志文件大小（默认 48M，需根据事务量调整）
innodb_log_file_size = 500M  
# 刷盘策略（核心参数）
innodb_flush_log_at_trx_commit = 1  

innodb_flush_log_at_trx_commit取值说明：

| 取值 | 含义 | 安全性 | 性能 |
| —— | ———————————————— | ——————————————- | —— |
| 0 | 事务提交时不刷盘，每秒批量刷盘 | 最低（宕机可能丢失 1 秒数据） | 最高 |
| 1 | 事务提交时立即刷盘（默认） | 最高（无数据丢失） | 最低 |
| 2 | 事务提交时写入 OS 缓存，每秒刷盘 | 中（OS 崩溃可能丢失数据） | 中 |

特点：

固定大小，循环写入（类似 “环形缓冲区”）；
仅记录已提交事务的操作，未提交事务的日志会被忽略；
日志大小影响恢复速度：过大延长恢复时间，过小导致频繁切换日志（性能抖动）。

2. 回滚日志（undo log）：保证一致性与 MVCC

核心作用：

事务回滚：记录数据修改前的状态，事务失败时通过 undo log 恢复到修改前（原子性）。
MVCC 支持：为读操作提供多版本数据（非锁定读），避免读写冲突（一致性）。

工作原理：

数据修改时，undo log 记录逻辑反向操作（如 INSERT 对应 DELETE，UPDATE 对应反向 UPDATE）；
事务提交后，undo log 不会立即删除，而是放入待清理链表，由 purge 线程判断是否有其他事务依赖旧版本数据，无依赖则释放空间。

undo log 记录的是逻辑日志（如 “删除 id=123 的行”），与具体数据修改对应。

配置：

存储位置：MySQL 5.7+ 支持独立表空间，配置如下：

# undo 日志目录
innodb_undo_directory = /data/undospace/  
# 回滚段数量（默认 128）
innodb_undo_logs = 128  
# 独立表空间文件数量（默认 4）
innodb_undo_tablespaces = 4  

特点：

会产生 redo log：undo log 本身的修改需要 redo log 保障持久性；
与事务绑定：每个事务有独立的 undo log 段；
支持 MVCC：通过 undo log 链提供数据的多个版本，实现 “读不加锁”。

二进制日志（binlog）：主从复制与数据恢复

binlog 是 MySQL 服务器级别的日志（所有引擎通用），记录所有数据变更操作（INSERT/UPDATE/DELETE、CREATE 等），不记录查询（SELECT）。

核心作用：

主从复制：从库通过解析主库的 binlog 同步数据；
数据恢复：通过回放 binlog 实现基于时间点的恢复；
审计追踪：记录所有变更操作的细节（时间、用户、语句）。

工作原理：

事务提交时，将整个事务的变更记录写入 binlog（内存缓冲 → 磁盘文件）；
采用追加写（append-only），文件满后自动轮转（生成新文件，如 mysql-bin.000001、mysql-bin.000002）。

binlog 记录的是逻辑日志（可配置为 SQL 语句或行变更）。

日志格式（`binlog_format`）：

binlog 支持三种格式，各有优劣：

格式	含义	优点	缺点
Statement	记录原始 SQL 语句	日志量小，IO 效率高	部分函数（如 `UUID()`）无法正确复制，主从可能不一致
Row（默认）	记录每行数据的变更	主从一致性强，无函数依赖问题	日志量大（尤其是批量操作），IO 压力高
Mixed	自动选择 Statement 或 Row	平衡日志量与一致性	复杂度高，依赖 MySQL 优化器判断

Statement

5.7.7之前的默认值

基于sql语句的记录，每一条会修改数据的sql都会记录在binlog中，记录的是原始的sql语句

优点： 不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该根据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql的复制，像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及用户自定义的 functions(udf)会出现问题)
使用以下函数的语句也无法被复制：

LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除非启动时启用了 —sysdate-is-now 选项)

Row

5.7.7之后的默认值

基于行的记录，不记录sql语句上下文相关信息，仅保存哪条记录被修改，记录的是原始数据

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中

Mixed

以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更

关键配置：

# 开启 binlog（日志文件名前缀）
log_bin = mysql-bin  
# 服务器唯一 ID（主从复制必需）
server-id = 1  
# 日志格式（默认 Row）
binlog_format = ROW  
# 刷盘策略（默认 1，最安全）
sync_binlog = 1  
# 日志过期时间（默认 30 天）
expire_logs_days = 7  
# 单个日志文件最大大小（默认 1G）
max_binlog_size = 1G  

sync_binlog取值说明：
- 0：由 OS 决定刷盘时机（性能高，可能丢失数据）；
- 1：事务提交时立即刷盘（最安全，性能略低）；
- N：每 N 个事务刷盘（平衡安全与性能）。

查看与管理：

-- 查看所有 binlog 文件
show binary logs;  

-- 查看当前正在写入的 binlog
show master status;  

-- 查看指定 binlog 内容（SQL 命令）
show binlog events in 'mysql-bin.000001';  

-- 通过 mysqlbinlog 工具解析（shell 命令）
mysqlbinlog --start-datetime='2024-01-01 00:00:00' /var/lib/mysql/mysql-bin.000001

中继日志（relay log）：主从复制的 “缓冲器”

中继日志是从库（Slave）特有的日志，用于临时存储主库（Master）发送的 binlog 事件，是主从复制的中间环节。

工作原理：

从库的 I/O 线程 接收主库的 binlog 事件，写入中继日志；
从库的 SQL 线程 读取中继日志，解析并执行 SQL 语句，同步数据；
中继日志格式与 binlog 一致，同步完成后会被自动清理。

配置与查看：

默认存储：从库数据目录下，以 relay-bin.000001 命名，由 relay-log.index 管理。

查看命令：

-- 查看中继日志内容
show relaylog events;  

-- 查看从库复制状态（含中继日志信息）
show slave status \G;  

慢查询日志：定位低效 SQL

慢查询日志记录执行时间超过阈值（long_query_time）的 SQL 语句，用于识别性能瓶颈。

核心配置：

# 开启慢查询日志
slow_query_log = 1  
# 日志文件路径
slow_query_log_file = /var/log/mysql/slow.log  
# 超时阈值（默认 10 秒，可设小数）
long_query_time = 2  
# 记录未使用索引的 SQL（即使未超时）
log_queries_not_using_indexes = 1  

分析工具：

mysqldumpslow：汇总相似慢查询，按执行次数、时间排序；
pt-query-digest：更详细的分析，支持输出执行计划、耗时分布等。

一般查询日志（general log）：记录所有操作

一般查询日志记录所有客户端连接和执行的 SQL 语句（包括查询），默认关闭（因日志量极大，影响性能）。

配置：

# 开启一般查询日志（谨慎使用）
general_log = 1  
# 日志文件路径
general_log_file = /var/log/mysql/general.log  
# 输出格式（FILE 或 TABLE）
log_output = FILE  

错误日志（error log）：数据库的 “日记”

错误日志记录 MySQL 服务器的启动、运行、关闭过程，包括错误、警告和通知信息，默认开启，是故障排查的首要依据。

配置与查看：

# 错误日志路径
log_error = /var/log/mysql/error.log  
# 日志详细程度（1=错误，2=错误+警告，3=全部，默认 3）
log_error_verbosity = 3  

查看方式：直接读取日志文件（文本格式），或通过 MySQL 命令查看最近错误。

日志协同工作：以 INSERT 语句为例

当执行 INSERT 语句时，各日志的触发顺序如下：

undo log：记录插入前的反向操作（DELETE），用于回滚或 MVCC；
redo log：记录数据页的物理变更（写入 redo log buffer）；
事务提交：
- 按 innodb_flush_log_at_trx_commit 配置刷写 redo log 到磁盘；
- 写入 binlog（按 sync_binlog 配置刷盘）；
- 标记 redo log 为 “已提交”；
若开启主从复制，主库的 binlog 会被发送到从库，写入中继日志，最终由从库 SQL 线程执行。

MySQL 日志系统全解析：类型、作用与实战配置

事务日志：保障事务的 ACID 特性

1. 重做日志（redo log）：保证持久性与原子性

核心作用：

工作原理：

存储与配置：

特点：

2. 回滚日志（undo log）：保证一致性与 MVCC

核心作用：

工作原理：

配置：

特点：

二进制日志（binlog）：主从复制与数据恢复

核心作用：

工作原理：

日志格式（binlog_format）：

Statement

Row

Mixed

关键配置：

查看与管理：

中继日志（relay log）：主从复制的 “缓冲器”

工作原理：

配置与查看：

慢查询日志：定位低效 SQL

核心配置：

分析工具：

一般查询日志（general log）：记录所有操作

配置：

错误日志（error log）：数据库的 “日记”

配置与查看：

日志协同工作：以 INSERT 语句为例

日志格式（`binlog_format`）：