萬盛學電腦網

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

解決 MySQL ibdata1 文件無限增大:啟用 innodb_file_per_table 徹底拆分表空間

問題現象

使用 InnoDB 且未開啟 innodb_file_per_table 時,所有表數據、索引、撤銷日誌混存於共享表空間 ibdata1。刪除數據/表不會縮小 ibdata1,文件單調增長,佔用大量磁盤,甚至導致磁盤滿載。

根因

  • 共享表空間模式下,DROP TABLE/DELETE 只標記頁可復用,**不會向作業系統歸還空間**
  • 無法單表遷移、備份、IO 隔離

徹底解決方案:遷移至獨立表空間

前提條件

  • MySQL 5.6.6+ / MariaDB 10.0+(默認已開啟)
  • **需停機維護窗口**,涉及全量導出導入

操作步驟

# 1. 停止業務寫入,備份數據
mysqldump -uroot -p --single-transaction --routines --triggers --events dbname | gzip > dbname.sql.gz

# 2. 修改配置
vim /etc/my.cnf
[mysqld]
innodb_file_per_table = 1

# 3. 停止 MySQL,刪除舊共享表空間文件
systemctl stop mysqld
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

# 4. 啟動 MySQL(自動創建新 ibdata1、ib_logfile0/1)
systemctl start mysqld

# 5. 驗證配置生效
mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_file_per_table';"
# 輸出 ON 表示成功

# 6. 導入數據
mysql -uroot -p dbname < dbname.sql
# 或登入後 source dbname.sql

遷移後效果

  • 每個表擁有獨立 .ibd 文件(數據+索引)
  • DROP TABLE / TRUNCATE 立即釋放磁盤空間
  • 支持單表 DISCARD/IMPORT TABLESPACE 遷移、備份
  • ibdata1 僅存儲系統數據字典、雙寫緩衝、撤銷日誌(若未配置獨立 undo 表空間),體積穩定在幾百 MB

附錄:Linux 修改 MySQL 數據目錄(datadir)

# 1. 新建目錄並複製數據
mkdir -p /data/mysql
cp -R /var/lib/mysql/* /data/mysql
chown -R mysql:mysql /data/mysql

# 2. 修改配置
vim /etc/my.cnf
datadir = /data/mysql

# 3. 修改 AppArmor/SELinux 策略
# Ubuntu/Debian (AppArmor)
vim /etc/apparmor.d/usr.sbin.mysqld
# 將 /var/lib/mysql 替換為 /data/mysql
systemctl restart apparmor

# CentOS/RHEL (SELinux)
semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
restorecon -Rv /data/mysql

# 4. 重啟驗證
systemctl restart mysqld

常見誤區避雷

copyright © 萬盛學電腦網 all rights reserved
誤區正確認知
直接刪除 ibdata1 解決空間問題會導致數據字典丟失,實例無法啟動,**絕對禁止**
ALTER TABLE ... ENGINE=InnoDB 可縮小 ibdata1僅重建該表 .ibd,共享表空間模式下 ibdata1 不縮小
開啟 innodb_file_per_table 後自動拆分現有表**僅對新建表生效**,存量表需重建(導出導入或 ALTER TABLE ... ENGINE=InnoDB)