萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ibdata1 文件暴漲根因分析與三種收縮方案實戰

現象與根因

ibdata1 是 InnoDB 共享表空間文件,默認配置下存儲:

  • 數據字典(表結構元數據)
  • Undo Log(回滾段)
  • Change Buffer(輔助索引緩衝)
  • **所有 InnoDB 表的數據與索引**(若未開啟 innodb_file_per_table)

為什麼只增不減? InnoDB 從不主動歸還已分配的磁盤空間給操作系統;DELETE、TRUNCATE、DROP TABLE 僅標記頁面可復用,文件大小不變。

前置條件

  1. **完整備份**:mysqldump --single-transaction --routines --triggers --all-databases > full_backup.sql
  2. **停止寫入**:業務停機或設置 SET GLOBAL read_only=ON;
  3. **MySQL 版本 ≥ 5.6**(建議 5.7/8.0),確保支持 innodb_file_per_table 動態修改。

方案一:修改表引擎法(適合表量少、可接受停機)

-- 1. 獲取所有 InnoDB 表
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=MyISAM;')
FROM information_schema.TABLES WHERE ENGINE='InnoDB';

-- 2. 執行生成的 ALTER 語句(將數據移至 MyISAM,ibdata1 空間標記可復用)

-- 3. 停止 MySQL
systemctl stop mysqld

-- 4. 刪除共享表空間文件
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

-- 5. 修改 my.cnf
[mysqld]
innodb_file_per_table=1
innodb_data_file_path=ibdata1:12M:autoextend

-- 6. 啟動 MySQL(自動創建新的 12MB ibdata1)

-- 7. 將表改回 InnoDB(數據寫入獨立 .ibd 文件)
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');
缺點: 需要兩次全表重建,大表耗時極長;期間業務完全不可用。

方案二:導出導入法(適合中等數據量、需保留 InnoDB 特性)

# 1. 導出所有 InnoDB 表(按庫分文件,便於並行導入)
mysqldump --single-transaction --routines --triggers --databases db1 db2 > dbs.sql

# 2. 刪除目標表
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE db1.t1, db1.t2, db2.t1; -- 列出所有 InnoDB 表
SET FOREIGN_KEY_CHECKS=1;

# 3. 停止 MySQL,刪除 ibdata1/ib_logfile,修改 my.cnf 開啟 innodb_file_per_table
systemctl stop mysqld
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*
# 修改 my.cnf 同方案一
systemctl start mysqld

# 4. 導入數據
mysql -e "SET FOREIGN_KEY_CHECKS=0; SOURCE dbs.sql; SET FOREIGN_KEY_CHECKS=1;"

方案三:全量重建法(最徹底、最常用、適合大數據量)

# 1. 全量導出(含所有庫、觸發器、存儲過程、權限)
/usr/bin/mysqldump --single-transaction --routines --triggers --all-databases \
  --add-drop-database --disable-keys --quick --flush-privileges > all_databases.sql

# 2. 停止服務
systemctl stop mysqld

# 3. 備份/重命名舊數據目錄
mv /var/lib/mysql /var/lib/mysql_bak_$(date +%F)

# 4. 修改 my.cnf 開啟 innodb_file_per_table
# [mysqld]
# innodb_file_per_table=1

# 5. 重新初始化數據目錄
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql

# 6. 啟動服務
systemctl start mysqld

# 7. 導入數據
mysql -e "SET FOREIGN_KEY_CHECKS=0; SOURCE all_databases.sql; SET FOREIGN_KEY_CHECKS=1;"

# 8. 驗證:每個 InnoDB 表在對應庫目錄下有獨立 .ibd 文件
ls -lh /var/lib/mysql/*/*.ibd

驗證收縮效果

-- 檢查獨立表空間是否生效
SELECT TABLE_SCHEMA, TABLE_NAME, 
       ROUND(DATA_LENGTH/1024/1024,2) AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb
FROM information_schema.TABLES 
WHERE ENGINE='InnoDB' AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;

長效機制:新建表默認獨立表空間

MySQL 5.6.6+ innodb_file_per_table 默認 **ON**。確保 my.cnf 顯式設置:

[mysqld]
innodb_file_per_table=1
# 可選:將臨時表也放入獨立表空間(5.7+)
innodb_temp_data_file_path=ibtmp1:12M:autoextend

總結選型建議

方案停機時間風險適用場景
修改引擎法極長(2×全表重建)中表少、數據量小、可長時間停機
導出導入法長(導出+導入)低中等數據量、需保留 InnoDB 特性
全量重建法最長(重新初始化)最低(最乾淨)**生產環境首選**、大數據量、長期未清理

核心原則: 一次徹底重建,永久解決 ibdata1 膨脹。事前必備份、驗證備份可恢復、預留足夠磁盤空間(建議 2×數據量)。

copyright © 萬盛學電腦網 all rights reserved