How to repair crashed tables in MySQL if auto repair fails

Here is what you should do if a table crashes and auto repair after mysql restart fails.

1. Open a terminal / command line utility
2. If crash is on remote server you will need to ssh first
3. service mysql stop
4. cd /var/lib/mysql/{your database name}
5. myisamchk -r {table name}
   

   – recovering (with sort) MyISAM-table ‘{table name}’

   Data records: 1686809

   myisamchk: error: Can’t create new tempfile: ‘{table name}.TMD’

   MyISAM-table ‘{table name}’ is not fixed because of errors

   Try fixing it by using the –safe-recover (-o), the –force (-f) option or by not using the –quick (-q) flag

6. myisamchk -r -v -f {table name}
   

   – recovering (with sort) MyISAM-table ‘{table name}’

   Data records: 1686809

   – Fixing index 1

     – Searching for keys, allocating buffer for 9434 keys

     – Merging 1686809 keys

     – Last merge and dumping keys

   – Fixing index 2

     – Searching for keys, allocating buffer for 30801 keys

     – Merging 1686809 keys

     – Last merge and dumping keys

7. service mysql start

Hope this helps