Using Backups for Data Recovery

Recovery procedures involve two sources of information: your backup files and your binary logs. Backup files can be either dump files generated with mysqldump or files copied using one of the binary backup methods.

The backup files restore tables to the state they were in at the time the backup was performed. The binary logs that were written after the backup contain the statements that have modified the tables since then. mysqlbinlog converts these logs back into text SQL statements so that you can execute them with mysql. This enables you to re-apply the changes made between the time of the backup and the time at which problems occurred.

The recovery procedure varies depending on how much information you must restore. In fact, it may be easier to restore an entire database than a single table, because it's easier to apply the binary logs for a database than for a table.

The discussion here assumes that you've been performing database backups and have binary logging enabled. If that's not true, you're living dangerously. You should enable the log right now and generate a new backup before reading further. You don't ever want to be in the position of having irretrievably lost a table because you were lax about saving the information necessary to restore it. Instructions for making backups are given earlier in this chapter. To find out how to enable the binary log, see "Maintaining Log Files," in Chapter 11.

Recovering Entire Databases

The general database recovery procedure involves the following steps:

1.	Copy the contents of the database directory somewhere else. You may want it later if you make a mistake or something goes wrong during recovery.
2.	Reload the databases using your most recent backup files: If your backups take the form of dump files generated by `mysqldump`, reload each one by using it as input to `mysql`. Note: If the database or databases that you need to recover include the `mysql` database that contains the grant tables and you are using dump files to recover the tables, you'll need to reload them while running the server using the `--skip-grant-tables` option. Otherwise, it may complain about not being able to find the grant tables. It's also a good idea to use `--skip-networking` to cause the server to reject all remote connection attempts while you're performing the restoration. After you've restored the tables, stop the server and restart it normally so that it uses the grant tables and listens to its network interfaces as usual. If you're using files from a binary backup (for example, a backup made with `mysqlhotcopy`, `tar`, or `cp`), stop the server, copy the files back to their original locations (probably under the data directory), and restart the server. The reason for stopping the server is that you don't want it trying to access the files during the copy operation.
3.	Use the binary logs to repeat the statements that modified database tables subsequent to the time at which the backup was made. The procedure for this is given in "Re-Executing Statements in Binary Log Files."

Recovering Individual Tables

Recovering an individual table can be more difficult than recovering a database. If you have a dump file generated by mysqldump that contains only that table, just reload the file. If you have a dump file that contains data for many tables, you can recover one of them by editing the file to delete the data for the other tables and then reloading the remainder. That's the easy part.

The more difficult part of recovery is extracting the parts of the binary logs that apply to the table. mysqlbinlog supports a --database option to limit its output to the statements for a single database, but there is no corresponding single-table option. A strategy that you might find useful in this situation is to restore more than you need, and then discard what you don't want. This procedure can actually be easier than trying to restore a single table by extracting the relevant parts from the binary logs.

1.	Restore the entire contents of the database that contains the table you want, but do so into a second, empty database. You can do this with your backups and by re-applying the binary logs. However, there are two complications: A dump file from `mysqldump` might contain a `USE` statement for the original database. You'll need to either change it or remove it before using the dump file as input to `mysql`. Output from `mysqlbinlog` will contain one or more `USE` statements for the original database. Save the output in a file so that you can edit these statements to name the second database before using the file as input to `mysql`.
2.	From the second database, use `mysqldump` to dump the table in which you are interested.
3.	Drop the original table and load the dump file into the original database to re-create the table. If you run `mysqldump` with the `--opt` or `--add-drop-table` option, the dump file itself will contain a `DROP TABLE` statement that removes the table before re-creating it.

For MyISAM tables, an alternative to using mysqldump is to directly copy the table files from the second database directory to the original database directory. Make sure that no server is working with either copy of the table when you perform the copy operation.

Re-Executing Statements in Binary Log Files

After you restore databases or tables from your backup files, re-apply the portions of your binary logs that contain the statements executed after the backup was made. This brings your tables up to date.

The mysqlbinlog program converts binary log files to statements in text form, making them easy to execute. For example, you can use the output from mysqlbinlog as input to mysql.

Depending on what you restored from backup, you might need to apply all statements in the binary logs, or just those for a particular database. You might also need to select only those statements that were executed within a particular time interval. mysqlbinlog can do these things. It can process multiple binary log files, and it can limit its output to statements for a given database or time interval.

The following instructions for applying the binary log files assume that the logs all have names of the form binlog.nnnnnn, where nnnnnn is the six-digit extension indicating the log sequence number. Adjust the instructions if your logs have a basename different from binlog. Also, I focus here on the use of local binary logs that exist on the same host where you execute mysqlbinlog. The program is capable of reading remote binary logs, but that is not covered here. For details on mysqlbinlog remote log processing options, see Appendix F.

