ibdata1 是 InnoDB 共享表空間文件,默認配置下存儲:
innodb_file_per_table)為什麼只增不減? InnoDB 從不主動歸還已分配的磁盤空間給操作系統;DELETE、TRUNCATE、DROP TABLE 僅標記頁面可復用,文件大小不變。
mysqldump --single-transaction --routines --triggers --all-databases > full_backup.sqlSET GLOBAL read_only=ON;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');
缺點: 需要兩次全表重建,大表耗時極長;期間業務完全不可用。
# 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×數據量)。