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

[yehlu]

https://www.phpini.com/mysql/mysql-rele ... disk-space
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:


mysql> OPTIMIZE TABLE table_name;
1
mysql> OPTIMIZE TABLE table_name;
2. ALTER TABLE

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





ALTER TABLE table_name ENGINE = InnoDB;
1
ALTER TABLE table_name ENGINE = InnoDB;
innodb_file_per_table 關閉

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

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


# mysqldump -u root -p --all-databases > alldb.sql
1
# mysqldump -u root -p --all-databases > alldb.sql
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. 匯入資料庫備份:


# mysql -u root -p < alldb.sql
1
# mysql -u root -p < alldb.sql