Backing Up and Copying Databases
It's important to back up your databases in case tables are lost or damaged. If a system crash occurs, you want to be able to restore your tables to the state they were in at the time of the crash with as little data loss as possible. Likewise, a user who issues an unwise DROP DATABASE or DROP TABLE will likely show up at your door requesting that you perform data recovery. Sometimes it's the MySQL administrator who causes the damage. Administrators have been known to damage table files by trying to edit them directly using an editor such as vi or emacs. This is certain to do bad things to a table!
The two main methods for backing up databases are to use the mysqldump program or to directly copy database files (for example, with cp, tar, or cpio). Each method has its own advantages and disadvantages:
mysqldump operates in cooperation with the MySQL server. Direct-copy methods are external to the server, and you must take steps to ensure that no clients are modifying the tables while you copy them. This is the same problem that occurs if you try to use file system backups to back up databases: If a database table is being updated during the file system backup, the table files that go into the backup are in an inconsistent state and are worthless for restoring the table later. The difference between file system backups and direct-copy files is that for the latter you have control over the backup schedule, so you can take steps to make sure the server leaves the tables alone.
mysqldump is slower than direct-copy techniques.
mysqldump generates text files that are portable to other machines, even those with a different hardware architecture. Direct-copy files are not portable to other machines unless the tables you're copying use the MyISAM storage format. ISAM tables can be copied only between machines of like hardware architecture. For example, copying files from Solaris on SPARC to Solaris on SPARC will work, but copying files from Solaris on SPARC to Solaris on Intel or vice-versa will not work. The MyISAM table storage format introduced in MySQL 3.23 solves this problem because the format is machine independent. Therefore, directly copied files can be moved to a machine with a different hardware architecture if two conditions are satisfied: The other machine must also be running MySQL 3.23 or later, and the files must represent MyISAM tables, not ISAM tables.
Whichever backup method you choose, there are certain principles to which you should adhere to assure the best results if you ever need to restore database contents:
Perform backups regularly. Set a schedule and stick to it.
Tell the server to perform update logging (see the earlier section, "Maintaining Log Files"). Update logs help you when you need to restore a database after a crash. After you use your backup files to restore a database to the state it was in at the time of the backup, you can reapply the changes that were made subsequent to the backup by running the queries in the update logs. This restores the tables in the database to their state at the time the crash occurred.
In the language of file system backups, database backup files represent full dumps, and update logs represent incremental dumps.
Use a consistent and understandable backup file-naming scheme. Names like backup1, backup2, and so forth are not particularly meaningful. When it comes time to perform your restore, you'll waste time figuring out what's in the files. You may find it useful to construct backup filenames using database names and dates. For example:
% mysqldump samp_db > /usr/archives/mysql/samp_db.1999-10-02 % mysqldump menagerie > /usr/archives/mysql/menagerie.1999-10-02
You might want to compress backup files after you generate them. Backups tend to be large, after all! You'll also need to expire your backup files to keep them from filling your disk, just like you expire your log files. See the section "Maintaining Log Files" for information on log file expiration. You can use the same techniques to expire your backup files.
Back up your backup files using file system backups. If you have a complete crash that wipes out not only your data directory but also the disk drive containing your database backups, you'll be in real trouble. Back up your update logs, too.
Put your backup files on a different file system than the one you use for your databases. This reduces the likelihood of filling up the file system containing the data directory as a result of generating backups.
The techniques that are used for creating backups are also useful for copying databases to another server. Most commonly, a database is transferred to a server running on another host, but you can also transfer data to another server running on the same host. You might do this if you're running a server for a new release of MySQL and want to test it with some real data from your production server. Or you might have gotten a new machine and want to move all your databases onto it.
Using mysqldump to Back Up and Copy Databases
When you use the mysqldump program to generate database backup files, by default the contents of the file consist of CREATE TABLE statements that create the tables being dumped and INSERT statements containing the data for the rows in the tables. In other words, mysqldump creates output that can be used later as input to mysql to re-create the database.
You can dump an entire database into a single text file like this:
% mysqldump samp_db > /usr/archives/mysql/samp_db.1999-10-02
The beginning of the output file looks like this:
# MySQL dump 6.0 # # Host: localhost Database: samp_db #-------------------------------------------------------- # Server version 3.23.2-alpha-log # # Table structure for table 'absence' # CREATE TABLE absence ( student_id int(10) unsigned DEFAULT '0' NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (student_id,date) ); # # Dumping data for table 'absence' # INSERT INTO absence VALUES (3,'1999-09-03'), INSERT INTO absence VALUES (5,'1999-09-03'), INSERT INTO absence VALUES (10,'1999-09-06'), …
The rest of the file consists of more INSERT and CREATE TABLE statements.
If you want to compress the backup as you generate it, use a command like this instead:
% mysqldump samp_db | gzip > /usr/archives/mysql/samp_db.1999-10-02.gz
If you have a huge database, the output file also will be huge and may be difficult to manage. If you like, you can dump the contents of individual tables by naming them after the database name on the mysqldump command line. This partitions the dump into smaller, more manageable files. The following example shows how to dump some of the samp_db tables into separate files:
% mysqldump samp_db student score event absence > gradebook.sql % mysqldump samp_db member president > hist-league.sql
If you're generating backup files that are intended to be used to periodically refresh the contents of another database, you may want to use the --add-drop-table option. This tells mysqldump to write DROP TABLE IF EXISTS statements into the backup file. Then, when you take the backup file and load it into the second database, you won't get an error if the tables already exist. If you're running a second test server, you can use this technique to periodically reload it with a copy of the data from the databases on your production server.
If you are dumping a database so that you can transfer the database to another server, you don't even need to create backup files. Make sure that the database exists on the other host, then dump the database using a pipe so that mysql reads the output of mysqldump directly. For example, if you want to copy the samp_db database from pit-viper.snake.net to boa.snake.net, that's easily done like this:
% mysqladmin -h boa.snake.net create samp_db % mysqldump samp_db | mysql -h boa.snake.net samp_db
Later, if you want to refresh that database on boa.snake.net again, skip the mysqladmin command, but add the --add-drop-table to the mysqldump to avoid getting errors about tables already existing:
% mysqldump --add-drop-table samp_db | mysql -h boa.snake.net samp_db
Other useful mysqldump options include the following:
The combination of --flush-logs and --lock-tables is helpful for checkpointing your database. --lock-tables locks all the tables that you're dumping, and --flush-logs closes and reopens the update log file. If you're generating sequenced update logs, the new update log will contain only queries that modify databases from the point of the backup. This checkpoints your update log to the time of the backup. (Locking all the tables is not so good for client access during the backups if you have clients that need to perform updates, however.)
If you use --flush-logs to checkpoint the update logs to the time of the backup, it's probably best to dump the entire database. If you dump individual files, it's harder to synchronize update log checkpoints against your backup files. During restore operations, you usually extract update log contents on a per-database basis. There is no option for extracting updates for individual tables, so you'll have to extract them yourself.
By default, mysqldump reads the entire contents of a table into memory before writing it out. This isn't really necessary, and in fact is almost a recipe for failure if you have really large tables. You can use the --quick option to tell mysqldump to write each row as soon as it has been retrieved. To further optimize the dump process, use --opt instead of --quick. The --opt option turns on other options that speed up dumping the data and reading it back in.
Performing backups using --opt is probably the most common method because of the benefits for backup speed. Be warned, however, that the --opt option does have a price; what --opt optimizes is your backup procedure, not access by other clients to the database. The --opt option prevents anyone from updating any of the tables that you're dumping by locking all the tables at once. You can easily see for yourself the effect of this on general database access. Just try running a backup at the time of day when your database is normally most heavily used. It won't take long for your phone to start ringing with people calling to find out what's going on. (I'd appreciate it if you would refrain from asking yourself how it is that I happen to know this.)
An option with something of the opposite effect of --opt is --delayed. This option causes mysqldump to write INSERT DELAYED statements rather than INSERT statements. If you are loading a data file into another database and you want to minimize the impact of this operation on other queries that may be taking place in that database, --delayed is helpful for achieving that end.
The --compress option is helpful when you're copying a database to another machine because it reduces the number of bytes travelling over the network. Here is an example; notice that the --compress option is given for the program that communicates with the server on the remote host, not the one that communicates with the local host:
% mysqldump --opt samp_db | mysql --compress –h boa.snake.net samp_db
mysqldump has many other options. Consult Appendix E for more information.
Using Direct-Copy Database Backup and Copying Methods
Another way to back up a database or tables that doesn't involve mysqldump is to copy table files directly. Typically this is done using utilities such as cp, tar, or cpio. The examples in this section use cp.
When you use a direct-copy backup method, you must make sure the tables aren't being used. If the server is changing a table while you're copying it, the copies will be worthless.
The best way to ensure the integrity of your copies is to bring down the server, copy the files, and restart the server. If you don't want to bring down the server, you should refer to Chapter 13. Take a look at the techniques described there for locking the server while performing table checking. If the server is running, the same constraints apply to copying files, and you should use the same locking protocol to keep the server quiet.
Assuming that the server is either down or that you've locked the tables you want to copy, the following example shows how to back up the entire samp_db database to a backup directory (DATADIR represents the server's data directory):
% cdDATADIR % cp -r samp_db /usr/archive/mysql
Individual tables can be backed up like this:
% cdDATADIR/samp_db % cp member.* /usr/archive/mysql/samp_db % cp score.* /usr/archive/mysql/samp_db …
When you're done backing up, you can restart the server (if you brought it down) or release the locks you have on the tables (if you left the server running).
To use direct-copy files to copy a database from one machine to another, just copy the files to the appropriate database directory on the other server host. Make sure the files are for MyISAM tables or that both machines have the same hardware architecture, though. Your tables may appear to have very strange contents on the second host otherwise. You should also make sure the server on the second host doesn't attempt to access the tables while you're installing them.
Replicating Databases
The term "replication" can mean something as simple as copying a database to another server, or it can involve live updating of a secondary database as changes are made to the contents of a master database. If you simply want to copy a database to another server, you can use commands for this that were already shown earlier. Support for replication based on live updating has begun to appear in MySQL as of the 3.23 version. This capability is still in its infancy, so I won't say more about it other than you should keep an eye on current versions as they are released to see what new developments are taking place if you're interested.