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]

^[1] If Dr. Huntley ever reads this, Jeremy hopes he doesn't take it personally.

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.