If the backup from which you restored your databases was made before all of your current binary logs were written, you'll need to apply their entire contents. To do so, use this command in the directory where the log files are located:

% mysqlbinlog binlog.[0-9]* | mysql

If you need to edit the logs before re-executing them, convert them to text format and save the result in a file. Then edit the file and feed the result to mysql. Here is an example:

% mysqlbinlog binlog.[0-9]* > text_file
% vi text_file
% mysql < text_file

This strategy is necessary if the reason that you're performing recovery and using the logs to restore information is because someone issued an ill-advised DROP DATABASE, DROP TABLE, or DELETE statement. You'll need to remove that statement from the logs before executing their contents.

The binlog.[0-9]* pattern in the preceding mysqlbinlog commands expands to the list of binary log files, normally in the same order in which they were generated by the server. However, if you have recently upgraded MySQL to version 4.1 or later, you'll notice that your old binary logs have sequence number extensions with only three digits, not six. If you have a mix of three-digit and six-digit extensions, the binlog.[0-9]* pattern might not expand with the files named in creation order. In this case, name the files individually in the order they should be processed. (Alternatively, the admin directory in the sampdb distribution contains a script named ext_num_sort.pl that you can use to sort the files in correct order. See the README.txt file in that directory for instructions.)

Do not use mysqlbinlog and mysql to process binary log files one by one. There can be inter-file dependencies that will be broken unless you process the files as a group. For example, a TEMPORARY table created in one log file might be used in a later log file. If you process each log file separately, all TEMPORARY tables created by each log are dropped as the corresponding mysql invocation finishes and become unavailable to statements in the following logs.

To extract only those statements that pertain to a particular database, use the --database option to mysqlbinlog:

% mysqlbinlog --database=db_name binlog.[0-9]* | mysql

mysqlbinlog also supports several options for extracting statements that occur within a particular time window (for example, statements written after a given backup was made). You may need to examine what's in the log files to see what option values to supply. Here is a sample of mysqlbinlog output (with some of the comment lines shortened to fit the page):

...
SET TIMESTAMP=1097443672;
INSERT INTO absence VALUES (5,'2004-09-03');
# at 105915
#041010 16:28:04 server id 1  log_pos 105915    Query...
SET TIMESTAMP=1097443684;
FLUSH TABLES;
# at 105996
#041010 16:28:08 server id 1  log_pos 105996    Query...
SET TIMESTAMP=1097443688;
INSERT INTO absence VALUES (10,'2004-09-09');
# at 106077
#041010 16:28:12 server id 1  log_pos 106077    Query...
SET TIMESTAMP=1097443692;
INSERT INTO absence VALUES (17,'2004-09-07');
# at 106158
#041010 16:28:19 server id 1  log_pos 106158    Query...
SET TIMESTAMP=1097443699;
INSERT INTO absence VALUES (20,'2004-09-07');
...

Suppose that the FLUSH TABLES statement corresponds to the time of your backup and that you want to apply the statements made after that. You could do this by using the time of the following INSERT statement, which is 2004-10-10 16:28:08. That value can be given to the --start-datetime option in either of these formats:

% mysqlbinlog --start-datetime=20041010162808 binlog.[0-9]* | mysql
% mysqlbinlog --start-datetime="2004-10-10 16:28:08" binlog.[0-9]* | mysql

There is a corresponding --stop-datetime option for giving the ending time. There are also position-based options that take log_pos values shown in the log. For information, see the description of mysqlbinlog in Appendix F.

Recovering InnoDB Tables That Have Foreign Key Relationships

Reloading the contents of InnoDB tables can be tricky due to the constraints imposed by foreign key relationships. As of MySQL 4.1.1, mysqldump makes this easier because its output includes SET statements that set the foreign_key_checks variable to disable and enable foreign key checking. The reason for this is to allow dump files containing multiple InnoDB tables to be reloaded in any order, no matter the order in which tables appear in the files.

If you have a dump file from an older version of mysqldump that does not include these SET statements, you might have trouble if the tables are not listed in the file in the order required by their foreign key relationships. To suppress foreign key checking, load the file with the source command rather than by naming the file on the mysql command line:

% mysql mydb
mysql> SET foreign_key_checks = 0;
mysql> source dump.sql;
mysql> SET foreign_key_checks = 1;
mysql> ...

The second SET statement enables foreign key checking again. It is necessary only if you plan to issue further statements within the mysql session after loading the dump files. It is unneeded if you exit mysql after loading the files.

