Chapter 11: Database Backups

Overview

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



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net