MySQL 磁盘一夜涨了 200G：binlog、临时表与 undo 的 4 个排查方向

我不会起名字322 · 后端 / 算法 / 数据库

📘 技术栈 ｜ 🧩 力扣 Hot100 ｜ 🐹 Go 项目 ｜ 🟥 Redis ｜ 🐬 MySQL

文章目录

• MySQL 磁盘一夜涨了 200G：binlog、临时表与 undo 的 4 个排查方向
• • 一、先看盘：是谁在长
  • 二、方向 1：binlog（最常见）
  • 三、方向 2：临时表与临时文件
  • 四、方向 3：undo 表空间
  • 五、方向 4：表碎片与系统表空间
  • 六、命令清单
  • 七、配置模板（防再犯）
  • 八、总结

MySQL 磁盘一夜涨了 200G：binlog、临时表与 undo 的 4 个排查方向

早上到公司收到磁盘告警：MySQL 所在的数据盘从 40% 涨到 92%，一夜多了 200G。业务数据量并没有明显增长——那这 200G 是谁？

MySQL 的磁盘占用不等于数据量。除了表文件（.ibd）之外，还有几类文件会悄悄长大，而且其中大部分不会因为删除数据而自动收缩。这篇文章按 4 个方向逐项排查：binlog、临时表与临时文件、undo 表空间、表碎片与系统表空间，每个方向都给确认命令和安全清理动作。

一、先看盘：是谁在长

在动手删任何东西之前，先把空间占用的构成摸清楚：

# 1) 先看 MySQL 数据目录整体大小
du -sh /var/lib/mysql

