📚 本文收录于「bksczm」的系列专栏
 Linux |  C/C++ |
 MySQL |  面试题 |
 Redis | |
一、MySQL 日志体系总览
1.1 为什么需要日志
日志用于记录服务器正在发生的活动，是数据安全、崩溃恢复、主从复制、故障排查的基础。MySQL 中常见的日志有七类：
| 日志 | 作用 | 所属层 |
|---|---|---|
| redo log | 事务提交后崩溃恢复，保证持久性（DML 物理修改） | InnoDB 引擎层 |
| undo log | 事务回滚 + MVCC 多版本并发控制（旧版本链） | InnoDB 引擎层 |
| error log | 记录 mysqld 启动、运行、停止时的错误与告警 | Server 层 |
| general query log | 记录所有客户端连接和收到的语句（调试用，默认关闭） | Server 层 |
| slow query log | 记录执行时间超过 long_query_time 的慢查询 | Server 层 |
| binary log（binlog） | 记录所有对数据的更改，用于复制和恢复 | Server 层 |
| relay log（中继日志） | 从库接收主库 binlog 后落地的日志，供 SQL 线程重放 | Server 层（从库） |
1.2 必须先建立的分层概念（重点）
这是理解 redo log 和 binlog 的前提：
- redo log / undo log 是 InnoDB 存储引擎层的日志，只有使用 InnoDB 才有；MyISAM 没有崩溃恢复能力正是因为没有 redo log。
- binlog 是 MySQL Server 层的日志，与存储引擎无关，任何引擎对数据的更改都会记录 binlog。
一条更新语句在 InnoDB 中会同时产生 redo log（引擎层）和 binlog（Server 层），二者通过两阶段提交协调（见第七章）。
二、binlog 是什么
2.1 概念与特点
二进制日志（binary log，binlog）以"事件（event）"的形式，记录所有导致（或可能导致）数据变化的操作：
- 记录：DDL（CREATE/ALTER/DROP）、DML（INSERT/UPDATE/DELETE）；
- 不记录：DQL（SELECT/SHOW 等纯查询，因为不改变数据）。
binlog 的三个本质特征：
- 逻辑日志：记录的是"执行了什么语句"或"哪些行从什么值变成什么值"，而不是数据页的物理字节变化（对比 redo log 是物理日志）；
- 追加写：日志文件按编号递增（binlog.000001、000002……），写满一个切下一个，不会循环覆盖旧内容（对比 redo log 是固定大小循环写）；
- 二进制格式：有自己严格定义的二进制事件结构，不是纯文本，需要用
mysqlbinlog工具解码查看。（并非没有编码的裸数据）
2.2 binlog 与 redo log 的区别（高频面试题）
| 对比维度 | redo log | binlog |
|---|---|---|
| 所属层 | InnoDB 引擎层 | MySQL Server 层（所有引擎通用） |
| 日志类型 | 物理日志：某数据页某偏移做了什么修改 | 逻辑日志：语句或行变更逻辑 |
| 写入方式 | 循环写，固定文件大小，写满回头覆盖 | 追加写，文件不断切换，不覆盖 |
| 主要用途 | 崩溃恢复（crash recovery），保证持久性 | 主从复制、数据恢复（point-in-time recovery） |
| 记录内容 | 事务执行过程中持续产生 | 事务提交时一次性写入 |
打个比方：redo log 像一块循环使用的"应急小黑板"，只关心数据库崩溃后能把已提交事务恢复回来；binlog 像一本不断追加的"操作流水账"，可以随时翻出来重放或给从库照抄。
2.3 binlog 的两大用途
- 主从复制：主库把 binlog 传给从库，从库重放得到相同数据（见第九章）；
- 数据恢复：备份恢复 + 重放 binlog，可以把数据库恢复到任意时间点/任意位置。
三、binlog 的开启与相关变量
3.1 查看相关变量
SHOW VARIABLES LIKE '%log_bin%';
| 变量 | 含义 |
|---|---|
log_bin | 是否开启 binlog（MySQL 8.0 默认 ON；5.7 默认 OFF，需手动开启） |
log_bin_basename | binlog 文件的存储路径和文件名前缀，如 /var/lib/mysql/binlog |
log_bin_index | binlog 索引文件路径（binlog.index），维护当前有哪些 binlog 文件 |
log_bin_trust_function_creators | 见下方说明 |
sql_log_bin | 当前会话是否写 binlog，默认 ON |
log_bin_use_v1_row_events | 已弃用 |
关于 log_bin_trust_function_creators ：开启 binlog 后，MySQL 默认会限制创建存储函数/触发器，要求函数必须声明为 DETERMINISTIC 或 READS SQL DATA，因为不确定函数（含 RAND()、NOW() 等）在主从上重放可能产生不一致结果。把它设为 ON 表示"信任创建者"，跳过这一限制检查。
3.2 log_bin 与 sql_log_bin 的辨析
| 变量 | 作用范围 | 修改方式 |
|---|---|---|
log_bin | 全局，决定整个实例是否开启 binlog | 只读变量，只能在配置文件或启动参数 --log-bin 中设置，不能运行时 SET 修改 |
sql_log_bin | 会话级，决定当前会话的操作是否写入 binlog | 可在会话中动态 SET sql_log_bin = OFF;（需较高权限，不能在事务中设置） |
典型用途：数据量大的一次性批量导入，临时 SET sql_log_bin=OFF 可以避免产生海量 binlog（但要自行承担该操作不参与复制/无法通过 binlog 恢复的后果）。
3.3 在配置文件中开启
Ubuntu 22.04 下入口配置是 /etc/mysql/my.cnf，它通常再引入 /etc/mysql/mysql.conf.d/mysqld.cnf，实际改动一般在后者的 [mysqld] 组下：
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=30
主从复制场景中
server-id必须配置且全集群唯一。修改后重启 mysqld 生效。
四、binlog 的文件组织与切换
4.1 磁盘文件
按 log_bin_basename + 递增编号生成：
/var/lib/mysql/binlog.000001
/var/lib/mysql/binlog.000002
...
/var/lib/mysql/binlog.index # 文本索引文件，记录所有 binlog 文件名
查看已有 binlog 文件及大小：
SHOW BINARY LOGS;
单个文件的容量上限：
SELECT @@max_binlog_size; -- 默认 1073741824 字节 = 1GB
4.2 生成新 binlog 文件（日志切换）的时机
- MySQL 服务器重启；
- 手动刷新：
FLUSH LOGS; -- 刷新所有日志 FLUSH BINARY LOGS; -- 只切换 binlog：当前文件封盘，新建下一个编号文件 - 当前文件大小达到
max_binlog_size。
4.3 大事务不会被拆到两个文件
如果一个事务产生的日志让文件超过了 max_binlog_size，整个事务仍然完整写在当前文件中，不会跨文件拆分——因为事务必须保证完整性，恢复/复制时一个事务要能完整重放。因此实际文件可能略微超过 1GB，超大事务甚至会让单个 binlog 文件远大于上限。
五、binlog 的删除策略
不要直接在 shell 里
rmbinlog 文件，否则会造成binlog.index索引与磁盘文件不一致，引发报错。应使用 MySQL 提供的命令。
5.1 过期自动删除
由 binlog_expire_logs_seconds 控制保留时长（MySQL 8.0 变量，单位秒）：
SELECT @@global.binlog_expire_logs_seconds; -- 默认 2592000 秒 = 30 天
5.7 版本使用
expire_logs_days（单位天）。超过保留期的 binlog 会被自动清理。
5.2 手动删除
-- 1）删除全部 binlog，编号重新从 .000001 开始（危险，主从环境慎用）
RESET MASTER;
-- 2）删除指定文件之前的所有文件：删除 binlog.000001 ~ 000004，保留 000005 及之后
PURGE BINARY LOGS TO 'binlog.000005';
-- 3）删除指定时间之前的所有文件
PURGE BINARY LOGS BEFORE '2026-10-12 21:55:59';
六、binlog 的三种记录格式
由 binlog_format 控制：
SELECT @@global.binlog_format; -- MySQL 5.7+ 默认 ROW
6.1 STATEMENT（语句格式）
原样记录每一条 SQL 语句。
- 优点：日志紧凑、体积小，可读；
- 缺点：不确定函数会导致主从不一致。例如记录的是
INSERT ... VALUES(NOW())，从库在另一个时间重放时NOW()取到的是从库当前时间，而不是主库执行时的那个时间；UUID()、RAND()、USER()同理。
6.2 ROW（行格式，默认推荐）
记录表中每一行数据的变更（变更前的旧镜像 before-image 和变更后的新镜像 after-image），不再依赖 SQL 上下文。
- 优点：记录的是确定的数据结果，任何函数、复杂语句都能正确复制，主从一致性最有保障；
- 缺点：一条
UPDATE ... WHERE改了 100 万行，就要记录 100 万行的变更，日志体积大。
6.3 MIXED（混合格式）
MySQL 自动判断：普通确定性语句用 STATEMENT 记录，遇到不确定函数/语句自动切换为 ROW，兼顾体积和安全。
6.4 三种格式对比
| 格式 | 记录内容 | 日志体积 | 一致性 | 适用 |
|---|---|---|---|---|
| STATEMENT | 原始 SQL | 小 | 不确定函数有风险 | 简单确定语句 |
| ROW | 每行变更镜像 | 大 | 最可靠 | 默认、生产推荐 |
| MIXED | 自动选择 | 居中 | 较可靠 | 折中场景 |
七、binlog 刷盘策略与两阶段提交
7.1 数据落盘的三级路径
很多人把 "写 binlog" 理解成一步到位写进磁盘文件，实际上数据从产生到真正持久化，要经过两级缓冲、两次系统调用，跨越三个位置：
事务执行中不断产生 binlog 事件
│
▼
┌───────────────────────────────────┐
│ ① 线程私有 binlog cache │ MySQL 进程的「用户态内存」
│ （由 binlog_cache_size 控制) │ 每个会话线程一个，暂存本事务的事件
└─────────────────┬─────────────────┘
│ 事务提交时调用 write()：数据从用户态拷贝到内核态
▼
┌───────────────────────────────────┐
│ ② OS Page Cache（页缓存） │ 操作系统「内核态内存」
│ binlog 文件对应的脏页 │ 此时逻辑上文件已有数据，但仍在内存
└─────────────────┬─────────────────┘
│ 调用 fsync()/fdatasync()：强制把脏页刷到物理设备
▼
┌───────────────────────────────────┐
│ ③ 物理磁盘 │ 真正持久化，断电不丢
│ binlog.0000xx 的磁盘扇区 │
└───────────────────────────────────┘
三级位置分别是：
- 线程私有 binlog cache：事务执行期间，产生的 binlog 事件先暂存在会话线程（服务器）私有的内存缓存中（大小由
binlog_cache_size控制），事务提交时整段写出，保证一个事务的 binlog 连续完整； - OS Page Cache（页缓存）：调用
write()后，数据从 MySQL 用户态内存拷贝到操作系统内核的页缓存； - 物理磁盘：调用
fsync()后，页缓存中的脏页才被强制写入磁盘扇区。
7.2 write () 与 fsync () 的区别（核心）
这是理解一切 "刷盘参数" 的关键，二者绝不能混为一谈：
| 系统调用 | 数据移动 | 返回后断电是否丢数据 |
|---|---|---|
write() | ① 用户态内存 → ② 内核 Page Cache | 会丢，数据还停留在内存 |
fsync() | ② Page Cache → ③ 物理磁盘 | 不丢（磁盘自带写缓存且无掉电保护 BBU 的硬件问题除外） |
执行完 write() 后，程序 "读文件" 已经能读到这些数据（因为读文件同样经过 Page Cache），文件逻辑大小也增长了，但物理字节可能还没写进磁盘；只有 fsync() 等磁盘确认写完才返回，才算真正落盘。
7.3 sync_binlog 的三个取值
sync_binlog 控制的是什么时候做 fsync（而不是 write）：
| 取值 | 提交时的动作 | 崩溃语义 |
|---|---|---|
| 0 | 只 write() 到 Page Cache，不主动 fsync，刷盘时机交给 OS（Linux 约 30 秒脏页刷盘周期） | mysqld 进程崩溃不丢（Page Cache 由内核管理，进程死了内核还在）；OS 崩溃 / 断电会丢未落盘部分 |
| 1 | 每次事务提交都 write() + fsync()，一步到第③级 | 最安全，fsync 成功后断电也不丢；性能损耗最大 |
| N（N>1） | 每提交 N 个事务才 fsync() 一次，中间事务只停在第②级 | 折中，OS 崩溃 / 断电时最多丢失约 N 个事务的 binlog |
⚠️ 注意区分两种 "崩溃"：
mysqld 进程崩溃但操作系统正常时，Page Cache 中的数据仍然存在、不会丢；
只有操作系统崩溃或机器断电导致内核内存失效时，未 fsync 的数据才会丢。笼统地说 "MySQL 崩溃 binlog 全丢" 是不准确的。
redo log 走的是完全相同的两级模型，可对照记忆：
redo log buffer（InnoDB 用户态内存）──write()──▶ OS Page Cache ──fsync()──▶ 物理磁盘
innodb_flush_log_at_trx_commit：=0 提交时不主动刷、后台每秒刷（进程崩溃也可能丢约 1 秒）；=1 每次提交 write+fsync（最严格）；=2 每次提交只 write 到 Page Cache、每秒 fsync（进程崩溃不丢，断电 / OS 崩溃丢约 1 秒）。
7.4 "双 1" 配置
生产环境最严格的持久化组合，保证 redo log 和 binlog 在每次事务提交时都真正落盘：
[mysqld]
innodb_flush_log_at_trx_commit = 1 # redo log 每次提交 fsync
sync_binlog = 1 # binlog 每次提交 fsync
7.5 redo log 与 binlog 的两阶段提交（2PC）
一条更新语句在 InnoDB 中会同时产生 redo log（引擎层）和 binlog（Server 层）。为了让两份日志在崩溃时仍保持一致，InnoDB 采用内部两阶段提交：
1. prepare 阶段：写 redo log，将事务标记为 PREPARE 状态
2. 写 binlog：把整段事务事件 write 到 binlog 并 fsync 落盘
3. commit 阶段：在 redo log 中写入 COMMIT 标记，事务真正提交
崩溃恢复时按以下规则判定：
- redo log 中已是 COMMIT 状态 → 提交该事务；
- redo log 中只有 PREPARE：
- 对应事务的 binlog 已完整 fsync 落盘 → 仍然提交（保证从库也能收到该事务）；
- binlog 不完整或不存在 → 回滚该事务。
这样就保证了 "binlog 里有的事务，主库一定提交；主库提交的事务，binlog 一定完整"，主库与从库不会因崩溃而不一致
八、mysqlbinlog 工具
binlog 是二进制文件，mysqlbinlog 是官方解码工具（随 MySQL 客户端安装）。
8.1 事件内容解读
mysqlbinlog --no-defaults binlog.000010
典型输出：
# at 3470
#241021 16:38:31 server id 1 end_log_pos 3547 CRC32 0x3bda21ce Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1729499911/*!*/;
BEGIN
| 字段 | 含义 |
|---|---|
# at 3470 | 该事件在文件中的起始字节偏移量（position） |
241021 16:38:31 | 事件发生时间戳 |
server id 1 | 产生该事件的服务器 ID（主从区分用） |
end_log_pos 3547 | 下一个事件的起始偏移（当前事件结束位置 +1） |
Query | 事件类型（如 Query/Write_rows/Xid 等） |
thread_id=8 | 执行该事件的客户端线程 ID |
exec_time=0 | 主库上事件执行耗时（0 表示极快） |
error_code=0 | 错误码，0 表示无错误 |
8.2 常用选项
| 选项 | 作用 |
|---|---|
--database=db名 | 只查看指定数据库的事件 |
--start-datetime / --stop-datetime | 按时间范围读取 |
--start-position=N / --stop-position=N | 按字节位置范围读取（定点恢复用） |
--base64-output=DECODE-ROWS | 把 ROW 行事件解码（配合 -v 使用） |
-v / --verbose | 把行事件重建为注释形式的 SQL（### INSERT ...） |
-r 文件名 | 输出到指定文件 |
-R / --read-from-remote-server | 从远程服务器读取 binlog 而非本地文件 |
--server-id=N | 只显示指定 server-id 产生的事件 |
--skip-gtids | 输出时不包含 GTID 信息 |
8.3 查看 ROW 格式内容（纠正"加密"说法）
mysqlbinlog --no-defaults --database=testdb \
--base64-output=DECODE-ROWS -v binlog.000001 | less
解码后能看到形如：
### INSERT INTO `testdb`.`student`
### SET
### @1=1
### @2='张三'
### @3=18
8.4 用 binlog 做定点数据恢复（实操）
先用备份恢复到某个基线，再重放 binlog 中指定区间，跳过误操作区间：
# 按位置恢复（恢复 position 1000 ~ 5000 之间的操作）
mysqlbinlog --no-defaults \
--start-position=1000 --stop-position=5000 \
binlog.000010 | mysql -u root -p
# 按时间恢复
mysqlbinlog --no-defaults \
--start-datetime="2026-10-12 20:00:00" \
--stop-datetime="2026-10-12 21:00:00" \
binlog.000010 | mysql -u root -p
这就是 PITR（Point-In-Time Recovery，时间点恢复）。
九、主从复制与 relay log
binlog 是主从复制的核心，整体流程涉及三个线程：
主库（Master） 从库（Slave）
┌──────────────┐ ① binlog 事件 ┌────────────────────┐
│ Binlog Dump │ ───────────────────▶ │ ② IO 线程 │
│ 线程 │ （TCP 网络传输） │ 写入 relay log │
└──────────────┘ └─────────┬──────────┘
│ ③ SQL 线程读取并重放
▼
从库数据与主库一致
- 从库 IO 线程连接主库，请求拉取 binlog；
- 主库 Binlog Dump 线程读取 binlog 事件发给从库；
- 从库 IO 线程把收到的事件写入本地的 relay log（中继日志）；
- 从库 SQL 线程读取 relay log 并重放，使数据与主库一致。
relay log 格式与 binlog 几乎相同，区别在于它是从库"中转暂存"主库变更的日志。MySQL 5.7+ 支持基于组提交的多线程复制（MTS）提升重放速度；开启 GTID 后可免去手工指定 binlog 文件和位置。
转载自 CSDN-专业IT技术社区
