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:
If table damage or data loss does occur despite your efforts, exercise your options for dealing with such problems:
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:
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.