MyISAM Index file Corruption repair method

With the MyISAM storage engine, when you create a table, three files appear: Data file myd, index file myi, format file frm. When the index file is corrupted or missing, you can fix it by using the following method. Here is the experimental process.

[mysql@testapp74 notify]$ MV Message_ignore. Myi Message_ignore. Myi.bak
[Mysql@testapp74 notify]$
[Mysql@testapp74 notify]$ Mysqld_safe &
[1] 26267
[mysql@testapp74 notify]$ 080630 14:19:03 mysqld_safe Logging to '/usr/mysql_data/log/alert.log '.
080630 14:19:03 Mysqld_safe starting mysqld daemon with databases From/usr/mysql_data/data
[Mysql@testapp74 notify]$
[Mysql@testapp74 notify]$
[Mysql@testapp74 notify]$
[Mysql@testapp74 notify]$ Vi/usr/mysql_data/log/alert.log
080630 14:18:14 [note]/usr/sbin/mysqld:normal shutdown
080630 14:18:14 [note] Event scheduler:purging the queue. 0 Events
080630 14:18:16 innodb:starting shutdown ...
080630 14:18:19 Innodb:shutdown completed; Log sequence number 162 783173592
080630 14:18:19 [note]/usr/sbin/mysqld:shutdown complete
080630 14:18:19 mysqld_safe mysqld from PID File/usr/mysql_data/data/testapp74.pid ended
080630 14:19:03 Mysqld_safe starting mysqld daemon with databases From/usr/mysql_data/data
080630 14:19:03 innodb:started; Log sequence number 162 783173592
080630 14:19:03 [note] Event scheduler:loaded 0 Events
080630 14:19:03 [note]/usr/sbin/mysqld:ready for connections.
Version: ' 5.1.23-rc-community ' socket: '/tmp/mysql.sock ' port:3306 MySQL community Edition (GPL)

As you can see from the above, when a table problems, the MySQL warning log does not appear any errors.

[Mysql@testapp74 notify]$ Mysql-uroot
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4
Server version:5.1.23-rc-community MySQL Community Edition (GPL)
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> Use Notify
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
Mysql>
Mysql>
Mysql>
Mysql>
Mysql> select * from Message_ignore limit 1; --When querying a table, an error is encountered
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 |
+-----------------------+-------+----------+----------------------------------------------+
2 rows in Set (0.03 sec)
Mysql>
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 |
+-----------------------+--------+----------+----------------------------------------------+
2 rows in Set (0.00 sec)

To recreate an index file using the format file frm

mysql> repair table message_ignore USE_FRM;
+-----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| notify.message_ignore | repair | status | OK |
+-----------------------+--------+----------+----------+
1 row in set (0.02 sec)
mysql>
mysql> check table message_ignore;
+-----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| notify.message_ignore | check | status | OK |
+-----------------------+-------+----------+----------+
1 row in set (0.02 sec)

After repair, the data can be queried correctlymysql> select * from message_ignore limit 1;
Empty set (0.00 sec)

If the format file frm corrupted, you can copy it from slave or restore from backup.