MySQL InnoDB 表空間擴容與 ibdata1 瘦身完全指南：原理、操作與避坑

背景：共享表空間 ibdata1 只增不減

啟用 innodb_file_per_table=ON 後，表數據存於獨立 .ibd 文件，但 **ibdata1 仍存儲**：數據字典、Undo Log、Change Buffer、Doublewrite Buffer。長事務、大事務、Purge 延遲會導致 ibdata1 暴增，**無法自動縮小**。

第一部分：表空間擴容（添加 ibdata 文件）

前置檢查

# 1. 確認當前配置與實際文件一致
ls -lh /var/lib/mysql/ibdata*

# 2. 查看 my.cnf 配置
grep innodb_data_file_path /etc/my.cnf
# 例：innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend

擴容操作步驟

1. 停止 MySQL：systemctl stop mysqld
2. 編輯 my.cnf：在 innodb_data_file_path 末尾追加新文件

   # 原配置
      innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend
      # 新配置（注意：原 autoextend 文件必須指定確切當前大小）
      innodb_data_file_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend

3. 關鍵：精確指定原 autoextend 文件實際大小

   # 獲取 ibdata2 實際大小 (MB，向下取整到整 MB)
      ls -l /var/lib/mysql/ibdata2
      # -rw-rw---- 1 mysql mysql 16106127360 Jan 26 16:34 ibdata2
      # 16106127360 / 1024 / 1024 = 15360 MB
      # 配置寫 15360M，不能寫 15G 或 16G

4. 啟動 MySQL：systemctl start mysqld
5. 驗證：ls -lh /var/lib/mysql/ibdata3 應自動創建並開始增長

注意事項

• 磁盤空間預留：新文件會預分配或按需增長
• 只能在末尾添加，不能插入中間
• 最後一個文件才能用 :autoextend
• 重啟時間 = 關閉 + 啟動，評估業務窗口

第二部分：ibdata1 瘦身（唯一徹底方案：邏輯重建）

什麼佔用了 ibdata1？診斷工具

# 1. 官方工具：innochecksum (需編譯或從 Percona 下載)
./innochecksum /var/lib/mysql/ibdata1
# 輸出頁類型統計，如 UNDO_LOG 佔 93%

# 2. 進階：Jeremy Cole 的 InnoDB Ruby 工具
gem install innodb_ruby
innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l

常見暴增原因

1. 長事務未提交：SHOW ENGINE INNODB STATUS\G 看 TRX ACTIVE 1256288 sec，Undo Log 無法清理
2. Purge 線程跟不上：History List Length 持續增長
3. 大事務批量操作：產生海量 Undo Log

瘦身標準流程：邏輯導出重建

# 1. 完整邏輯備份 (單事務一致性，含觸發器/存儲過程)
mysqldump -uroot -p --single-transaction --routines --triggers --events \
  --hex-blob --default-character-set=utf8mb4 --master-data=2 \
  --all-databases > /backup/full_$(date +%F).sql

# 2. 停止服務
systemctl stop mysqld

# 3. 刪除共享表空間與日志文件 (保留 .ibd 表空間文件若未重建)
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile* /var/lib/mysql/mysql-bin.index

# 4. 刪除除 mysql 系統庫外的所有數據庫目錄
# ls /var/lib/mysql/ | grep -v '^mysql$' | xargs rm -rf

# 5. 啟動服務 (自動創建新 ibdata1, ib_logfile0/1)
systemctl start mysqld

# 6. 還原數據
mysql -uroot -p < /backup/full_$(date +%F).sql

# 7. 驗證 ibdata1 大小大幅縮小

優化建議

• 備份前 TRUNCATE 無用大表（如日志、臨時表）
• 使用 mydumper/myloader 並行導出導入，縮短停機窗口
• 5.6+ 可配置 innodb_undo_tablespaces 將 Undo Log 移出 ibdata1
• Percona Server 支援 innodb_ibuf_max_size 限制 Change Buffer、innodb_doublewrite_file 分離 Doublewrite

預防性架構：避免 ibdata1 膨脹

ibdata1 膨脹本質是 **Undo Log 積壓**。擴容治標，邏輯重建治本，**架構分離（獨立表空間、獨立 Undo 表空間）才是永久治理**。