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:

• 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.

• Database backups. In the event of a severe system crash, database backups are of crucial importance. You want to be able to restore your databases to the state they were in at the time of the crash with as little data loss as possible. Note that backing up your databases is not the same thing as performing general system backups (as is done, for example, by using the Unix dump program). The files corresponding to your database tables might be in flux due to server activity when system backups take place, so restoring those files will not give you internally consistent tables. The mysqldump program generates backup files that are more useful for database restoration, and it allows you to create backups without taking down the server. You might also need to move databases to a different location in the event of a full disk.

• 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 programs. Know how to recover data using your backup files and how to use the binary logs to recover changes that were made after your most recent backup.

• Database migration. If you decide to run a database on a faster host, you'll need to copy its contents to a different machine. You should understand the procedure for doing this, should the need arise. Database file contents might be machine dependent; if so, you can't just copy them from one system to another.

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.