MySQL进阶篇之日志(上半篇)

作者:bksczm日期:2026/10/17

📚 本文收录于「bksczm」的系列专栏

LinuxC/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 的三个本质特征:

  1. 逻辑日志:记录的是"执行了什么语句"或"哪些行从什么值变成什么值",而不是数据页的物理字节变化(对比 redo log 是物理日志);
  2. 追加写:日志文件按编号递增(binlog.000001、000002……),写满一个切下一个,不会循环覆盖旧内容(对比 redo log 是固定大小循环写);
  3. 二进制格式:有自己严格定义的二进制事件结构,不是纯文本,需要用 mysqlbinlog 工具解码查看。(并非没有编码的裸数据)

2.2 binlog 与 redo log 的区别(高频面试题)

对比维度redo logbinlog
所属层InnoDB 引擎层MySQL Server 层(所有引擎通用)
日志类型物理日志:某数据页某偏移做了什么修改逻辑日志:语句或行变更逻辑
写入方式循环写,固定文件大小,写满回头覆盖追加写,文件不断切换,不覆盖
主要用途崩溃恢复(crash recovery),保证持久性主从复制、数据恢复(point-in-time recovery)
记录内容事务执行过程中持续产生事务提交时一次性写入

打个比方:redo log 像一块循环使用的"应急小黑板",只关心数据库崩溃后能把已提交事务恢复回来;binlog 像一本不断追加的"操作流水账",可以随时翻出来重放或给从库照抄。

2.3 binlog 的两大用途

  1. 主从复制:主库把 binlog 传给从库,从库重放得到相同数据(见第九章);
  2. 数据恢复:备份恢复 + 重放 binlog,可以把数据库恢复到任意时间点/任意位置。

三、binlog 的开启与相关变量

3.1 查看相关变量

1SHOW VARIABLES LIKE '%log_bin%';
2
变量含义
log_bin是否开启 binlog(MySQL 8.0 默认 ON;5.7 默认 OFF,需手动开启)
log_bin_basenamebinlog 文件的存储路径和文件名前缀,如 /var/lib/mysql/binlog
log_bin_indexbinlog 索引文件路径(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] 组下:

1[mysqld]
2server-id=1
3log-bin=mysql-bin
4binlog_format=ROW
5expire_logs_days=30
6

主从复制场景中 server-id 必须配置且全集群唯一。修改后重启 mysqld 生效。


四、binlog 的文件组织与切换

4.1 磁盘文件

按 log_bin_basename + 递增编号生成:

1/var/lib/mysql/binlog.000001
2/var/lib/mysql/binlog.000002
3...
4/var/lib/mysql/binlog.index      # 文本索引文件,记录所有 binlog 文件名
5

查看已有 binlog 文件及大小:

1SHOW BINARY LOGS;
2

单个文件的容量上限:

1SELECT @@max_binlog_size;   -- 默认 1073741824 字节 = 1GB
2

4.2 生成新 binlog 文件(日志切换)的时机

  1. MySQL 服务器重启;
  2. 手动刷新:
1FLUSH LOGS;            -- 刷新所有日志  
2FLUSH BINARY LOGS;     -- 只切换 binlog:当前文件封盘,新建下一个编号文件  
  1. 当前文件大小达到 max_binlog_size。

4.3 大事务不会被拆到两个文件

如果一个事务产生的日志让文件超过了 max_binlog_size,整个事务仍然完整写在当前文件中,不会跨文件拆分——因为事务必须保证完整性,恢复/复制时一个事务要能完整重放。因此实际文件可能略微超过 1GB,超大事务甚至会让单个 binlog 文件远大于上限。


五、binlog 的删除策略

不要直接在 shell 里 rm binlog 文件,否则会造成 binlog.index 索引与磁盘文件不一致,引发报错。应使用 MySQL 提供的命令。

5.1 过期自动删除

由 binlog_expire_logs_seconds 控制保留时长(MySQL 8.0 变量,单位秒):

1SELECT @@global.binlog_expire_logs_seconds;  -- 默认 2592000 秒 = 30 天
2

5.7 版本使用 expire_logs_days(单位天)。超过保留期的 binlog 会被自动清理。

5.2 手动删除

1-- 1)删除全部 binlog,编号重新从 .000001 开始(危险,主从环境慎用)
2RESET MASTER;
3
4-- 2)删除指定文件之前的所有文件:删除 binlog.000001 ~ 000004,保留 000005 及之后
5PURGE BINARY LOGS TO 'binlog.000005';
6
7-- 3)删除指定时间之前的所有文件
8PURGE BINARY LOGS BEFORE '2026-10-12 21:55:59';
9

