Chapter 13. 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 and database administrators

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

  • Buy an expensive commercial utility

  • Find or write your own utility

  • Perform cold backups without a utility

Almost anyone who reads this list will find at least one of these steps daunting. Many people work with databases that operate 24 hours, seven 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 regular dump. Of course dd would work, 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 watch—something 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 much more than its ability to back itself up. (This goes for filesystems too. Most Unix vendors have added support for a multiple-terabyte filesystem that break the 2-GB file-size barrier, but at least one man page for dump says “WARNING: dump will not back up a filesystem containing large files.”) Over the last few years, databases have grown from a gigabyte or so to an average size that is daily growing closer to a terabyte. 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.

Can It Be Done?

Think about database backups from a big-picture perspective, comparing them to filesystem backups. There are a number of good backup utilities on the market now. Why aren’t there just as many for database backups? There is certainly a demand for it.

One of the reasons is the complexity of the task. In order to release a database backup product, a company would need to consider several factors:

Multiple moving targets

How do you get a database to hold still? Have you ever tried to take a picture of 100 people? Designing a backup utility for a database is very hard, since you have to “take a picture” of hundreds of files all at once.

Interrelationship between the files

A database backup program needs to understand all of the database elements and how they relate.

Necessity to work with the database engine

This is essential. If the database understands that you are running a backup, it can help you. If you don’t interface with the database, you’ll be backing up blind.

The size of the job

How do you get one terabyte of data to a backup drive in one hour? That’s what some of the backup requirements are! The only answer to that is a multi-threaded backup program. (See Section 5.6 in Chapter 5 for more details.)

Recoverability versus cost

You need all of the preceding, but you don’t want to mortgage your house to get it.

Differing levels of automation

Different customers want different levels of automation. Some want everything managed by the library, while others would rather do it themselves.

With these kinds of requirements, is there any hope of getting commercial utilities that are up to the challenge? The answer is “Yes!” Database companies have finally recognized that backup utilities do have an effect on the overall sales of a database engine. They have finally started producing good utilities that interface with other commercial backup products. Vendors have even taken an active lead in making sure that customers use a utility that works properly on both the backup and the restore sides. Now that there are decent backup utilities, though, you have another problem—confusion.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.189.180.76