# 2) 再按目录/文件从大到小排
du -sh /var/lib/mysql/* | sort -rh | head -20

# 3) 单独看几类关键文件
ls -lh /var/lib/mysql/ibdata1 /var/lib/mysql/ibtmp1 2>/dev/null
ls -lh /var/lib/mysql/undo_* 2>/dev/null
ls -lh /var/lib/mysql/binlog.* /var/lib/mysql/mysql-bin.* 2>/dev/null | tail -20

判断规则（按可能性排序）：

• mysql-bin.* / binlog.* 一堆且很新 → 方向 1：binlog；
• ibtmp1 很大 → 方向 2：临时表空间；
• undo_001 / undo_002 很大 → 方向 3：undo 表空间；
• ibdata1 很大且你的表都是独立表空间 → 方向 4：系统表空间（可能是历史遗留或 undo 在系统表空间里）；
• 某个库的 .ibd 很大但表里没多少数据 → 方向 4：碎片。

二、方向 1：binlog（最常见）

binlog 记录了所有变更，是主从复制和数据恢复的基础。它不会因为你的数据被删除而变小，只能靠过期清理。

确认占用：

SHOW BINARY LOGS; — 列出所有 binlog 文件及大小
SELECT @@log_bin_basename, @@binlog_format, @@max_binlog_size;
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; — 8.0
SHOW VARIABLES LIKE 'expire_logs_days'; — 5.7

⚠ 千万不要直接 rm 掉 binlog 文件——MySQL 靠 mysql-bin.index 管理它们，手动删会导致复制错乱、实例起不来。用官方命令清理：

— 清理到指定文件之前的所有 binlog（保留这个文件本身）
SHOW BINARY LOGS; — 先确认要保留哪个
PURGE BINARY LOGS TO 'mysql-bin.000123'; — 删掉 000123 之前的

— 或者按时间清理
PURGE BINARY LOGS BEFORE '2026-10-08 00:00:00';

— ⚠ 有从库时，必须确认从库已经消费完，否则会导致复制中断
SHOW SLAVE STATUS\\G — 看 Relay_Master_Log_File 读到哪了

自动过期配置（8.0 推荐用秒级参数）：

— 8.0：保留 7 天
SET GLOBAL binlog_expire_logs_seconds = 604800;
SET GLOBAL max_binlog_size = 1073741824; — 单个文件 1G，太大不好管理

— 5.7：保留 7 天
SET GLOBAL expire_logs_days = 7;

配置文件同步（重启后仍生效）：

[mysqld]
binlog_expire_logs_seconds = 604800 # 8.0；5.7 用 expire_logs_days = 7
max_binlog_size = 1073741824

判断 binlog 该留多久：从库最坏情况下的追平时间 + 一次全备的周期，通常 3~7 天足够。留 30 天又没清理策略，就是磁盘暴涨的头号原因。

三、方向 2：临时表与临时文件

MySQL 在执行大排序、分组、ALTER TABLE、以及显式 CREATE TEMPORARY TABLE 时会用到临时空间，分两种：

确认：

ls -lh /var/lib/mysql/ibtmp1
du -sh /tmp | tail -1

— 落盘临时表的累计次数（涨得快说明有大量落盘排序/分组）
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'tmp_table_size'; — 内部临时表内存上限
SHOW VARIABLES LIKE 'max_heap_table_size';
— 当前正在用的临时表
SELECT * FROM information_schema.innodb_temp_table_info LIMIT 10;

处理：

[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 60; — 找出可能正在造临时表的大查询

四、方向 3：undo 表空间

长事务会阻止 undo 被清理——一个跑了 6 小时的事务，会让这 6 小时里所有相关行的 undo 记录都无法回收，undo 表空间持续膨胀。

确认：

— 8.0：查看 undo 表空间
SELECT tablespace_name, file_name, ROUND(initial_size/1024/1024) AS init_mb,
ROUND(autoextend_size/1024/1024) AS extend_mb
FROM information_schema.files
WHERE file_type = 'UNDO LOG';

— ⭐ 找长事务（这是 undo 涨的根因）
SELECT trx_id, trx_started, trx_state,
TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS minutes,
LEFT(trx_query, 100) AS sql_text
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

MySQL 8.0 支持 undo 表空间的自动 truncate（需要满足两个条件）：

SHOW VARIABLES LIKE 'innodb_undo_tablespaces'; — 8.0 默认 2，且必须是 ≥2 才能 truncate
SHOW VARIABLES LIKE 'innodb_max_undo_log_size'; — 默认 1G，超过后标记为可 truncate
SHOW VARIABLES LIKE 'innodb_purge_rseg_truncate_frequency'; — 默认 128，调小更激进

手动触发（8.0）：

— 先把某个 undo 表空间设为 inactive，再自动 truncate
ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
— 之后再设回 ACTIVE
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;

⚠ 注意：有大事务在跑时 SET INACTIVE 会失败。所以根治办法是消灭长事务——把批量操作拆小、给应用侧语句加超时、监控 innodb_trx 里 trx_started 很老的事务。

五、方向 4：表碎片与系统表空间

删除了大量数据之后，.ibd 文件不会变小（空间被标记为可复用，但不会还给操作系统）。表现就是「表里只有 100 万行，文件却有 50G」。

确认：

— 看每个表的数据大小和「可复用但未释放」的空间
SELECT
table_schema,
table_name,
ROUND(data_length/1024/1024) AS data_mb,
ROUND(index_length/1024/1024) AS index_mb,
ROUND(data_free/1024/1024) AS free_mb, — ⭐ 碎片空间
table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
AND engine = 'InnoDB'
ORDER BY free_mb DESC
LIMIT 20;

data_free 很大 → 碎片严重。处理方式：

— 方式 1：重建表并回收空间（会锁表/需要额外空间，8.0 支持在线）
OPTIMIZE TABLE dbname.tablename; — 等价于 ALTER TABLE … FORCE

— 方式 2（推荐）：在线重建，不长时间锁表
ALTER TABLE dbname.tablename ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

⚠ 两条注意事项：

• 重建需要额外的磁盘空间（约等于表大小），磁盘已经满的时候要先腾出空间，否则会失败；
• 大表重建耗时长、产生大量 binlog（有从库时会造成延迟），务必在低峰做，有从库时先评估延迟影响。

另一类是 ibdata1（系统表空间）：如果 5.6 之前没有开 innodb_file_per_table，所有表数据都堆在 ibdata1 里，删表也不会释放。这种情况只能逻辑导出再导入来重建实例，属于大工程，建议提前规划。

SELECT @@innodb_file_per_table; — 应该是 ON（5.6 起默认开）

六、命令清单

七、配置模板（防再犯）

[mysqld]
# ===== binlog：必须有过期策略 =====
binlog_expire_logs_seconds = 604800 # 8.0：保留 7 天（5.7 用 expire_logs_days = 7）
max_binlog_size = 1073741824

# ===== 临时表空间：设上限，避免无限增长 =====
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
tmp_table_size = 64M
max_heap_table_size = 64M
tmpdir = /data/mysql_tmp # 建议单独挂盘

# ===== undo：8.0 的自动 truncate =====
innodb_undo_tablespaces = 2 # ≥2 才能 truncate
innodb_max_undo_log_size = 1G
innodb_purge_rseg_truncate_frequency = 32 # 调小 = 更积极地回收

# ===== 独立表空间（5.6 起默认 ON，确认一下）=====
innodb_file_per_table = ON

配套的监控建议（比清理更重要）：

八、总结

本文命令以 MySQL 8.0 为主，5.7 的差异已在文中标注（expire_logs_days、innodb_undo_tablespaces 的行为、undo 手动 truncate 在 5.7 上不支持，只能重建实例）。执行任何清理前请确认你有过可用备份。