六、binlog 的三种记录格式

由 binlog_format 控制:

1SELECT @@global.binlog_format;   -- MySQL 5.7+ 默认 ROW
2

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" 理解成一步到位写进磁盘文件,实际上数据从产生到真正持久化,要经过两级缓冲、两次系统调用,跨越三个位置:

1        事务执行中不断产生 binlog 事件
2                    │
3                    ▼
4┌───────────────────────────────────┐
5│ ① 线程私有 binlog cache            │  MySQL 进程的「用户态内存」
6│   (由 binlog_cache_size 控制)     │  每个会话线程一个,暂存本事务的事件
7└─────────────────┬─────────────────┘
8                  │ 事务提交时调用 write():数据从用户态拷贝到内核态
9                  ▼
10┌───────────────────────────────────┐
11│ ② OS Page Cache(页缓存)          │  操作系统「内核态内存」
12│   binlog 文件对应的脏页            │  此时逻辑上文件已有数据,但仍在内存
13└─────────────────┬─────────────────┘
14                  │ 调用 fsync()/fdatasync():强制把脏页刷到物理设备
15                  ▼
16┌───────────────────────────────────┐
17│ ③ 物理磁盘                         │  真正持久化,断电不丢
18│   binlog.0000xx 的磁盘扇区          │
19└───────────────────────────────────┘
20

三级位置分别是:

  1. 线程私有 binlog cache:事务执行期间,产生的 binlog 事件先暂存在会话线程(服务器)私有的内存缓存中(大小由 binlog_cache_size 控制),事务提交时整段写出,保证一个事务的 binlog 连续完整;
  2. OS Page Cache(页缓存):调用 write() 后,数据从 MySQL 用户态内存拷贝到操作系统内核的页缓存;
  3. 物理磁盘:调用 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 走的是完全相同的两级模型,可对照记忆:

1redo log buffer(InnoDB 用户态内存)──write()──▶ OS Page Cache ──fsync()──▶ 物理磁盘
2

innodb_flush_log_at_trx_commit:=0 提交时不主动刷、后台每秒刷(进程崩溃也可能丢约 1 秒);=1 每次提交 write+fsync(最严格);=2 每次提交只 write 到 Page Cache、每秒 fsync(进程崩溃不丢,断电 / OS 崩溃丢约 1 秒)。

7.4 "双 1" 配置

生产环境最严格的持久化组合,保证 redo log 和 binlog 在每次事务提交时都真正落盘:

1[mysqld]
2innodb_flush_log_at_trx_commit = 1   # redo log 每次提交 fsync
3sync_binlog = 1                      # binlog 每次提交 fsync
4

7.5 redo log 与 binlog 的两阶段提交(2PC)

一条更新语句在 InnoDB 中会同时产生 redo log(引擎层)和 binlog(Server 层)。为了让两份日志在崩溃时仍保持一致,InnoDB 采用内部两阶段提交:

11. prepare 阶段:写 redo log,将事务标记为 PREPARE 状态
22. 写 binlog:把整段事务事件 write 到 binlog 并 fsync 落盘
33. commit 阶段:在 redo log 中写入 COMMIT 标记,事务真正提交
4

崩溃恢复时按以下规则判定:

  • redo log 中已是 COMMIT 状态 → 提交该事务;
  • redo log 中只有 PREPARE:
    • 对应事务的 binlog 已完整 fsync 落盘 → 仍然提交(保证从库也能收到该事务);
    • binlog 不完整或不存在 → 回滚该事务。

这样就保证了 "binlog 里有的事务,主库一定提交;主库提交的事务,binlog 一定完整",主库与从库不会因崩溃而不一致


八、mysqlbinlog 工具

binlog 是二进制文件,mysqlbinlog 是官方解码工具(随 MySQL 客户端安装)。

8.1 事件内容解读

1mysqlbinlog --no-defaults binlog.000010
2

典型输出:

1# at 3470
2#241021 16:38:31 server id 1  end_log_pos 3547 CRC32 0x3bda21ce Query  thread_id=8 exec_time=0 error_code=0
3SET TIMESTAMP=1729499911/*!*/;
4BEGIN
5
字段含义
# 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 格式内容(纠正"加密"说法)

1mysqlbinlog --no-defaults --database=testdb \
2  --base64-output=DECODE-ROWS -v binlog.000001 | less
3

