32.1. Introduction

A MySQL administrator makes database backups to guard against the possibility of system crashes or hardware failures that may result in data loss or corruption. Backups also are useful when users remove databases or tables by mistake. Another use for backups is to move or copy databases to another server, such as when you migrate a MySQL installation from one machine to another or set up a replication slave server.

Backups can be made by copying database files directly, or by using programs designed for that purpose. Such programs include mysqldump, mysqlhotcopy, MySQL Administrator, and InnoDB Hot Backup.

It's necessary to make backups, but a backup is only one of the components needed for data recovery after loss or damage. The other is the binary log, which contains a record of data changes. To recover databases, you use the backup to restore them to their state at backup time, and then re-execute statements contained in the binary log that made data changes after the backup was created.

Here are some principles to keep in mind with regard to backups:

• Make backups regularly.

• Enable the binary log so that you have a record of changes made after a given backup.

• Flush the logs when backing up so that the server will begin a new binary log file that corresponds to the time of the backup. (That is, "checkpoint" the log to the backup.)

• Store your data directory and your backups on different physical devices so that a device failure cannot destroy both.

• Include your backups in your normal filesystem backup procedures so that you can recover the backup if necessary.