Learning MySQL

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 inconvenient; 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, ...