High Performance MySQL

Chapter 9. Backup and Recovery

Ask your favorite system administrator what the least favorite part of her job is and there’s a good chance she’ll mutter, “backups,” with a sullen look on her face. Running backups ranks right up there with a visit to the dentist on most people’s list of least fun things to do.^[1]

If you already rely on standard backup software to handle your MySQL servers, you probably have a false sense of security about backups. There aren’t many popular backup tools that know how to back up MySQL properly, so that there is no corruption, half-committed transactions, or other assorted problems.

In this chapter we’ll begin by considering why you need backups in the first place. Then we’ll examine the issues that arise when trying to back up a running database server, including a look at why most backup software isn’t well suited to MySQL backups. That leads to a discussion of the various backup-related tools for MySQL and how you can put them to use. Finally, we’ll consider what’s involved in creating a custom backup script.

Most of the how-to material is in the second half of the chapter. Much of the initial discussion revolves around understanding your backup options and how to go about selecting the right one.

Why Backups?

Strangely, some people never stop to consider why they need to back up their servers. The data is important, so we just assume that backing it up is equally important. That’s good, because backups are important and do need to be done. But by ...