Performing Database System Backups


Finally, we must address the issue of backups. It's a fact of life that hardware suffers failures, usually when least convenient or welcome. It's also a fact that running software written with the best of intentions can result in unintended consequences. All the most seasoned database veterans have war stories of times when they froze, stared dumbfounded at the screen, and then sank their heads into their hands, sobbing at the sight of an irretrievably dropped database.

MySQL and PostgreSQL don't have the "live" backup mechanisms that high-end commercial databases such as Oracle and DB2 do. (Many third-party backup software makers have applications that interact with the database, lock it piece by piece, and save backups to tape or across the network.) What they do have, however, is the ability to be locked and "dumped," like any properly designed database. The sections that follow look at a few of the tools that are necessary for creating usable backups.

Dumping the Database

MySQL has two methods for dumping its databases to disk: mysqldump and mysqlhotcopy.

Using mysqldump

mysqldump is a command-line tool that locks the database, preventing any new connections, and then copies the structures of all tables, followed by the contents of all tables, into STDOUT or an output data file. This file, whose name typically ends in .sql, is a long list of SQL commands that, if run in sequence, will re-create the database structure and then repopulate all the data. The mysqldump program must be run as a user who has access to the database; it is usually safe to do this as root, but it's a good idea to specify the user who owns each database with the -u user or --user=user option. You can also specify the user's password with the -p password or --password=password option. Generally, you will want to do this as a cron job, with the mysqldump command inside the owner-readableonly crontab file; but if you're concerned about local security, you can create a special "backup" user who has SELECT and LOCK TABLES access to all databases, but no other privileges. That way, if the user's password is intercepted, an attacker won't be able to do anything destructive.

The mysqldump command is executed like this:

[View full width]

# mysqldump --user="frank" --password="franks-password" PictureArchive > /usr/local/www/db /PictureArchive.sql


If you're running this as a cron job, you might want to precede this command with something such as mv /usr/local/www/db/PictureArchive.sql /usr/local/www/db/PictureArchive.sql-old and then a semicolon. This will move the old dump file out of the way so the new dump file can be created. Then it will run the dump, all in one command.

Using mysqlhotcopy

If you want to quickly make a snapshot of the entire database directory without creating a dump file, you can use mysqlhotcopy. This is a Perl script that locks the tables and copies all their associated files into a new specified location; if your database becomes corrupted or lost, you can simply shut down MySQL, move the files into their appropriate locations under /var/db/mysql, and restart the server. To run mysqlhotcopy, do the following:

[View full width]

# mysqlhotcopy --user="frank" --password="franks-password" PictureArchive /usr/local/www/ db/PictureArchive


Backing Up PostgreSQL Databases

As for PostgreSQL, the pgdump command is the equivalent of mysqlhotcopy. It is easiest to run pgdump as the pgsql user who owns the database:

# pgsql PictureArchive > /usr/local/www/db/PictureArchive.sql


Create pgsql's crontab file with crontab -u pgsql, as you saw in Chapter 15.

Restoring the Database

Restoring from a .sql file output from mysqldump or pgdump is conceptually very simple. It's just a "replay" of SQL commands, which theoretically will re-create the entire database in every detailfirst by rebuilding the tables and all their indexes and then by reinserting all the data.

To restore a MySQL database from a .sql file, you must first create the database (if it has been deleted or dropped) and then use the following command:

# mysql -p PictureArchive < /usr/local/www/db/PictureArchive.sql


You will be prompted for your password. (Alternatively, you can specify the frank user with -u or --user=, and frank's password with -p or --password=, as you saw earlier in this chapter.) After this is accepted, the .sql file will be read into the database, and through the use of SHOW TABLES and a few DESCRIBE table and SELECT queries, you should be able to verify that everything has been properly restored.

The process is almost exactly the same in PostgreSQL. First, use su pgsql to change to the pgsql user; then create the database (using createdb, as you saw earlier) if it doesn't already exist. Then start psql, reading in the .sql file:

$ psql < /usr/local/www/db/PictureArchive.sql


If all goes well, you should be back up and running.

Designing for Restorability

The backup/restore process isn't perfect, however. Because the tables and content are being re-created from a series of commands, and because those commands are automatically generated by the dump programs, it's conceivable that a configuration that you were able to set up manually cannot be re-created properly by the automated tools.

One potentially complicating factor, particularly in MySQL, is AUTO_INCREMENT. This special kind of INT column must be a primary key, UNIQUE, and NOT NULL by definition. Ideally you should never have to specify the values in an AUTO_INCREMENT column manually; you should end up with a different positive integer for every row in the table. However, you canif you really want togo into the database and manually specify that an entry in the table should have a value of 0. This can mess up attempts to restore the database.

When MySQL attempts to re-create this table, it will insert this row while explicitly specifying a value of 0 for the AUTO_INCREMENT column. However, the value of 0, as you will recall, is treated as a special case and reassigned to the maximum value of the column plus one. Therefore, if you have code in your web application that is hard-wired to treat this row a certain way because it has a unique ID of 0 (for example, if you have a table of preference settings containing a row of "defaults" that is identified by its special ID number of 0), it will not find that row there after the restore. As a result, your scripts will break; you'll have to examine the tables and reassign the value manually.

Of course, a better solution is to avoid messing with the values in an AUTO_INCREMENT column in the first place.

Most problems of this nature are such that you won't be able to see them coming, but the proper precautions you should have taken will seem blindingly obvious in hindsight. The best advice one can give on a subject like this is to encourage you to learn from your experience as rapidly as possiblenothing will rack up the UNIX guru points faster than an embarrassing failure.




FreeBSD 6 Unleashed
FreeBSD 6 Unleashed
ISBN: 0672328755
EAN: 2147483647
Year: 2006
Pages: 355
Authors: Brian Tiemann

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net