MySQL 二進制日志佔滿磁盤排查與解決：expire_logs_days 設置、手動清理與表級空間分析

故障現象

數據庫除查詢外所有操作失敗，報錯 Got error 28 from storage engine。df -h 發現根分區 100% 滿。

定位佔用大戶

cd /
du -h --max-depth=1   # 發現 /usr 佔用最大
cd /usr
du -h --max-depth=1   # 發現 local 佔用最大
cd /usr/local
du -h --max-depth=1   # 發現 mysql/data 佔用最大

兩類常見原因與對策

情況一：二進制日志堆積

mysql-bin.000001 等文件佔用大量空間。Binlog 記錄所有 DML/DDL，默認永不自動刪除。

永久解決：配置自動過期

# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
expire_logs_days = 7   # 僅保留最近 7 天，舊日志自動清理

重啟 MySQL 生效：systemctl restart mysqld。
立即生效不重啟： SET GLOBAL expire_logs_days = 7;（需配合 FLUSH BINARY LOGS 觸發清理）。

手動清理舊日志（謹慎）

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 或按文件名
PURGE BINARY LOGS TO 'mysql-bin.000100';

⚠️ 確保從庫已同步相關日志再清理，否則導致從庫同步中斷。

情況二：單表數據/索引過大

進入具體庫目錄，ls -lh 查看 .ibd（InnoDB 獨立表空間）或 .MYD/.MYI（MyISAM）大小。

InnoDB 表空間回收

單純 DELETE 不釋放磁盤空間，需重建表：

-- 方案 1：OPTIMIZE TABLE（會鎖表，大表慎用）
OPTIMIZE TABLE huge_table;

-- 方案 2：pt-online-schema-change（無鎖在線重建，推薦生產）
pt-online-schema-change --alter "ENGINE=InnoDB" D=db,t=huge_table --execute

-- 方案 3：新建表導入數據切換（停機窗口允許時）
CREATE TABLE huge_table_new LIKE huge_table;
INSERT INTO huge_table_new SELECT * FROM huge_table WHERE ...保留條件;
RENAME TABLE huge_table TO huge_table_old, huge_table_new TO huge_table;

日常巡檢建議

• 監控磁盤使用率告警（>80% 報警）。
• 定期檢查 SHOW BINARY LOGS 文件數量與大小。
• 大表制定歸檔/分區策略，避免無限增長。
• 備份驗證：確保清理 Binlog 前已完成全備/增備。