MySQL InnoDB 刪除資料後釋放硬盤空間

MySQL 使用 InnoDB 時,當資料表用 DELETE FROM 刪除資料後,硬碟空間並不會立即釋放,因為 MySQL 只對已刪除的資料標記為刪除,並沒有真正刪除資料,這會導致 InnoDB 的檔案不斷擴大。只有在 DROP TABLE 及 TRUNCATE TABLE 時才會釋放空間。

如果沒有開啟 innodb_file_per_table,所有 InnoDB 的資料都會儲存在 ibdata 檔案內,對於資料庫的體積很大的 MySQL Server,要釋放硬碟空間會很痛苦,因為只有將 MySQL 資料庫導出,再重新將備份還原才可以釋放空間。如果 innodb_file_per_table 有開啟,每個 InnoDB 資料表都會有獨立的檔案,可以較簡單地釋放硬碟空間, 以下是 innodb_file_per_table 開啟及關閉的釋放空間方法。

innodb_file_per_table 開啟

首先是較簡單的情況, 在 innodb_file_per_table 開啟時, 當用 DELETE 刪除資料後, 可以用以下方法釋放硬碟空間。

1. OPTIMIZE TABLE

只要執行 OPTIMIZE TABLE 指令, 將資料表最佳化, 便可以釋放硬碟空間, 例如資料表名稱是 table_name:

2. ALTER TABLE

另一個方法是用 ALTER TABLE 指令:


innodb_file_per_table 關閉

如果 innodb_file_per_table 是關閉,要釋放經由 DELETE 刪除的資料的空間,需要把資料庫導出,刪除 innodb 資料庫,然後再匯入, 以下是操作步驟, 但請務必關閉 MySQL Server 後, 將資料庫目錄先備份, 即使有問題要還原也較快。

1. 使用 mysqldump 指令將 innodb 資料庫導出:

2. 刪除所有資料庫, 但要留下 “mysql” 及 “information_schema” 兩個資料庫不要刪除.

3. 關閉 MySQL 伺服器:

# systemctl stop mysql

4. 刪除 ibdata 檔案及日誌:

# rm /var/lib/mysql/ibdata1
# rm /var/lib/mysql/ib_logfile*

5. 為了避避免日後易於維護, 開啟 innodb_file_per_table, 開啟檔案 /etc/my.cnf:

# vi /etc/my.cnf

在 [mysqld] 段落加入以下一行:

innodb_file_per_table=1

6. 啟動 MySQL Server:

# systemctl start mysql

7. 匯入資料庫備份:


Leave a Reply