How to Check and Repair MySQL Tables

The best way to avoid data corruption and damage problems in MySQL is to make sure that you have a regular backup schedule in place that makes a copy of all your MySQL data as often as possible, be that daily or even monthly.

That way, if and or when data become corrupted, restoration of a backup will bring the database back online with a minimum of data loss.

Alternatively however, database administrators should be aware of two MySQL options, myisamchk and the commands OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE.

Generally speaking, myisamchk is quicker and more efficient than the other SQL commands and for the purposes of this article, this is what we will examine.

To check a table that is proving problematic:

myisamchk /path/to/database/name/tablename.myi

The path to the database itself can vary according to platform and installation, but may be /var/lib/mysql/dbname/tablename or /usr/local/mysql/data/databasename/tablename.

You will get the output, something like:

Checking MyISAM file: /path/to/database/name/tablename.myi
Data records: 24573 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links

Note that you can use different levels of checking:

myisamchk --fast Perform a quick check, only verifying if the table handler closed successfully

myisamchk --medium-check Perform a faster check, by calculating a checksum for the indexes in each record and verifying that checksum against the index tree

myisamchk --extend-check Perform a thorough check of the table, verifying the data in each record

If the checking throws up error messages then you can attempt a repair. The first step is to shut down MySQL gracefully in order that no further changes can be made to the data tables. Then perform the recovery:

myisamchk --recover /path/to/database/name/tablename.myi

The --recover option rebuilds the table index file having removed deleted data records and invalid entries. If this fails, try:

myisamchk --safe-recover /path/to/database/name/tablename.myi

This is a lot slower than the standard --recover option but may yield better results.

External Links

• Using MySQL recovery utility to repair damaged MyISAM 5.0.4 table