Chapter 15. Backing Up Databases

Performing regular database backups is one of the hardest tasks that lies before today's system administrator. The primary reason is that databases are infinitely larger and more complex than simple filesystem files. In order to properly back up a database, you first need to:

Understand the internal structure of your database

Understand the available utilities

Have an excellent working relationship between system administrators, storage administrators, and database administrators

Once you've accomplished all of that, you need to choose among your various options:

Shut down the database and back up its files "cold."

Put the database in a mode that allows its files to be backed up live, or "hot."

Use its built-in tool to back up to disk or tape without a commercial utility.

Buy an agent for a commercial utility that will back it up to its devices.

Almost anyone who reads this list will find at least one of these steps daunting. Many people work with databases that operate 24 hours a day, 7 days a week. They can't shut them down for hours at a time to back them up. Even if they could, if a database uses raw devices, it can't be backed up with a filesystem backup utility. Of course, dd would work on Unix, but that would mean doing one thing for filesystems and a different thing for databases. A common theme throughout this book is that different is bad. Every special case is a chance for failure. It's something else you have to code for, something else you have to watchsomething else to break. The result is that database backups are not easy.

Part of the problem is the design process of the actual database engine itself. Historically, the need for bigger storage and faster queries drove the design of a product to develop much more than its ability to back itself up. Over the last few years, databases have grown from a gigabyte or so to well beyond several terabytes. This growth in size and performance happened because the customer base screamed for it. Unfortunately, they weren't simultaneously screaming for a backup utility to support those huge databases.