萬盛學電腦網

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

MySQL 獨立表空間 innodb_file_per_table 參數詳解與遷移實戰

背景:共享表空間 ibdata1 無限增長

默認 innodb_file_per_table = OFF,所有 InnoDB 表數據、索引、插入緩存、Undo 日志、數據字典均存放於共享表空間 ibdata1,導致文件持續膨脹,且刪除表數據不釋放磁盤空間(高水位線不回縮)。

innodb_file_per_table 作用

  • ON:每張表獨立 .ibd 文件存儲數據與索引,DROP TABLE/TRUNCATE 即時釋放磁盤空間,便於單表備份/遷移/壓縮。
  • OFF:共享表空間,上述優勢均無。

注意: 開啟後,ibdata1 仍存放:Undo Log、Change Buffer、Data Dictionary(元數據),不可刪除。

遷移實戰:從共享表空間轉為獨立表空間

1. 全量導出數據

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > /tmp/all-database.dump

2. 刪除所有業務庫(保留 mysql 系統庫)

mysql -u root -p -e "SHOW DATABASES;" | grep -v -E "^(Database|information_schema|mysql|performance_schema|sys)$" | xargs -I{} mysql -u root -p -e "DROP DATABASE {};"

3. 停止 MySQL 並備份/移除舊共享表空間文件

service mysqld stop
mv /var/lib/mysql/ibdata1 /tmp/
mv /var/lib/mysql/ib_logfile0 /tmp/
mv /var/lib/mysql/ib_logfile1 /tmp/

4. 配置獨立表空間並指定 ibdata1 初始大小

vi /etc/my.cnf
[mysqld]
innodb_file_per_table = 1
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:1G:autoextend:max:10G
# 也可多文件:ibdata1:1G;ibdata2:512M:autoextend

5. 啟動 MySQL 自動創建新 ibdata1

service mysqld start

6. 導入數據(自動生成 .ibd 文件)

mysql -u root -p < /tmp/all-database.dump

驗證

ls -lh /var/lib/mysql/*/*.ibd   # 每表一個 .ibd 文件
du -sh /var/lib/mysql/ibdata1   # 體積大幅縮小(僅存元數據/Undo)

生產環境注意事項

  1. 必須停機遷移,或使用 pt-online-schema-change 在線逐表轉換(ALTER TABLE t ENGINE=InnoDB 觸發重建)。
  2. 遷移前全量備份,驗證備份可用性。
  3. innodb_data_file_path 設定 autoextend 並限制 max,防止失控佔滿磁盤。
  4. 監控 Innodb_data_file_path、Innodb_data_pending_fsyncs 等指標。
copyright © 萬盛學電腦網 all rights reserved