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:
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.
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:
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:
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.
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: