 |  |
4.4. Backup
A good backup strategy is by far the most important thing you can develop as an administrator. In particular, you will be really glad you have good backups if you ever have a system crash and need to restore your databases with as little data loss as possible. Also, if you ever accidentally delete a table or destroy a database, those backups will come in very handy.
Every site is different, so it is difficult to give specific recommendations on what you should do. You need to think about your installation and your needs. In this section, we present some general backup principles that you can adopt and cover the technical details of performing backups. With the information provided in this section, you should be able to devise a coherent strategy for your installation.
In general, there are a number of key backup considerations:
-
Store your backups on a device other than the database (either on another disk or perhaps a tape device), if possible. If your disk crashes, you'll be really happy to have the backups in a different place. If you are doing binary logging, store the binary logs on the same device as the backups.
-
Make sure you have enough disk space for the backups to complete.
-
Use binary logging in addition to backups, if appropriate, so you can restore your database with minimal data loss. If you choose not to use binary logging, you will only be able to recover your database to the point of your last backup. Depending on your application, a backup without binary logs might be useless.
-
Keep an adequate number of archived backups.
-
Test your backups before an emergency occurs.
The next sections describe two MySQL utilities for taking backups.
4.4.1. mysqldump
mysqldump is the MySQL utility provided for dumping databases. It basically generates an SQL script containing the commands (CREATE TABLE, INSERT, etc.) necessary to rebuild the database from scratch. The main advantage of this approach over the direct copy (mysqlhotcopy) is that output is in a portable ASCII format that can be used across hardware and operating systems to rebuild a database. Also, because the output is an SQL script, it is possible to recover individual tables.
To use mysqldump to back up your database, we recommend that you use the -opt option. This turns on -quick, --add-drop-table, --add-locks, --extended-insert, and --lock-tables. This option should give you the fastest possible dump of your database. The option --lock-tables locks all the tables in the database, so the database will essentially be offline while you are doing this.
Your command will look something like this:
$ mysqldump --opt test > /usr/backups/testdb
This command dumps the test database into the file /usr/backups/testdb. If you are using binary logging, you will also want to specify --flush-logs, so the binary logs get checkpointed at the time of the backup:
$ mysqldump --flush-logs --opt test > /usr/backups/testdb
mysqldump has a number of other options for customizing your backup. For a list of all the available options for mysqldump, type mysqldump --help.
4.4.2. mysqlhotcopy
mysqlhotcopy is a Perl script that uses a combination of LOCK TABLES, FLUSH TABLES, and Unix cp to perform a fast backup of the database. It simply copies the raw database files to another location. Because it does only a file copy, it is much faster than mysqldump. And because the copy is in native format, the backup is not portable to other hardware or operating systems, except for MyISAM tables, which are portable. Also, mysqlhotcopy can be run only on the same host as the database, whereas mysqldump can be executed remotely.
To run mysqlhotcopy, type:
$ mysqlhotcopy test /usr/backups
This command creates a new directory in the /usr/backups directory that has a copy of all the data files in the test database.
If you are using binary logging, you will also want to specify --flushlog, so the binary logs get checkpointed at the time of the backup.
|  | |
| 4.3. Logging |  | 4.5. Recovery |
Copyright © 2003 O'Reilly & Associates. All rights reserved.