Chapter 13. Database Backups, 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). Events 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 describes what you can do to minimize your risks and to be ready in case disaster strikes anyway. The techniques covered here include making database backups, performing table checking and repair operations, and how to use recovery procedures in case you do lose data. The chapter also covers database copying procedures for transferring a database to another server, because these are often are quite similar to backup techniques.

To prepare in advance for problems, take the following actions:

• Use the MySQL server's auto-recovery capabilities.

• Set up a database backup schedule. Should the worst occur and you be faced with catastrophic system failure, you'll need the backups in order to perform recovery operations. Enable your binary log, too, so that you have a record of updates that were made after the backup. The overhead associated with binary logging is negligible (perhaps 1%), so there is little reason not to enable it.

• Set up scheduled preventive maintenance that performs table checking periodically. Routine table-checking procedures can help you detect and correct minor problems before they become worse.

If table damage or data loss does occur despite your efforts, exercise your options for dealing with such problems:

• Check your tables and then fix any that are found to be corrupt if possible. Minor damage often can be taken care of with MySQL's table repair capabilities.

• For circumstances under which table checking and repair isn't sufficient to get you up and running again, perform data recovery using your backups and your binary update logs. First, use the backups to restore your tables to their state at the time of the backup. After that, use the logs to re-apply any updates that were made after the backup, to bring your tables to their state when the crash occurred.

The tools at your disposal for carrying out these tasks include the capabilities of the MySQL server itself and also several other utilities included in the MySQL distribution:

• When the server starts up, it has the ability to check tables for problems and correct many of them. This is useful when you restart the server after a crash. Some of the checks are automatic and others can be enabled at your option.

• The mysqldump and mysqlhotcopy programs help you make backups of your databases to be used should you need to recover them later.

• You can tell the server to perform several types of table maintenance and repair operations by means of SQL statements such as CHECK TABLE and REPAIR TABLE. The mysqlcheck utility provides a command line interface to these statements.

• Another way to check tables for problems and perform various corrective actions on them is to use the myisamchk and isamchk utilities.

Some of these programs work in cooperation with the server, such as mysqlcheck and mysqldump. They connect to the server and issue SQL statements to tell the server what kind of table checking or backup operation to perform. Others, like myisamchk and isamchk, operate directly on the files used to represent tables. However, because the server also accesses those files while it runs, such utilities act in effect as competitors to the server, and you must take steps to prevent them from interfering with each other. For example, if you're repairing a table with myisamchk, it's necessary to keep the server from trying to access the table at the same time. Failure to do so can result in much worse problems than those you're trying to correct!

The need to cooperate with the server arises in connection with several of the administrative tasks discussed in this chapter, from making backups to performing table repair. Therefore, the chapter begins by describing how to keep the server at bay when necessary. After that it discusses how to prepare for problems, and then how to use repair and recovery techniques if necessary.

Under UNIX, operations that require you to directly access table files or other files under the data directory should be performed while logged in as the MySQL administrator, so that you have permission to use the files. In this book, the name of that account is mysqladm. It's also possible to access the files as root, but in that case, make sure when you're done that any files you work with have the same mode and ownership as when you began.

For a full listing of the options supported by the statements and programs discussed in this chapter, see Appendix D, "SQL Syntax Reference," and Appendix E, "MySQL Program Reference."