解码后能看到形如:

1### INSERT INTO `testdb`.`student`
2### SET
3###   @1=1
4###   @2='张三'
5###   @3=18
6

8.4 用 binlog 做定点数据恢复(实操)

先用备份恢复到某个基线,再重放 binlog 中指定区间,跳过误操作区间:

1# 按位置恢复(恢复 position 1000 ~ 5000 之间的操作)
2mysqlbinlog --no-defaults \
3  --start-position=1000 --stop-position=5000 \
4  binlog.000010 | mysql -u root -p
5
6# 按时间恢复
7mysqlbinlog --no-defaults \
8  --start-datetime="2026-10-12 20:00:00" \
9  --stop-datetime="2026-10-12 21:00:00" \
10  binlog.000010 | mysql -u root -p
11

这就是 PITR(Point-In-Time Recovery,时间点恢复)。


九、主从复制与 relay log

binlog 是主从复制的核心,整体流程涉及三个线程:

1主库(Master)                          从库(Slave)
2┌──────────────┐    ① binlog 事件      ┌────────────────────┐
3│ Binlog Dump  │ ───────────────────▶ │ ② IO 线程          │
4│ 线程         │   (TCP 网络传输)     │    写入 relay log  │
5└──────────────┘                       └─────────┬──────────┘
6                                                 │ ③ SQL 线程读取并重放
7                                                 ▼
8                                          从库数据与主库一致
9
  1. 从库 IO 线程连接主库,请求拉取 binlog;
  2. 主库 Binlog Dump 线程读取 binlog 事件发给从库;
  3. 从库 IO 线程把收到的事件写入本地的 relay log(中继日志);
  4. 从库 SQL 线程读取 relay log 并重放,使数据与主库一致。

relay log 格式与 binlog 几乎相同,区别在于它是从库"中转暂存"主库变更的日志。MySQL 5.7+ 支持基于组提交的多线程复制(MTS)提升重放速度;开启 GTID 后可免去手工指定 binlog 文件和位置。


参考文档


《MySQL进阶篇之日志(上半篇)》 是转载文章,点击查看原文。


相关推荐


多 Agent 协作架构:Pipeline 模式——串行流水线设计与实战
七夜zippoe2026/10/8

摘要:当单个 Agent 无法独立完成复杂任务时,多 Agent 协作成为必然选择。在众多协作范式中,Pipeline(串行流水线)模式是最简单、最直观、也最容易踩坑的一种——它把一个大任务拆成多个有先后依赖的子任务,让每个 Agent 只专注一个环节,前一个 Agent 的输出成为后一个 Agent 的输入。本文从第一性原理出发,系统拆解 Pipeline 模式的任务分解、串行编排与中间态传递三大核心问题,对比 LangGraph、LangChain LCEL 与自研轻量编排三种实现路径,并给


Prometheus跨主机监控实战:Node Exporter部署、CPU内存告警与cpolar远程采集
码农阿豪@新空间2026/9/30

Prometheus跨主机监控实战:Node Exporter部署、CPU内存告警与cpolar远程采集 前言 Prometheus 采用定期抓取指标的方式采集目标状态。对 Linux 主机而言,Node Exporter 可以把 CPU、内存、文件系统和网络等指标暴露在 HTTP /metrics 端点。实际运维中,Prometheus 和被监控主机往往分开部署;当两台主机处于同一个可路由网络时,直接配置被监控端 IP 与 9100 端口即可。但如果目标设备位于另一处内网,监控中心不能直接到达


从 0 到 1 手搓 AI 视觉识别平台前端:1,286 路视频、6 大模块、纯 HTML/CSS/JS 实现(附完整思路与代码)
合抱阴阳2026/9/24

摘要:算法能跑通,只完成了 AI 视觉项目的 30%;剩下 70% 是「让人看得懂、用得上」。本文用一个可运行的单文件原型,拆解 AI 视觉识别平台的 6 大核心模块——态势总览、实时预览、事件中心、事件详情与处置、证据档案、检索分析,覆盖 KPI 体系、九宫格预览与检测框叠加、事件处置漏斗、合规化证据归档,以及以文搜图的多模态检索。全文零框架依赖,一个 index.html 双击即开,所有图表用 CSS/SVG 手绘,代码可直接抄进你的项目。 关键词:AI 视觉 · 视频结构化 · 前端


06-并发
前端兰博2026/9/16

