High Performance MySQL, 3rd Edition

Chapter 15. 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.

In contrast to the first and second editions of this book, we now assume most readers are using primarily InnoDB instead of MyISAM. 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:

• Security (access to the backup, privileges to restore data, 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

These topics are covered in books such as W. Curtis Preston’s Backup & Recovery (O’Reilly).

Before we begin, let’s clarify some key terms. First, you’ll ...