32.8. Data Recovery

A backup is one component needed for a data recovery operation. It serves as a snapshot of your databases at a given point in time (the time when the backup was made). However, for an active server, data changes will have been made after the most recent backup. The other component of a restore operation is the record that the server has of those changes that is, the binary log. A recovery operation therefore involves using the backup to restore the databases, and then re-executing the modifications contained in the binary log that were made after the backup.

The general recovery procedure follows these steps:

1.	Make a copy of the data directory first, in case something goes wrong during recovery.
2.	Recover your databases using your backup files. If you made a binary backup, this step involves stopping the server and replacing the lost or damaged files with the copies. For MyISAM and InnoDB tables, the files that you need to replace are described in Section 32.3, "Making Binary Backups." If you made a text backup, reload the dump file or files as described in Section 32.8.1, "Reloading mysqldump Output," or Section 32.8.2, "Reloading Dumps with MySQL Administrator."
3.	Re-execute the changes in the binary log that were recorded after your backup was made. See Section 32.8.3, "Processing Binary Log Contents."

32.8.1. Reloading mysqldump Output

To reload an SQL-format dump file produced by mysqldump, process it with mysql. For example, you might have made a copy of the Country table in the world database with this command:

 shell> mysqldump world Country > dump.sql 

To reload the file later, use mysql:

 shell> mysql world < dump.sql 

The mysql command for reloading mysqldump output should name the database if the dump file itself does not. It is not necessary to name the database if you are reloading a dump file created by invoking mysqldump with the --database or --all-databases option. In that case, the dump file contains appropriate USE db_name statements.

mysqldump output can be used not just to restore tables or databases, but also to copy them. mysql can read from a pipe, so you can combine the use of mysqldump and mysql into a single command that copies tables from one database to another. For example, to copy the Country table from the world database to the test database, use this command:

 shell> mysqldump world Country | mysql test 

The pipe technique also can be used to copy databases or tables over the network to another server. The following command uses a pipe to copy the Country table from the world database on the local host to the world database on the remote host other.host.com:

 shell> mysqldump world Country | mysql -h other.host.com world 

If a dump file contains very long INSERT statements, they might exceed the default size of the communications buffer (1MB). You can increase the buffer size for both mysqldump and mysql with the --max-allowed-packet option. The option value may be given in bytes or followed by K, M, or G to indicate a size in kilobytes, megabytes, or gigabytes. For example, --max-allowed-packet=32M specifies a size of 32MB. The server also must be run with a --max-allowed-packet value that increases its own communications buffer to be large enough.

If you invoke mysqldump with the --tab option, it produces tab-delimited data files. (See Section 15.3.2, "Exporting Data with mysqldump.") In this case, reloading the files requires a different approach. Suppose that you dump the table City from the world database using the /tmp directory as the output directory:

 shell> mysqldump --tab=/tmp world City 

The output will consist of a City.sql file containing the CREATE TABLE statement for the table, and a City.txt file containing the table data. To reload the table, change location into the dump directory, process the .sql file using mysql, and load the .txt file using mysqlimport:

 shell> cd /tmp shell> mysql world < City.sql shell> mysqlimport world City.txt 

If you combine the --tab option with format-control options such as --fields-terminated-by and --fields-enclosed-by, you should specify the same format-control options with mysqlimport so that it knows how to interpret the data files.

32.8.2. Reloading Dumps with MySQL Administrator

MySQL Administrator can reload SQL-format dump files such as those created by itself or mysqldump. It can also analyze a dump file to see what tables it will restore, and then present a dialog that allows you to exclude tables from the restore operation. This is useful if you want MySQL Administrator to process only part of a dump file (for example, to recover only certain tables from a full-database dump).

For more information about these capabilities of MySQL Administrator, see Section 26.5, "Backup and Restore Capabilities."

32.8.3. Processing Binary Log Contents

After you have restored your binary backup files or reloaded your text backup files, you should finish a recovery operation by reprocessing the data changes that are recorded in the server's binary logs. To do this, determine which logs were written after you made your backup. Then convert their contents to text SQL statements with the mysqlbinlog program and process the resulting statements with mysql.

It's easiest to process the binary logs if each log file was written entirely before or entirely after the time of the backup. For example, if your log files were numbered 1 to 49 before the backup and logs 50 to 52 were written after the backup, you'll need to process logs 50 to 52 after restoring the backup. If your binary logs are named with a prefix of bin, the log processing command looks like this:

 shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql 

All the binary log files that you want to process should be handled in a single mysqlbinlog command. There may be inter-file dependencies that will not be satisfied if you process them separately.

If a given binary log file was in the middle of being written during the backup, you must extract from it only the part that was written after the backup, plus all log files written after that. To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

• The --start-datetime option specifies the date and time at which to begin extraction, where the option argument is given in DATETIME format.

• The --start-position option can be used to specify extraction beginning at a given log position.

• There are also corresponding --stop-datetime and --stop-position options for specifying the point at which to stop extracting log contents.

For example, to extract the contents of logs 50 to 52 beginning with events recorded at 2005-05-20 17:43:20, modify the previous command as follows:

 shell> mysqlbinlog --start-datetime="2005-05-20 17:43:20"            bin.000050 bin.000051 bin.000052 | mysql 

If you're not sure about the timestamp or position in a log file that corresponds to the point at which you want processing to begin, use mysqlbinlog without mysql to display the log contents for examination. In this case, a pager program can be useful:

 shell> mysqlbinlog file_name | more