Backing up is one of the most important tasks for your database and one of the most neglected. Unpredictable events can be disastrous simply because of their unpredictability. Many have learned that the hard way by putting off a backup for some future date, which never seemed to come around, and then paying the price with a complete loss of data. The more important the data and the more frequent the changes, the more frequent the backup needs to be. For a news database, where changes are happening continually, a daily backup is prudent, with logging enabled to allow recovery of the day's work. For a small website where the data is changed weekly, a weekly backup makes more sense. However large or small your database, there's no avoiding it. A backup is a critical component of any data storage system.
I suggest also keeping a backup of your configuration file (my.cnf or my.ini), as the work you did tuning the server is also worth saving. I've been in the unpleasant situation of breezing through a restore from a disk failure, only to find that my lovingly crafted config file was lost.
This chapter shows various ways you can back up and restore with MySQL. Once you know the complexities and the possibilities involved in backing up, you'll be in a better position to implement the best strategy for your situation.
Featured in this chapter:
The BACKUP and RESTORE commands
Backing up by directly copying files
mysqldump
mysqlhotcopy
Using SELECT INTO to back up
Using LOAD DATA INFILE to restore
Security issues with LOAD DATA LOCAL
Using the binary update log
Backing up InnoDB tables
Replication as a means of backup