2014年12月12日 星期五

使用 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. 易於備份

沒有留言:

張貼留言