MySQL innoDB 縮減空間大小

MySQL 的 innodb_file_per_table 設定為 0 時，所有 innoDB 資料表的資料，都儲存在同一個表空間 ibdata* 檔案。此時，即使刪除某個資料表的資料，ibdata* 的檔案也不會變小，時間一久，佔用的空間就會越來越大。若想回收 ibdata* 的檔案空間，可先將全部的資料庫匯出，再重新匯入。步驟如下：

1. 使用  mysqldump 匯出全部 innoDB 資料表。
2. 刪除所有 innoDB 資料表。
3. 停止 MySQL。
4. 刪除 ibdata 和 ib_log 檔案 (在data資料夾底下的 ibdata*、ib_logfile* 檔案)。
5. 啟動 MySQL。
6. 匯入資料。

這樣佔用的空間應該會就縮減。但是之後還是會只增不減，所以比較方便的方式，是將 innodb_file_per_table 設定改為 1，讓每個 innoDB 資料表有自己獨立的檔案來儲存資料，之後便可以用 OPTIMIZE TABLE 指令來縮減資料表空間。不過修改 innodb_file_per_table 設定，只對之後新建立的資料表有效，所以若之前的 innoDB 資料表，也要使用新的設定，須將資料匯出後再匯入。
啟用 innodb_file_per_table 的步驟如下：

1. 使用  mysqldump 匯出全部 innoDB 資料表。
   以下是匯出全部資料庫的資料

   mysqldump -u root -p --events --routines --triggers --all-databases --default-character-set=utf8 > d:\backup.sql
   

   以下是匯出指定資料庫(例：abc)

   mysqldump -u root -p --events --routines --triggers --default-character-set=utf8 abc > d:\backup.sql

2. 刪除所有 innoDB 資料表。
3. 停止 MySQL。
4. 刪除 ibdata 和 ib_log 檔案 (在data資料夾底下的 ibdata*、ib_logfile* 檔案)。
5. 修改 MySQL 設定檔(my.ini 或 my.cnf)設定，開啟 innodb_file_per_table

   innodb_file_per_table=1

6. 啟動 MySQL。
7. 匯入資料。
   

   mysql --default-character-set=utf8 -u root -p < d:\backup.sql

   以下是匯入到指定資料庫(例：abc)

   mysql --default-character-set=utf8 -u root -p abc < d:\backup.sql

8. 如此在每個資料庫資料夾底下，innodb 資料表都會有自己的 *.ibd 檔案。

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

OPTIMIZE TABLE 資料表名稱 

參考：
Decreasing the Size of the InnoDB Tablespace
Reclaiming Disk Space with TRUNCATE TABLE
InnoDB Startup Options and System Variables
How to reclaim space in InnoDB when innodb_file_per_table is ON