Managing & Using MySQL, 2nd Edition

Backup

A good backup strategy is by far the most important thing you can develop as an administrator. In particular, you will be really glad you have good backups if you ever have a system crash and need to restore your databases with as little data loss as possible. Also, if you ever accidentally delete a table or destroy a database, those backups will come in very handy.

Every site is different, so it is difficult to give specific recommendations on what you should do. You need to think about your installation and your needs. In this section, we present some general backup principles that you can adopt and cover the technical details of performing backups. With the information provided in this section, you should be able to devise a coherent strategy for your installation.

In general, there are a number of key backup considerations:

• Store your backups on a device other than the database (either on another disk or perhaps a tape device), if possible. If your disk crashes, you’ll be really happy to have the backups in a different place. If you are doing binary logging, store the binary logs on the same device as the backups.

• Make sure you have enough disk space for the backups to complete.

• Use binary logging in addition to backups, if appropriate, so you can restore your database with minimal data loss. If you choose not to use binary logging, you will only be able to recover your database to the point of your last backup. Depending on your application, a backup without binary logs might be useless.

• Keep an adequate number of archived backups.

• Test your backups before an emergency occurs.

The next sections describe two MySQL utilities for taking backups.