一、线程创建 Java中常见创建线程有三种,常见的如下两类 1.1 继承Threa类 public class MyThread extends Thread{ @Override public void run(){ System.out.println("MyThread is running"); } } public static void main(String[] args) { MyThread myThread = new MyT


当 Mac mini 的价格不再 mini -- 肘子的 Swift 周报 #152
东坡肘子2026/9/8

当 Mac mini 的价格不再 mini 上月底,苹果发布了新的 Mac mini 和 Mac Studio。除了强悍的性能更新,明显上涨的价格同样给人带来了不小的震撼。内存与存储成本上涨无疑是本轮涨价的重要原因,但在 AI 时代,开发者对硬件的需求也在悄然发生变化。更大的内存、更高的存储容量,不断推高我们心目中“够用配置”的标准,最终也让实际购买价格变得更加夸张。 过去几个月,开发中最让我头痛的一件事,是 MacBook Pro 风扇持续转动带来的噪音。过去多年的使用中,只有在少数高负载场景


Eino + DeepFlux 实战全景复盘:100 篇,从 5 分钟 Demo 到企业级平台(第100篇-E86)
leeyi2026/8/31

这是第 100 篇,收官篇。不拆新源码——反过来,把 99 篇走过的路和 DeepFlux 这个真实仓库放在一起盘一遍。 (一)100 篇走了哪几步 回头看,系列其实是一条"从外向内、再向外"的路线: 第一季(01-14):从平台往下看。 先拆 DeepFlux 这个企业级平台——DDD 四层、Session 状态机、ReAct 主循环、HITL、SSE 推流、Hook 护栏、多 LLM、RAG、记忆、RLS 多租户、审计、可观测。当时的问题是"企业级 Agent 平台长什么样",答案是 14


FastAPI 后台任务的边界,以及 Celery、Redis 与自建调度系统的选择
卷无止境2026/8/23

FastAPI 的 BackgroundTasks 很轻巧,但它更像是响应发送后的顺手处理机制,并不是一套可靠的任务队列。写日志、发送不太关键的通知、清理临时文件,它用起来很舒服;一旦任务涉及可靠投递、自动重试、跨机器执行、定时调度或运行状态追踪,就该把工作交给 Celery、Redis Streams,或者独立的任务调度服务。 FastAPI 的 BackgroundTasks 做了什么 FastAPI 的 BackgroundTasks 建立在 Starlette 的后台任务机制之上。开发


AI 工具越好用越要严格把控安全边界
MobotStone2026/8/10

前端时间,有个小伙伴问我:“公众号文章、标书这些东西,是不是都可以直接丢给 AI,让它帮忙处理?” 刚听到这个问题,我觉得挺常见。现在大家写文章、改材料、做总结,第一反应就是找 AI,确实能省不少时间。 但继续聊了几句,我才发现,事情没那么简单。 他已经把公司一整份标书上传给 AI 了。 而这份标书里,不只有网上能查到的公司介绍、业务信息,还包括营业执照、员工社保资料,甚至身份证件等敏感材料。 这两类内容,看起来都是“让 AI 帮忙处理文档”,风险却完全不是一个级别。 比如,一篇准备公开发布的公


Linux——基础开发工具(上)
进击的荆棘2026/7/31

💁‍♂️个人主页:进击的荆棘 👇作者其它专栏: 《数据结构与算法》《算法》《C++起始之路》《Linux》 目录 1.软件包管理器 2.编辑器Vim 3.编译器gcc/g++ 4.自动化构建--make/Makefile 5.Linux第一个系统程序--进度条 6.版本控制器Git 7.调试器-gdb/cgdb使用 1.软件包管理器 1.1什么是软件包 ●在Linux下安装软件,一个通常的办法是下载到程序的源代码,并进行编译,得到可执行程序。 ●但


c++11终章
牢姐与蒯2026/7/23

一.lambda 1.lambda的语法 ①.定义 lambda表达式是一个匿名函数对象,跟普通函数只能定义在全局,或者类里不同,它能够定义在函数的内部。 ②.类型 lambda表达式在使用层而言没有类型,一般用auto或者模板参数定义的对象去接收lambda对象。 ③.lambda的格式 表达式格式跟普通函数相比,少了函数名(匿名),多了捕捉列表。 一个小示例: 注:此处修正一下,返回值部分是“->返回值类型”。 2.lambda的用途 从图中可看出利用lam

首页编辑器站点地图

本站内容在 CC BY-SA 4.0 协议下发布

Copyright © 2026 聚合阅读