Database Repair and Maintenance

Every MySQL administrator hopes to avoid having to deal with corrupted or destroyed database tables. But just hoping won't keep problems from occurring. There are steps you can take 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 the table-checking and repair capabilities of isamchk and myisamchk. Know how to recover as well as possible from 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 provide procedures to follow so that you can carry out these responsibilities effectively. We'll discuss the MySQL data directory first; that's the 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