使用 innodb_file_per_table 解決 ibdata1 單一且過大

參考這裡


使用 mysql 配合 innodb 引擎在預設的情況下所有的資料都會儲存在 ibdata1

用久了這個檔案會很大，比較好的作法是把每個資料庫的 ibdata 各別儲存。

先備份全部資料庫

mysqldump -u root -p --all-databases > /home/backup/all.sql

找到 my.cnf 或是 my.ini 並加入 innodb_file_per_table=1
以我使用 EC2 自架 mysql 來說是 /etc/my.cnf
重啟資料庫  #service mysqld restart
進入資料庫

#mysql -uroot -ppassword

檢查是否開啟分類儲存功能

mysql> show variables like '%per_table%';

若已開啟會長得像

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

再來把 ibdata1 以及 ib_logfile* 刪掉，此時進入資料庫應該就是空空的了
在還原之前最好先停止網路服務，我曾經還原到一半 ec2 就斷線當機給我看

#service httpd stop

還原

mysql -uroot -p < /home/backup/all.sql

啟用 innodb_file_per_table 後，當刪除資料表(DROP TABLE)、清空資料表(TRUNCATE TABLE)，佔用的空見會自動縮減。刪除資料表資料(DELETE)，則執行 OPTIMIZE TABLE 來縮減資料表空間。

OPTIMIZE TABLE 資料表名稱

最後補充一下使用別人整理的 innodb_file_per_table 的好處

1. 如果使用軟鏈結將大表分配到不同的分區上，易於管理資料文件
2. 易於監控解决IO資源使用的问题
3. 易於修復和恢復損壞的資料
    3.1 相互獨立，不影響其他innodb表
    3.2 導出導入針對單一table，而不是整個共享table
4. 解決單一文件大小的限制
5. 對於大量的delete操作，更易回收磁碟空間
6. 碎片較少，易於整理optimize table
7. 易於安全檢查
8. 易於備份