MyISAM 索引檔損毀修復實戰：REPAIR TABLE USE_FRM

MyISAM 三檔架構

建立 MyISAM 表會產生三個檔案：

• .MYD：資料檔
• .MYI：索引檔
• .frm：格式定義檔

模擬索引檔遺失與修復

1. 移除索引檔並重啟 MySQL

[mysql@testapp74 notify]$ mv message_ignore.MYI message_ignore.MYI.bak
[mysql@testapp74 notify]$ mysqld_safe &

錯誤日誌 alert.log 並未報錯，MySQL 正常啟動。

2. 查詢觸發錯誤

mysql> SELECT * FROM message_ignore LIMIT 1;
ERROR 1017 (HY000): Can't find file: 'message_ignore' (errno: 2)

mysql> CHECK TABLE message_ignore;
+-----------------------+-------+----------+----------------------------------------------+
| Table                 | Op    | Msg_type | Msg_text                                     |
+-----------------------+-------+----------+----------------------------------------------+
| notify.message_ignore | check | Error    | Can't find file: 'message_ignore' (errno: 2) |
| notify.message_ignore | check | error    | Corrupt                                      |
+-----------------------+-------+----------+----------------------------------------------+

3. 一般 REPAIR 失效

mysql> REPAIR TABLE message_ignore;
+-----------------------+--------+----------+----------------------------------------------+
| Table                 | Op     | Msg_type | Msg_text                                     |
+-----------------------+--------+----------+----------------------------------------------+
| notify.message_ignore | repair | Error    | Can't find file: 'message_ignore' (errno: 2) |
| notify.message_ignore | repair | error    | Corrupt                                      |
+-----------------------+--------+----------+----------------------------------------------+

4. 關鍵指令：USE_FRM 重建索引

mysql> REPAIR TABLE message_ignore USE_FRM;
+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| notify.message_ignore | repair | status   | OK       |
+-----------------------+--------+----------+----------+

mysql> CHECK TABLE message_ignore;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| notify.message_ignore | check | status   | OK       |
+-----------------------+-------+----------+----------+

mysql> SELECT * FROM message_ignore LIMIT 1;
Empty set (0.00 sec)

修復成功，資料可正常查詢。

補充：格式檔損毀處理

若 .frm 損毀，可從 Slave 複製或備份還原。