Database Repair and Maintenance

Every MySQL administrator hopes to avoid having to deal with corrupted or destroyed database tables. But hope alone won't keep problems from occurring. You should take steps to minimize your risks and learn what to do if bad things do happen:

Crash recovery. Should disaster strike in spite of your best efforts, you should know how to repair or restore your tables. Crash recovery should be necessary only rarely, but when it is, it's an unpleasant, high-stress business (especially with the phone ringing and people knocking on the door while you're scrambling to fix things). Nevertheless, you must know how to do it because your users will be quite unhappy otherwise. Be familiar with MySQL's table-checking and repair utilities. Know how to recover data using your backup files and how to use the update logs to recover changes that were made after your most recent backup.

Preventive maintenance. A regular program of preventive maintenance should be put in place to minimize the likelihood of database corruption or damage. You should also be making backups, of course, but preventive maintenance reduces the chance that you'll need to use them.

The preceding outline summarizes the responsibilities you undertake by becoming a MySQL administrator. The next few chapters discuss them in more detail and describe procedures to follow so that you can carry out these responsibilities effectively. We'll discuss the MySQL data directory first; that's the primary resource you're maintaining, and you should understand its layout and contents. From there we move on to general administrative duties, a discussion of MySQL's security system, and maintenance and troubleshooting.