SQL - MySQL InnoDB Table Space 的異常回復

[Admin1]

簡單的說有幾個步驟:

1. 把 MySQL Server 中所有的 InnoDB Table 全部 Dump 出來，也就是用 mysqldump 先備份起來
2. DROP 所有的 InnoDB Table
3. Shutdown MySQL Server
4. 刪除 MySQL Server 資料夾中所有的 InnoDB Table Space，包含 ibdata*、 ib_logfile* 等檔案
5. 重新啟動 MySQL Server，此時系統會自動對 InnoDB Table Space 進行 initialize
6. Restore 之前備份的 InnoDB Table

接下來是比較麻煩的部份，也就是實際在處理的時候會遇到的問題。



問題一：要怎麼找出 MySQL Server 中所有的 InnoDB Table?
答：

可從 MySQL Server 中內建的資料庫 information_schema 查詢出來。在 information_schema 這個 database 中包含了 MySQL Server 中所有的資料庫的 Meta-Data，我們可以使用以下的 SQL 指令輕易的查出目前系統中所有的 InnoDB Table：

SELECT table_schema, table_name FROM information_schema.tables WHERE ENGINE='InnoDB'

其中 table_name 即為資料表的名稱，table_schema 則為該 table 所屬的資料庫名稱，若是要找出某個特定資料庫下所有的 InnoDB Table 則只要再加上 table_schema 這個條件即可，例如：

SELECT table_schema, table_name FROM information_schema.tables WHERE engine='InnoDB' AND table_schema='some_db'

問題二：找出所有的 InnoDB Table 後該怎麼備份？
答：

請愛用 mysqldump 指令，請參考『MySQL 資料庫的備份與還原』(http://forum.slime.com.tw/thread208592.html)。若是要備份的 Table 數量非常多，對各別的 table 進行備份則會非常沒有效率，此時可以改變一下 mysqldump 指令的用法，例如：

mysqldump database_name table1 table2 table3 > backup.sql

這個指令的意思是說，要將 database_name 這個資料庫下的 table1, table2, table3 全部 dump 到 backup.sql 這個檔案中。當然，若要備份的 table 真的很多時用 command-line 輸入所有的 table name 也是一件很麻煩的事，這時不妨以您熟悉的程式語言讓電腦自己去把指令組出來即可，例如以 PHP 來實作的話：

PHP 語法:

$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='some_db' AND engine='InnoDB'";
$result = mysql_query($sql, $conn);
$table_str = "";
while($row = mysql_fetch_assoc($result)) {
  $table_str = $table_str . $row['table_name'] . " ";
}
$cmd = "mysqldump --skip-extended-insert some_db " . $table_str . " > innodb.sql";
// 最後的 %cmd 變數中就包含了組合好的指令。 


問題三：如果在未 Shutdown MySQL Server 的情況下刪除了 InnoDB Table Space 的話會發生什麼事？
答：此時可能會有 MySQL Server 無法順利 Shutdown 的情形發生，這時請使用 kill 指令將 mysql daemon 刪除，或是直接 reboot MySQL Server 所在的伺服器。



問題四：該如何知道 InnoDB Table Space 存放在檔案系統中的什麼地方？
答：

請查閱 MySQL Server 的設定檔(eg. /etc/my.cnf) 中有關於 InnoDB 的設定，例如：

PHP 語法:

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/ 
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50 


從範例中可以看出是放在 /var/lib/mysql/ 目錄下。