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


Also published on Medium.

Leave a Reply