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 force-terminate it with kill -9 or when the server host 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 if 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 quite similar to backup techniques.

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

• Enable any optional auto-recovery capabilities that the MySQL server provides.

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

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

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 corrected by using MySQL's table repair capabilities.

• For circumstances under which table checking and repair isn't sufficient to get you up and running, perform data recovery using your backups and your binary logs. Begin by using 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, transactional storage engines can perform auto-recovery. You can also enable automatic table repair for the MyISAM storage engine. These capabilities are useful when the server restarts after a crash.

• Use the mysqldump and mysqlhotcopy programs to make backups of your databases, should you need to recover them later.

• You can tell the server to perform table maintenance operations on demand by means of SQL statements such as CHECK TABLE and REPAIR TABLE. For a command-line interface to these statements, use the mysqlcheck program. The myisamchk utility also can check tables for problems and perform various corrective actions on them.

Some of these programs, such as mysqlcheck and mysqldump, work in cooperation with the server. They connect to the server and issue SQL statements that instruct the server what kind of table maintenance operation to perform. By contrast, myisamchk operates directly on the files used to represent tables. However, because the server also accesses those files while it runs, myisamchk acts in effect as a competitor to the server. This means that you must take steps to prevent myisamchk and the server 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 write to 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 repairs. Therefore, the chapter begins by describing how to keep the server at bay when necessary. After that, it discusses how to prepare for problems, how to make backups, and how to use repair and recovery techniques if necessary.

Under Unix, operations that require you to directly work with table files or other files under the data directory should be performed while you're logged in as the MySQL administrator so that you have permission to access the files. In this book, the name of that login account is mysql. 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 SQL statements and programs discussed in this chapter, see Appendix E, "SQL Syntax Reference," and Appendix F, "MySQL Program Reference."