Learning Mysql

PART III
Advanced Topics

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 shutdown 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. Fora home user, this is inconvenient fora 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 recreate a damaged mysql database.

Download 4.24 Mb.

Share with your friends: