Table Maintenance and Crash Recovery (Managing and Using MySQL, 2nd Edition)

4.6. Table Maintenance and Crash Recovery

Database tables can get out of whack when a write to the data file is not complete. This can happen due to a variety of reasons, such as a power failure or a non-graceful shutdown of the MySQL server.

MySQL provides two mechanisms for detecting and repairing table errors: myisamchk /isamchk and mysqlcheck. It is a wise practice to perform these checks regularly. Early detection may increase your chances of recovering from errors successfully.

mysqlcheck is new with Version 3.23.38 of MySQL. The main difference between myisamchk/isamchk and mysqlcheck is that mysqlcheck enables you to check or repair tables while the server is running. myisamchk/isamchk require that the server not be running.

myisamchk and isamchk are quite similar. They provide the same functions. The only difference is that myisamchk is used on MyISAM tables, and isamchk is used on ISAM tables. mysqlcheck can be used only with MyISAM tables.

4.6.1. Checking a Table

If you suspect errors on a table, the first thing you should do is use one of the utilities to check it out. You can tell what kind of table you are dealing with by looking at the extension of the data file. An extension of .MYI tells you it is a MyISAM table, and .ISM indicates an ISAM table. As we said earlier, myisamchk and mysqlcheck are used only with .MYI files, and isamchk with .ISM files.

Assume we have a database called test with two tables: table1, which is an ISAM table, and table2, which is a MyISAM table. First, check your table, using the appropriate utility. If you use myisamchk or isamchk, make sure your MySQL server is not running to prevent the server from writing to the file while you are reading it.

$ myisamchk table2.MYI 
Data records:       0   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
$ isamchk table1.ISM
Checking ISAM file: table1.ISM
Data records:       0   Deleted blocks:       0
- check file-size
- check delete-chain
- check index reference
$ mysqlcheck test table2
test.table2                                        OK

This output shows that there are no errors in either of the tables.

The default method is usually adequate for detecting errors. However, if no errors are reported but you still suspect damage, you can perform an extended check using the --extend-check option with myisamchk/isamchk or the --extend option with mysqlcheck. This will take a long time, but is very thorough. If the extended check does not report any errors, you are in good shape.

4.6.2. Repairing a Table

If the check reported errors on a table, you can try to repair them.

If you are using myisamchk or isamchk, make sure your MySQL server is not running when you attempt the repair. Also, it is a good idea to back up the data files before attempting a repair operation in case something goes haywire.

With myisamchk/isamchk, you should first try the --recover option:

$ isamchk  --recover table1.ISM
- recovering ISAM-table 'table1.ISM'
Data records: 3
$ myisamchk  --recover table2.MYI
- recovering (with keycache) MyISAM-table 'table2.MYI'
Data records: 2

If this fails for some reason, you can try --safe-recover, a slower recovery method that can fix some errors --recover cannot.

$ isamchk  --safe-recover table1.ISM
- recovering ISAM-table 'table1.ISM'
Data records: 3
$ myisamchk  --safe-recover table2.MYI
- recovering (with keycache) MyISAM-table 'table2.MYI'
Data records: 2

With mysqlcheck, your only recovery option is --repair.

$ mysqlcheck  --repair test table2
test.table2                                        OK

If these operations fail, your only remaining option is to restore the table from your backups and binary logs. See Section 4.4 and Section 4.5 for more information.

4.6.3. Scheduled Table Checking

We recommend that you take steps to perform regularly scheduled table checks on your database files. This can be done by wrapping isamchk/myisamchk/mysqlcheck commands into a script that is executed periodically from cron or some other scheduling software.

You may also want to modify your system boot procedure to check tables at boot time. This is especially useful when you reboot the system after a system crash.