Backups and Recovery - Learning MySQL - page 353

CHAPTER 10
Backups and Recovery
If you suffer a crippling attack, or your server has technical problems, you should have
backups that allow you to quickly get a server up and running with relatively up-to-
date data. The simplest way to create backups is to shut down the MySQL server and
make a copy of the data directory (we listed common locations for the data directory
in “Server Doesn't Start” in Chapter 2) to a secure location, and copy it back if required.
With a Windows system, you can right-click on the data directory folder and select the
menu option to create a compressed folder. On a Linux or Mac OS X system, you can
make a compressed package of all the databases on the server by typing:
# tar zcf /tmp/`date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.tgz mysql_data_directory
The backup file is created in the /tmp directory. The segment `date +"%Y.%m.%d.%H.
%M"` is a trick to include a timestamp in the filename. The resulting compressed file will
have a name like 2006.08.16.06.08.MySQL_Backup.tgz ; an explicit record of the
backup date and time is very useful when you need to recover data from a particular
point in time.
The MySQL server must be stopped when you make a backup in this way, since you
want the files on disk to be up-to-date and consistent. For a home user, this is incon-
venient; for a production database, such downtime can be very disruptive and should
be avoided when possible.
In this chapter, we explain alternative approaches to backing up and restoring your
MySQL databases, and how to configure regular automatic backups. We also explain
how to check and repair damaged database tables. Finally, we show how you can re-
create a damaged mysql database.
Dumping a Database as SQL Statements
You can make a database backup by generating a file of all the SQL commands neces-
sary to re-create the existing database structure from scratch, and (if you want) the SQL
commands to insert all the data. Note that this is different from exporting table contents
using the SELECT INTO OUTFILE syntax that we saw in “Writing Data into Comma-