MySQL 存儲文件暴增問題：ibdata1 與 Binlog 清理與獨立表空間遷移

MySQL 存儲文件暴增問題：ibdata1 與 Binlog 清理與獨立表空間遷移

生產環境常遇磁盤告警，罪魁禍首通常是 ibdata1（共享表空間）與 mysql-bin.*（Binlog）。本文深度解析成因，給出清理與架構遷移方案。

一、問題現象與成因

二、InnoDB 兩種表空間模式對比

三、Binlog 清理方案

1. 手動清理

-- 刪除指定文件之前的 Binlog
PURGE MASTER LOGS TO 'mysql-bin.010';

-- 刪除指定時間之前的 Binlog
PURGE MASTER LOGS BEFORE '2024-01-15 12:00:00';

2. 自動過期（推薦）

[mysqld]
expire_logs_days = 30   # 30 天自動清理
max_binlog_size = 1G    # 單文件上限，防止單文件過大

四、共享表空間遷移至獨立表空間（核心方案）

原理： 開啟 innodb_file_per_table=1 後，新建表自動使用獨立 .ibd 文件。存量表需「導出→刪除共享文件→導入」重建。

操作步驟

1. 全量備份

   mysqldump -q -uroot -p --add-drop-table --all-databases > /backup/all.sql
   systemctl stop mysqld
   

2. 修改配置（/etc/my.cnf）

   [mysqld]
   innodb_file_per_table=1
   

3. 刪除舊文件

   rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile* /var/lib/mysql/<業務庫目錄>/*
   # 保留 mysql/ 系統庫目錄
   

4. 啟動並還原

   systemctl start mysqld
   mysql -uroot -p < /backup/all.sql
   

5. 驗證

   mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
   +-----------------------+-------+
   | Variable_name         | Value |
   +-----------------------+-------+
   | innodb_file_per_table | ON    |
   +-----------------------+-------+
   
   ls -lh /var/lib/mysql/業務庫/*.ibd   # 應見每表獨立 .ibd 文件
   

五、遷移後效果對比

# 遷移前
-rw-rw---- 1 mysql mysql 120G ibdata1

# 遷移後
-rw-rw---- 1 mysql mysql  10M ibdata1   # 僅存系統元數據
drwx------ 2 mysql mysql 4.0K db_name/
-rw-rw---- 1 mysql mysql  50M db_name/table1.ibd
-rw-rw---- 1 mysql mysql  30M db_name/table2.ibd
...

六、大表優化補充

• 單表 > 100G：考慮分區表 或 分庫分表
• 定期碎片整理：OPTIMIZE TABLE 或 ALTER TABLE t ENGINE=InnoDB
• 監控 innodb_file_per_table、innodb_open_files、磁盤使用率

完成遷移後，磁盤空間可控、備份恢復更靈活、單表維護無影響，是 InnoDB 架構演進的必經之路。