High Performance MySQL, 4th Edition

Chapter 10. Backup and Recovery

If you don’t plan for backups up front, you might later find that you’ve ruled out some of the best options. For example, you might set up a server and then wish for LVM so that you can take filesystem snapshots—but it’s too late. You also might not notice some important performance impacts of configuring your systems for backups. And if you don’t plan for and practice recovery, it won’t go smoothly when you need to do it.

We won’t cover all parts of a well-designed backup and recovery solution in this chapter—just the parts that are relevant to MySQL. Here are some points we decided not to include here but that you should still absolutely be including in your overall backup and recovery strategy:

• Security (access to the backup, privileges to restore data, and whether the files need to be encrypted)

• Where to store the backups, including how far away from the source they should be (on a different disk, a different server, or offsite) and how to move the data from the source to the destination

• Retention policies, auditing, legal requirements, and related subjects

• Storage solutions and media, compression, and incremental backups

• Storage formats

• Monitoring and reporting on your backups

• Backup capabilities built into storage layers or particular devices, such as prefabricated file servers

Before we begin, let’s clarify some key terms. First, you’ll often hear about so-called hot, warm, and cold backups. People generally use these terms ...