Chapter 13. Database Maintenance and Repair

Ideally, MySQL runs smoothly from the time that you first install it. But problems sometimes do occur for a variety of reasons, ranging from power outages to hardware failure to improper shutdown of the MySQL server (such as when you terminate it with kill –9 or the machine crashes). Circumstances such as these, many of which are beyond your control, can result in damage to database tables, typically caused by incomplete writes in the middle of a change to a table.

This chapter focuses on detecting and solving problems with your tables, regardless of how those problems arise. For table checking and repair, the MySQL administrator's best friends are the myisamchk and isamchk utilities. These two programs have several capabilities. We've already discussed how to use them to perform index key distribution analysis and index deactivation and reactivation in Chapter 4, "Query Optimization." You can also use them for checking tables and repairing those that have problems. This enables you to correct damage before it becomes worse, possibly making tables unusable.

For a full listing of the options supported by myisamchk and isamchk, see Appendix E, "MySQL Program Reference." For additional background, see the chapter "Maintaining a MySQL Installation" in the MySQL Reference Manual.