If the files are individual dump files, each corresponding to a single InnoDB table, you must either reload them in the order required by their parent-child foreign key relationships or else disable foreign key checking so that you can load them in any order. Suppose that you have two dump files, parent.sql and child.sql, that contain the rows from tables named parent and child, and that child includes a foreign key reference to parent. You can reload the files two ways:

If you leave foreign key checking enabled, you must load parent.sql first, and then child.sql:
```
% mysql mydb < parent.sql
% mysql mydb < child.sql
```
If you try to load the files in the opposite order, the parent records to which the child records refer will not exist and an error will occur.
If you turn off foreign key checking, you can reload the tables in any order. This also speeds up loading. The foreign_key_checks variable controls whether InnoDB checks foreign key relationships. You can turn it off and load the tables like this:
```
% mysql mydb
mysql> SET foreign_key_checks = 0;
mysql> source child.sql;
mysql> source parent.sql;
mysql> SET foreign_key_checks = 1;
mysql> ...
```

Coping with InnoDB or BDB Auto-Recovery Problems

If the MySQL server or the server host crashes, the InnoDB and BDB storage engines attempt to perform auto-recovery when the MySQL server restarts. In rare instances, auto-recovery might fail. This section describes what to do if that happens.

In the event that InnoDB detects a non-recoverable problem during server startup, its auto-recovery process fails. In this case, set the innodb_force_recovery system variable to a non-zero value between 1 and 6 to cause the server to start up even if InnoDB recovery after a crash otherwise fails. To set the variable, put a line in the [mysqld] group of your server's option file:

[mysqld]
innodb_force_recovery=level

The InnoDB storage engine uses more conservative strategies for lower values of level. A typical recommended starting value is 4. After the server starts, dump your InnoDB tables with mysqldump to get back as much information as possible, drop the tables, and restore them from the mysqldump output file. This procedure will re-create the tables in a form that is internally consistent, and may be sufficient to achieve a satisfactory recovery. After performing the recovery, remove the line that sets innodb_force_recovery from the option file.

If you need to restore all of your InnoDB tables, you'll need to use your backups. The approach to take depends on what kind of backup you made:

If you made a binary backup, you should have copies of the shared and individual tablespace files, the InnoDB log files, the .frm file for each table, and the option file that defines your InnoDB configuration. After making sure the server is stopped, delete any existing InnoDB files and replace them with your backup copies. Then make sure your current server option file lists the InnoDB configuration the same way as your saved option file and restart the server.

If you backed up your InnoDB tables by running mysqldump to generate a dump file, you should reinitialize the shared tablespace and InnoDB logs and reload the dump file into InnoDB:

1.	Stop the server and remove any existing InnoDB-related files: the shared and individual tablespace files (other than raw partitions), the InnoDB log files, and the `.frm` files for all InnoDB tables.
2.	Configure the shared tablespace the same way you did initially and restart the server. InnoDB then will re-create its shared tablespace and log files. For instructions, see "Configuring the InnoDB Tablespace" in Chapter 11. Remember that initializing the tablespace is a two-step process if you're using any raw partitions.
3.	Reload your dump file or files by using them as input to `mysql`. This re-creates the InnoDB tables.

After restoring the InnoDB tables from the backups, re-apply any updates from your binary logs that occurred after the backup was made. (See "Re-Executing Statements in Binary Log Files.") This is easiest if you're restoring your InnoDB tables as part of restoring your entire set of databases, because in that case you can apply all the updates made subsequent to the backup. If you're restoring only your InnoDB tables, applying the logs will be trickier because you want to use updates only for those tables.

The BDB storage engine, like the InnoDB engine, attempts auto-recovery when you start the server after a crash. If startup fails because of a non-recoverable BDB problem, move any BDB log files from the data directory to some other directory (or remove them if you don't plan to examine them further). Then start the server with the --bdb-no-recover option. If the log files were corrupted, this may allow the server to start up and create a new BDB log. If the server still won't start up, you can try to replace your BDB files from backups:

If you made binary backups of the relevant files, you should have the BDB table files and the BDB log files. With the server stopped, remove the existing BDB log files from the data directory and the BDB .frm and .db table files from database directories in which they appear. Replace those files with your backups and restart the server.
If you used mysqldump to generate a backup file consisting of SQL statements that re-create the tables, stop the server and remove the existing BDB table and log files. Then restart the server and load the backup file by using it as input to mysql.

After restoring the backup, re-apply any post-backup updates from the binary logs (observing the same comments noted a few paragraphs back with regard to InnoDB recovery).