MySQL磁盘空间排查实践：binlog与InnoDB表空间五类占用治理

大家好，我是数据库小学妹👋我踩过的坑，你别再踩。

凌晨两点四十五分，手机被告警短信叫醒。磁盘使用率98%，MySQL所在分区只剩不到两个G。第一反应是df -h确认情况。

df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       500G  490G   10G  98% /var/lib/mysql

确认确实满了，接着用du -sh逐层下钻，找出到底是谁在吃盘。

du -sh /var/lib/mysql/*
320G    ibdata1
85G     mysql-bin.000087
42G     mysql-bin.000086
38G     slow.log
55G     app_db

看到结果我倒吸一口凉气。ibdata1共享表空间占了320G，binlog两个文件加起来127G，慢日志38G。这一晚我没睡，但把五个吃硬盘的大户一个个查清楚了。今天完整写出来，希望你遇到同样情况能照着排一遍就能定位，少走弯路少踩坑。

第一个吃硬盘大户：binlog暴胀

binlog本身没有总量上限，约束它的只有过期时间。如果expire_logs_days设了30天，生产写入量大，30天的binlog轻轻松松上百G。我那次更极端，一个批量导入任务跑了6小时，单小时就写了40G，两个文件127G就是这么来的。

这里要澄清一个容易误解的点。max_binlog_size控制的是单个文件大小，到阈值会自动切新文件，但一个事务不会被拆到两个文件，所以单个binlog可能略大于设定值。真正决定binlog总量的，还是过期时间。

排查方法

-- 查看binlog保留策略
SHOW VARIABLES LIKE 'expire_logs_days';
-- 8.0+ 用这个
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- 查看当前binlog文件大小
SHOW BINARY LOGS;

清理方法

-- 清理指定时间之前的binlog
PURGE BINARY LOGS BEFORE '2026-08-01 00:00:00';

-- 或者清理到指定文件
PURGE BINARY LOGS TO 'mysql-bin.000080';

别用rm直接删binlog文件。MySQL的binlog index文件里记录了所有binlog文件名，rm删了文件但index不会更新，下次启动就会报错。PURGE命令会同步更新index，这是它的价值所在。

治本方案

# my.cnf
# 8.0以下设置天数，8.4起该参数已彻底移除
expire_logs_days = 7
# 8.0+用秒数，8.4后只剩这一个参数
binlog_expire_logs_seconds = 604800
# 单文件最大1G，超过自动切新文件
max_binlog_size = 1G

保留7天是常见起点，核心系统可以设到14天。有主从复制的系统要先确认从库追平再清理，否则从库要拉取的binlog被删了，主从就断了。

-- 在主库上确认从库位点
SHOW SLAVE HOSTS;
-- 确保从库的Exec_Master_Log_Pos接近主库的binlog位点

第二个吃硬盘大户：InnoDB共享表空间

ibdata1是InnoDB的共享表空间，存的是change buffer、doublewrite buffer这些全局结构。5.6之前连表数据和索引也塞在这里，5.6之后可以开innodb_file_per_table让每张表独立成.ibd文件，undo在8.0也默认独立出去了。

最坑的一点是，ibdata1一旦撑大就缩不回来。你DELETE一百万条数据，表里数据是少了，但磁盘占用一点不减。DELETE只是把page标记为可复用，空间还留在表空间里，不会归还操作系统。想真正缩回来，只能重建。

排查方法

-- 查看表空间大小
SELECT table_name,
  ROUND(data_length/1024/1024, 2) AS data_mb,
  ROUND(index_length/1024/1024, 2) AS index_mb,
  ROUND(data_free/1024/1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'app_db'
ORDER BY data_free DESC;

-- 查看ibdata1实际大小
SELECT file_name, tablespace_name,
  ROUND(total_extent_size/1024/1024/1024, 2) AS size_gb
FROM information_schema.FILES
WHERE tablespace_name = 'innodb_system';

上面SQL里的data_free就是碎片空间，它只说明有多少page被标记了可复用，不表示这些空间已经还给操作系统。

治本方案：独立表空间

# my.cnf
innodb_file_per_table = ON

这个参数MySQL 5.6+默认就是ON，但老系统可能还是OFF。开启后每张表有自己的.ibd文件，DELETE后虽然ibd文件不会自动缩小，但至少可以用OPTIMIZE TABLE回收空间。

-- 回收单表碎片
OPTIMIZE TABLE t_order;
-- 本质是重建表，期间会锁表

OPTIMIZE TABLE本质是重建表，期间会锁表，而且重建需要额外的临时空间，表里数据多的话反而可能把盘写爆。所以大表别直接OPTIMIZE，用gh-ost或pt-online-schema-change在线重建更稳妥。

如果ibdata1已经大到几百G，唯一办法是逻辑导出、重建数据库、再导入回去。

# 全量导出
mysqldump --all-databases --routines --triggers > /tmp/full.sql
# 停库
systemctl stop mysqld
# 清空数据目录
rm -rf /var/lib/mysql/*
# 重新初始化
mysqld --initialize
# 导入
mysql < /tmp/full.sql

这个过程要停机，所以最好在设计阶段就开启innodb_file_per_table。

第三个吃硬盘大户：undo日志膨胀

undo日志存的是事务回滚需要的旧版本数据，也是MVCC读视图要用的历史版本。长事务不提交，undo就一路增长。

有个真实场景：一个报表查询开了事务，跑了40分钟。这40分钟里所有变更数据的undo都不能清理，undo表空间直线增长。原因在于InnoDB的purge线程只会清理不再被任何读视图引用的旧版本，长事务的读视图一直存在，purge就一直滞后。

排查方法

-- 查看长事务
SELECT trx_id, trx_state, trx_started,
  trx_query, trx_mysql_thread_id,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 60 SECOND
ORDER BY duration_sec DESC;

-- 查看undo表空间大小
SELECT file_name, tablespace_name,
  ROUND(total_extent_size/1024/1024, 2) AS size_mb
FROM information_schema.FILES
WHERE tablespace_name LIKE 'innodb_undo%';

监控上还要盯history list length，这个值直接反映purge是否滞后，数值长期偏高说明有长事务在拖后腿。

清理方法

杀掉长事务。

-- 找到线程ID后直接KILL
KILL 12345;

杀掉后undo日志会自动被purge线程清理，但空间不会释放回操作系统，只是标记为可复用。

治本方案

# my.cnf - 8.0+ undo表空间自动截断
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G
innodb_purge_rseg_truncate_frequency = 128

8.0默认就会创建两个独立undo表空间（undo_001和undo_002），不用再像老版本那样手工配innodb_undo_tablespaces，这个参数8.0.14起已经废弃，8.0.21后直接移除，额外的undo表空间用CREATE UNDO TABLESPACE语句管理。

开启undo自动截断后，undo表空间超过1G会自动收缩。innodb_undo_log_truncate在8.0里默认就是ON，通常不用改。innodb_purge_rseg_truncate_frequency控制purge多少次才尝试截断一次，设小点截断更勤快，但会带来额外开销。在线截断从5.7就开始支持，5.6及以下才需要重建实例。

第四个吃硬盘大户：临时表

临时表分两种，内存临时表和磁盘临时表。内存临时表超过tmp_table_size或max_heap_table_size限制后，会自动转成磁盘临时表，落在tmpdir目录。8.0有个关键变化：内存内部临时表的默认引擎从MEMORY换成了新的TempTable引擎，磁盘内部临时表则固定用InnoDB的temp tablespace。TempTable有独立的内存上限参数temptable_max_ram，超了照样落盘。8.0之前磁盘临时表还是MyISAM引擎，8.0起才统一成InnoDB。

大表的GROUP BY和ORDER BY走不了索引，就会用filesort加磁盘临时表。一个百万行的表做GROUP BY，磁盘临时表可能占到十几G。判断SQL会不会产生磁盘临时表，看EXPLAIN的Extra列，出现Using temporary就是用了临时表，Using filesort就是走了文件排序。

排查方法

-- 查看临时表配置
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- 查看tmpdir位置
SHOW VARIABLES LIKE 'tmpdir';

-- 查看磁盘临时表数量
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
-- 和总临时表数量对比
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

磁盘临时表和总临时表的比值长期超过5%，就说明有SQL在频繁落盘，该优化了。

清理方法

MySQL会在查询结束后自动清理临时表，但如果查询本身卡死了，临时文件就一直占着空间。

-- 找到正在执行的查询
SELECT id, user, host, db, command, time, state,
  ROUND(LENGTH(info)/1024, 2) AS info_kb
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 杀掉卡死的查询
KILL 67890;

治本方案

# my.cnf
tmp_table_size = 128M
max_heap_table_size = 128M
# tmpdir指向独立的磁盘分区
tmpdir = /tmp/mysql_tmp

给tmpdir单独挂一个分区，满了不会影响数据目录。但核心解法还是优化SQL，让GROUP BY和ORDER BY走索引，不产生磁盘临时表。

第五个吃硬盘大户：慢日志和general_log

慢查询日志开久了就是磁盘炸弹。我那次38G慢日志，是因为long_query_time设了0.1秒，生产环境下80%的查询都被记录进去。这个阈值只在调优期临时用，稳定后要调回1秒。

更危险的是general_log，它记录MySQL收到的每一条SQL，写入量极大。高并发下它的性能损耗往往比磁盘占用更先暴露，尤其写到表里时。log_output默认是FILE，日志写文件；只有设成TABLE才会进mysql库的general_log和slow_log表，8.0里这些表可能是CSV或MyISAM引擎，查询方便但同样吃盘。

排查方法

-- 查看慢日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看general_log
SHOW VARIABLES LIKE 'general_log%';

清理方法

# 清空慢日志（不停机）
> /var/log/mysql/slow.log

# 或者轮换日志
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.bak
mysqladmin flush-logs

mysqladmin flush-logs会让MySQL关闭当前日志文件、打开新的，这样就不用停服务。

治本方案

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
# 生产建议1秒，调优期可设0.5秒
long_query_time = 1
# 关闭general_log，只在排错时临时开
general_log = 0
# 未走索引的查询也记录，排错时临时开
log_queries_not_using_indexes = 0

用logrotate做日志轮换。

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    postrotate
        mysqladmin flush-logs
    endscript
}

一套完整的磁盘监控方案

事后我搭了这套监控。Prometheus通过mysqld_exporter采集MySQL指标，Grafana做可视化。关键告警规则：磁盘使用率超80%预警，超90%紧急告警，binlog文件数量超30个预警，单个binlog超2G预警，慢日志超5G预警，ibdata1超50G预警。这些规则看着简单，但能挡掉90%的磁盘事故。

除了磁盘占用，还要盯两个容易被忽略的指标。一个是history list length，反映purge是否滞后；另一个是Created_tmp_disk_tables和Created_tmp_tables的比值，反映临时表落盘比例。后者长期超5%，就该去翻慢日志优化SQL了。

避坑清单

ibdata1共享表空间一旦撑大就缩不回来，想回收只能全量导出、重建库、再导入，全程停机。所以我一直强调innodb_file_per_table必须从第一天就开，让每张表独立成.ibd文件，单表碎片还能用OPTIMIZE TABLE单独回收。老系统如果还是OFF，趁数据量没大到不可收拾之前改掉，别拖。

清理binlog最忌讳用rm直接删。binlog index文件里记录着所有文件名，rm删了文件但index不更新，下次启动MySQL直接报错起不来。要用PURGE BINARY LOGS，它会同步更新index。主从架构下更要先确认从库追平再清，否则从库要拉的binlog被删，主从就断了。

OPTIMIZE TABLE本质是重建表，期间会锁表，而且重建需要额外的临时空间。大表直接OPTIMIZE，反而可能把本就不多的磁盘写爆。大表要么在业务低峰期做，要么用gh-ost、pt-online-schema-change在线重建，边导数据边切表，业务几乎无感。

general_log是个隐藏炸弹，它记录每一条SQL，高并发下性能损耗往往比磁盘占用更先暴露。只在排错时临时开，用完立刻关。慢日志的long_query_time也别长期设0.1秒这种激进值，调优期用完就调回1秒，否则慢日志自己就是下一个磁盘大户。

总结

回头看这次事故，磁盘爆盘从来不是某一个文件的锅，而是缺了对"谁在吃盘"的持续关注。五个大户其实可以分成两类：binlog、undo、临时表是流量型，跟着写入量走，靠配置和SQL优化就能控住；ibdata1和慢日志是存量型，一旦撑大难回收，只能靠预防。真正要记住的也就三件事：innodb_file_per_table从第一天开，清理用PURGE别用rm，general_log用完就关。剩下的交给监控。

我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