O'Reilly logo

Learning MySQL by Hugh E. Williams, Seyed M.M. Tahaghoghi

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required