Table Repair and Data Recovery

Database damage occurs for a number of reasons and varies in extent. If you're lucky, you may simply have minor damage to a table or two (for example, if your machine goes down briefly due to a power outage). In this case, it's likely that the server can repair the damage when it comes back up. If you're not so lucky, you may have to replace your entire data directory (for example, if a disk died and took your data directory with it). Recovery is also needed under other circumstances, such as when users mistakenly drop databases or tables or delete a table's contents. Whatever the reason for these unfortunate events, you'll need to perform some sort of recovery:

  • If tables are damaged but not lost, try to repair them using the CHECK TABLE statement or with the mysqlcheck or myisamchk utilities. You may not need to resort to using backup files at all if the damage is such that a repair operation can fix it.

  • If tables are lost or irreparable, you'll need to restore them.

The first part of this section describes table checking and repair procedures you can use to deal with more minor forms of damage. This includes interactive procedures to be used as needed and non-interactive procedures to be used for setting up scheduled preventive maintenance. The second part of the section discusses how to recover tables and databases if you lose them entirely or they are damaged beyond repair.

Checking and Repairing Database Tables

If you suspect that table corruption has occurred, the general procedure for damage detection and correction is as follows:

  1. Check the table for errors. If the table checks okay, you're done. If not, you must repair it.

  2. Make copies of the table files before beginning repair, just in case something goes wrong. That is unlikely, but if it happens, you can make a new copy of the table from the copied files and try a different recovery method.

  3. Try to repair the table. If the repair operation succeeds, you're done. If not, restore the table from your database backups and update logs.

The final step of this procedure assumes that you've been performing database backups and have binary update logging enabled. If that's not true, you're living dangerously. Read the discussion earlier in this chapter that describes how to make backups. Also, read Chapter 11 to find out how to enable the log. 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.

You can check or repair tables by using myisamchk, which operates on the table files directly. Or you can tell the server to check or repair tables using the CHECK TABLE or REPAIR TABLE statements (or by using mysqlcheck, which connects to the server and issues these statements for you). An advantage of using the SQL statements or mysqlcheck is that the server does the work for you. If you run myisamchk, you must ensure that the server stays away from the table files while you're working on them.

As mentioned earlier in the chapter, if you have a choice between letting the server do the work and running an external utility, let the server do the work. Then you don't have to worry about issues of table access coordination. The primary reasons you might decide to use an external program, such as myisamchk, are as follows:

  • You can use it when the server is down. CREATE TABLE and REPAIR TABLE require that the server be running.

  • You can tell myisamchk to use larger buffers to make checking and repair operations run faster. This can be helpful if you have very large tables.

  • myisamchk can be used with older servers. CHECK TABLE and REPAIR TABLE were introduced in MySQL 3.23.13 and 3.23.14, respectively, whereas myisamchk is available back to 3.23.0. (Of course, this particular distinction becomes less relevant as time passes and servers older than 3.23.13 become less common.)

Note: The information given here for myisamchk can be adapted for use with isamchk. See the "The relationship of isamchk to myisamchk" section near the beginning of this chapter.

Checking and Repairing Tables Using myisamchk

Before you use myisamchk to check or repair tables, you may want to bring down the server so that it doesn't access the table files while you're using them. If you want to leave the server running, make sure you read the "Coordinating with the Server" section earlier in this chapter. That section discusses the appropriate locking protocols to use to prevent the server from using a table at the same time you're performing checking or repair procedures on it.

Invoking myisamchk

myisamchk makes no assumptions about where tables are located; to run it, you specify the pathnames to the table files you want to use. It's most convenient to do this if you're in the directory that contains the tables, so typically you change location into the relevant database directory first before invoking myisamchk. Then tell it which tables you want to check or repair, along with the options that indicate what type of operation to perform:

 % myisamchk options tbl_name ...  

A tbl_name argument can be either a table name or the name of the table's index file. This means you can use a filename pattern based on the index file extension to name pick up all the relevant files in the current directory:

 % myisamchk options *.MYI  

If you don't want to use the original table files, you can copy them to another directory and then work with the copies in that directory.

Checking Tables with myisamchk

myisamchk provides table-checking methods that vary in how thoroughly they examine a table. To perform a normal table check, use either of the following commands:

 % myisamchk tbl_name  % myisamchk --check tbl_name 

myisamchk's default action with no options is --check, so those commands are equivalent.

The normal check method is usually sufficient to identify problems. If it reports no errors but you still suspect damage (perhaps because queries do not seem to be working properly), you can perform a more extensive check by specifying the --extend-check option. This can be very slow, but it is extremely thorough; for each record in the table's data file, the associated key for every index in the index file is checked to make sure it really points to the correct record. (myisamchk also supports a --medium-check option to perform an intermediate check that is less thorough but faster than extended checking.)

If no errors are reported for a check with --extend-check, you can be sure your table is okay. If you still have problems with the table, the cause must lie elsewhere. Re-examine any queries that seem to yield problematic results to verify that they are written correctly. If you believe the problem may be with the MySQL server, consider filing a bug report or upgrading to a newer version.

If myisamchk reports that a table has errors, you should try to repair it.

Repairing Tables with myisamchk

Table repair is an ugly business, made more so by the fact that the details tend to be very incident-specific. Nevertheless, there are general guidelines and procedures you can follow to significantly increase your chances of being able to fix the tables. Generally, you begin with the fastest repair method to see if that will correct the damage. If you find that it is not sufficient, you can escalate to more thorough (but slower) repair methods until either the damage has been repaired or you cannot escalate further. (In practice, most problems are fixable without going to more extensive and slower repair modes.) If the table cannot be repaired, you'll need to restore the table from your backups. Instructions for recovery using backup files and log files are given later in this chapter.

To perform a standard repair operation on a table, use the following procedure:

  1. Try to fix the table using the --recover option, and use the --quick option as well to attempt recovery based only on the contents of the index file. This will repair the table without touching the data file:

     % myisamchk --recover --quick tbl_name  
  2. If problems remain, rerun the command without the --quick option to allow myisamchk to go ahead and modify the data file, too:

     % myisamchk --recover tbl_name  
  3. If that doesn't work, try the --safe-recover repair mode. This is slower than regular recovery mode, but is capable of fixing a few problems that --recover mode will not:

     % myisamchk --safe-recover tbl_name  

It's possible when you run these commands that myisamchk will stop with an error message of the form Can't create new temp file: file_name. If that happens, repeat the command and add the --force option to force removal of the temporary file that may have been left around from a previous failed repair attempt.

If the standard repair procedure fails to repair the table, your index file may be missing or damaged beyond repair. It's also possible, though unlikely, that the table description file is missing. In either of these cases, you'll need to replace the affected files and then try the standard repair procedure again.

To regenerate the index file for a table t, use this procedure:

  1. Move into the database directory that contains the crashed table.

  2. Move the table's data file, t.MYD, to a safe place.

  3. Invoke mysql and re-create a new empty table by executing the following statement, which uses the table description file, t.frm, to regenerate new data and index files from scratch:

     mysql> TRUNCATE TABLE t;  

    Prior to MySQL 4.0, use DELETE instead:

     mysql> DELETE FROM t;  
  4. Exit mysql and move the original data file back into the database directory, replacing the new empty data file you just created. The data file and the index file now will be out of sync, but the index file now has a legal internal structure that the server can be interpret and rebuild based on the contents of the data file and table description file.

  5. Attempt a standard table repair again.

To recover the table description file, t.frm, restore it from your backup files and then attempt a standard repair again. If for some reason you have no backup but you know the CREATE TABLE statement that must be issued to create the table, you still may be able to repair it:

  1. Move into the database directory that contains the crashed table.

  2. Move the table's data file, t.MYD, to a safe place. If you want to try to use the index file, t.MYI, move that, too.

  3. Invoke mysql and issue the CREATE TABLE statement that creates the table.

  4. Exit mysql and move the original data file back into the database directory, replacing the new data file you just created. If you moved the index file in step 2, move it back into the database directory, too.

  5. Attempt a standard table repair again.

Getting myisamchk to Run Faster

myisamchk can take a long time to run, especially if you're working with a big table or using one of the more-extensive checking or repair methods. You can speed up this process by telling myisamchk to use more memory when it runs. myisamchk has several operating parameters that can be set. The most important of these variables control the sizes of the buffers that it uses:

Variable Meaning
key_buffer_size Size of buffer used to hold index blocks
read_buffer_size Size of buffer used for read operations
sort_buffer_size Size of buffer used for sorting
write_buffer_size Size of buffer used for write operations

To find out what values myisamchk uses for these variables by default, run it with the --help option. To specify a different value, use --set-variable variable=value or -Ovariable=value on the command line. You can abbreviate the variable names as key, read, sort, and write. For example, if you have lots of memory, you can tell myisamchk to use a 512MB sort buffer and 1MB read and write buffers by invoking it like this:

 % myisamchk -O sort=512M -O read=1M -O write=1M other-options tbl_name  

sort_buffer_size is used only with the --recover option (not with --safe_recover), and in that case, key_buffer_size is not used.

Checking and Repairing Tables Using the Server

The CHECK TABLE and REPAIR TABLE statements provide a SQL interface to the server's table checking and repair capabilities. They work for MyISAM tables. As of MySQL 3.23.39, CHECK TABLE also works for InnoDB tables.

For each statement, you provide a list of one or more table names followed by options to indicate what type of check or repair mode to use. For example, the following statement performs a medium level check on three tables, but only if they have not been properly closed:

 CHECK TABLE tbl1, tbl2, tbl3 FAST MEDIUM;  

The following statement tries to repair the same tables in quick repair mode:

 REPAIR TABLE tbl1, tbl2, tbl3 QUICK;  

CHECK TABLE allows the following options to specify what type of check to perform:

  • CHANGED

    Don't check tables unless they were not properly closed or have been changed since the last time they were checked.

  • EXTENDED

    Perform an extensive check. This is the most thorough check available and consequently the slowest. It attempts to verify that the data rows and the indexes are fully consistent.

  • FAST

    Don't check tables unless they were not properly closed.

  • MEDIUM

    Perform a medium-level check. This is the default if you specify no options.

  • QUICK

    Perform a quick check that scans only the index rows. It does not check the data rows.

It's possible that CHECK TABLE will actually modify a table in some cases. For example, if a table was marked as corrupt or as not having been closed properly but the check finds no problems, CHECK TABLE will mark the table as okay. This change involves only modifying an internal flag.

REPAIR TABLE allows the following options to specify the repair mode:

  • EXTENDED

    Attempt a repair by recreating the indexes. (This is like using the --safe-recover option with myisamchk.)

  • QUICK

    Attempt a quick repair of just the indexes.

  • USE_FRM

    Attempt a repair using the table's .frm description file. The repair recreates the index based on the table description. Essentially, it automates the procedure described earlier that uses the .frm file to rebuild the index from the table description under circumstances when the index file is missing or unusable, and this can be useful if the index has been lost or corrupted. This option was introduced in MySQL 4.0.2.

With no options, REPAIR TABLE performs a standard repair operation like that done by myisamchk--recover.

The mysqlcheck utility provides a command line interface to the CHECK TABLE and REPAIR TABLE statements. This program connects to the server and issues the appropriate statements for you based on the options you specify. It is available as of MySQL 3.23.38. It can check MyISAM tables (and InnoDB tables, as of MySQL 3.23.39).

Typically, you invoke mysqlcheck with a database name, optionally followed by one or more table names. With just a database name, mysqlcheck checks all the tables in the database:

 % mysqlcheck sampdb  

With table names following the database name, it checks only those tables:

 % mysqlcheck sampdb president member  

If you specify the --databases option, all following names are interpreted as database names and mysqlcheck checks all the tables in each one:

 % mysqlcheck --databases sampdb test  

If you specify --all-databases, mysqlcheck checks all tables in all databases. In this case, you supply no database or table name arguments:

 % mysqlcheck --all-databases  

By default, mysqlcheck checks tables using a standard check but supports options that allow you to be more specific about the type of operation to perform. The following table shows some mysqlcheck options and the CHECK TABLE options to which they correspond:

mysqlcheck Option CHECK TABLE Option
--check-only-changed CHANGED
--extended EXTENDED
--fast FAST
--medium-check MEDIUM
--quick QUICK

mysqlcheck can also perform table repair operations. The following table shows some mysqlcheck options and the REPAIR TABLE options to which they correspond:

mysqlcheck Option REPAIR TABLE Option
--repair No options (performs a standard repair operation)
--repair--quick QUICK
--repair--extended EXTENDED

mysqlcheck provides a convenience over issuing the CHECK TABLE and REPAIR TABLE statements directly because those statements require that you explicitly name each table to be checked or repaired. mysqlcheck looks up the table names and constructs for you statements that name the appropriate tables.

Scheduling Preventive Maintenance

In addition to enabling auto-recovery and instituting backup procedures, as described earlier in this chapter, you should consider setting up a schedule of preventive maintenance. This helps detect problems automatically so that you can take steps to correct them. By arranging to check your tables on a regular basis, you'll reduce the likelihood of having to resort to your backups. This is most easily accomplished by using a cron job, typically invoked from the crontab file of the account used to run the server. For example, if you run the server as the mysqladm user, you can set up periodic check from the crontab file for mysqladm. (See Chapter 11 for information about setting up cron jobs.)

To check MyISAM and InnoDB tables automatically on a periodic basis while the server is online, use the mysqlcheck utility. Suppose you want to invoke mysqlcheck from the crontab file for the mysqladm user. Add an entry to that file that looks something like the following:

 0 3 * * 0 /usr/local/mysql/bin/mysqlcheck --all-databases     --check-only-changed --silent 

(The command as shown here takes two lines, but you should write it all on a single line.) This entry tells cron to run mysqlcheck at 3 a.m. every Sunday. You can vary the time or scheduling as desired.

The --all-databases option causes mysqlcheck to check all tables in all databases. This gives you an easy way to use it for maximum effect. The --check-only-changed option tells mysqlcheck to skip any table that hasn't been modified since it was last checked successfully, and the --silent option suppresses output unless there are errors in the tables. (cron jobs typically generate a mail message if a job produces any output at all, and there's little reason to receive mail for table-checking jobs that find no problems.) But note that even with --silent you may get some diagnostic output from mysqlcheck if your databases have tables of types that it doesn't know how to check.

If you have ISAM tables or if you have MyISAM tables but an older version of MySQL that doesn't include mysqlcheck, you can use isamchk and myisamchk to check tables. To perform an automatic table check, write a simple script that runs those utilities on all the MyISAM and ISAM tables under a given directory. The following script illustrates one way to do this:

 #! /bin/sh  # chk_mysql_tables.sh - check all MyISAM/ISAM tables under a given directory # Argument 1: directory pathname if [ $# -ne 1 ]; then     echo "Usage: $0 dirname" 1>&2     exit 1 fi # Change location to directory, check tables under it. # Notes: # - Prior to MySQL 3.23.22, change --check-only-changed to --fast # - isamchk does not support --check-only-changed or --fast cd $1 if [ $? -ne 0 ]; then     echo "Cannot cd to $1" 1>&2     exit 1 fi find . -name "*.MYI" -follow -print \     | xargs myisamchk --silent --check-only-changed find . -name "*.ISM" -follow -print \     | xargs isamchk --silent 

To use only one of the table checking utilities in this script, just omit the commands for the other. For example, if you have no ISAM tables, you can omit the isamchk command because in that case, find will provide it with no filenames and isamchk will display a usage message indicating that you need to specify some arguments.

Make the script executable with chmod +x and then invoke it with the pathname of your data directory to check all your MyISAM and ISAM tables:

 % chk_mysql_tables.sh /usr/local/mysql/data  

To check just the tables in a given database, invoke the script with the pathname to the corresponding database directory:

 % chk_mysql_tables.sh /usr/local/mysql/data/sampdb  

To invoke the script automatically, set it up as a cron job. If you run multiple servers on your system, you can run chk_mysql_tables.sh multiple times, each time with a different data directory argument.

Ideally, there will be no output from chk_mysql_tables.sh. However, if external locking is disabled (which it is by default as of MySQL 4), it's possible that the server will change a table while you're checking it. The script just checks the tables without attempting to repair them, so this won't cause any damage, but the myisamchk or isamchk utilities may falsely report problems for tables that are actually okay. (That is somewhat unfortunate, but it's better than the opposite problem of having the utilities report no damage when there actually is some.) If your system supports external locking, this problem won't occur. See the "Coordinating with the Server" section earlier in this chapter for information about external locking.

You can also run the chk_mysql_tables.sh script at machine boot time by invoking it from one of your system startup scripts. If you're using a BSD-style system and your MySQL server startup command is located in /etc/rc.local or the equivalent, you can simply invoke chk_mysql_tables.sh from that same file before starting up the server. For a System-V style system, look in the /etc/rc.d directory. You may be able to use rc.sysinit or a similar script to run chk_mysql_tables.sh before the MySQL server starts up.

Using Backups for Data Recovery

Recovery procedures involve two sources of information your backup files and your binary logs. The backup files restore tables to the state they were in at the time the backup was performed. However, tables typically will have been modified between the time of the dump and the time at which problems occurred. The binary logs contain the queries used to make those changes, so you can repeat the queries by using the logs as input to mysql. (This is why you should enable binary logging. If you haven't yet done so, you should do so right now and generate a new backup before reading further.)

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 update logs for a database than for a table.

What Logs Should You Use for Recovery Operations?

The possible logs that you can use in conjunction with your backup files for recovery operations are the update logs and the binary update logs. The binary update logs are better for recovery purposes, so you should use them in preference to the update logs if possible. The discussion in this section is written under the assumption that you'll use the binary logs. However, binary logging is available only as of MySQL 3.23.14. If you don't have that capability, you can use the update logs instead. Adjust the instructions accordingly.

Recovering an Entire Database

First of all, if the database you want to recover is the mysql database that contains the grant tables, you'll need to run the server using the --skip-grant-tables option. Otherwise, it will 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, bring down the server and restart it normally so that it uses the grant tables and listens to the network interfaces as usual.

The general recovery procedure involves the following steps:

  1. Copy the contents of the database directory somewhere else. You may want them later or example, to perform post-mortem examination of the corpses of crashed tables.

  2. Reload the database using your most recent backup files. If you're using backups generated by mysqldump as files that contain SQL statements, use them as input to mysql. If you're using files that were directly copied from the database (for example, with mysqlhotcopy, tar, or cp), bring down the server, copy the files directly into the database directory, and restart the server. (The reason for bringing down the server is that you don't want it trying to access the files during the copy operation.)

  3. Use the binary update logs to repeat the queries that modified database tables subsequent to the time at which the backup was made. For any applicable log, convert it to ASCII format with mysqlbinlog and use the result as input to mysql. Specify the --one-database option so that mysql executes queries only for the database you're interested in recovering.

If you know you need to apply all the log files, normally you can use the following command in the directory where they are located:

 % ls -t -r -1 binlog.[0-9]* | xargs mysqlbinlog | mysql --one-database db_name  

The ls command produces a single-column list of update log files, sorted by name, which normally should be the order in which they were generated by the server. However, if the numeric extensions of the filenames don't all have the same number of digits, that won't work. For example, if you have logs named binlog.998, binlog.999, and binlog.1000, ls will sort them with binlog.1000 first. Piping the names through sort won't work, either, because it sorts the names the same way. It's necessary to perform a numeric sort based only on the extension values. The following short Perl script does this:

 #! /usr/bin/perl -w  # ext_num_sort.pl - sort filenames based on numeric extension value. use strict; my @files = <>;         # read all input lines @files = sort {         # sort them by numeric extension             my $anum = $1 if $a =~ /\.(\d+)$/;             my $bnum = $1 if $b =~ /\.(\d+)$/;             $anum <=> $bnum;         } @files; print @files;           # print them exit (0); 

Use the script as follows:

 % ls -1 binlog.[0-9]* | ext_num_sort.pl | xargs mysqlbinlog \      | mysql --one-database db_name 

The preceding discussion assumes that you want to apply all the update logs, but it's often the case that you'll have to apply just some of them those that were written after some particular backup, for example. If the logs made since the time of your backup are named binlog.1392, binlog.1393, and so on, you can rerun the statements in them as follows:

 % mysqlbinlog binlog.1392 | mysql --one-database db_name  % mysqlbinlog binlog.1393 | mysql --one-database db_name ... 

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, be sure to remove that statement from the log file in which it appears before applying the log! To do this, convert the log to ASCII format and save it in a file. Then edit the file and feed the result to mysql:

 % mysqlbinlog logfile > textfile  % vi textfile % mysql --one-database db_name < textfile 

If you don't have binary update logs but you do have the text update logs, you don't need to use mysqlbinlog; the logs will already be in ASCII format. In this case, to apply the updates for a given database from all the logs at once, do the following:

 % ls -1 update.[0-9]* | ext_num_sort.pl | xargs cat \      | mysql --one-database db_name 

To apply individual logs, do the following:

 % mysql --one-database db_name < update.1392  % mysql --one-database db_name < update.1393 ... 

The problem of logs not sorting properly due to having different extension lengths should occur less frequently in the future. At some point (perhaps in the 4.1 series), the server will be changed to use extensions with a minimum of six digits rather than three. That change should make ext_num_sort.pl unnecessary.

Recovering Individual Tables

Recovering individual tables can be more difficult than recovering a database. If you have a backup file generated by mysqldump and it contains data for many tables, not just the one you're interested in (which is likely), you'll need to extract the relevant part of the file and use it as input to mysql. And that's the easy part! The hard part is pulling out the pieces of the logs that apply to just that table. You may find the mysql_find_rows utility helpful for this; it can extract multiple-line queries from update logs or from binary update logs that have been converted to ASCII with mysqlbinlog.

Another possibility is to restore the entire database into a second, empty database. From that database, dump the table you want to restore by using mysqldump--add-drop-table and then load it back into the original database. (--add-drop-table makes sure that the restore operation begins with a clean slate.) This procedure may actually be easier than trying to restore a single table by extracting the relevant lines from update logs. Another possibility (for table types other than BDB or InnoDB) is to copy the table files from the second database back to the database directory of the original database. Make sure the server for both databases are down when you copy the files back into the database directory.

Recovering Tables Using RESTORE TABLE

RESTORE TABLE is the counterpart to BACKUP TABLE and thus works to restore MyISAM tables that have been copied using the latter statement. It is available as of MySQL 3.23.25 and requires the FILE and INSERT privileges. To use RESTORE TABLE, name the table or tables that you want to restore, along with the directory on the server host where the backup files are located. Suppose you previously backed up three tables using BACKUP TABLE as follows:

 BACKUP TABLE tbl1, tbl2, tbl3 TO '/archive/sampdb';  

To restore any or all of those tables, name the ones you want in a RESTORE TABLE statement. For example, restore tbl1 and tbl3 as follows:

 RESTORE TABLE tbl1, tbl3 FROM '/archive/sampdb';  

RESTORE TABLE reloads a table's .frm and .MYD description and data files into the database directory and then uses them to rebuild the .MYI index file.

Recovering Tables That Have Foreign Key Relationships

If you have trouble using a dump file to restore tables that have foreign key relationships because the tables are not listed in the file in the order required by those relationships, you can temporarily turn off key checking with the following statement:

 SET FOREIGN_KEY_CHECKS = 0;  

FOREIGN_KEY_CHECKS is available as of MySQL 3.23.52. After the tables have been imported, enable key checking again:

 SET FOREIGN_KEY_CHECKS = 1;  

Turning key checking off allows you to create and load the tables in any order. It also speeds up loading.

It's necessary to turn off key checking within the same connection as that used to reload the dump file because the setting affects the current connection only. You can accomplish this by loading the file with the source command rather than by naming it on the command line. Suppose you have a dump file named dump.sql containing tables from a database named mydata. Load it as follows:

 % mysql mydata  mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> source dump.sql; mysql> SET FOREIGN_KEY_CHECKS = 1; mysql> ... 

The second SET statement is necessary only if you plan to issue further statements within the mysql session after loading the dump file. If you exit after loading the file, it is unneeded.

Recovering the InnoDB Tablespace or BDB Tables

The InnoDB table handler attempts to perform any necessary auto-recovery when the server restarts after a crash. However, in the event that InnoDB detects a non-recoverable problem, startup will fail. In this case, set the innodb_force_recovery server 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 like the following in your [mysqld] option group:

 set-variable = innodb_force_recovery=level  

The InnoDB handler attempts more conservative strategies for lower numbers. A typical recommended starting value of level is 4. After the server starts up, 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 recreate 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 line.

If you need to restore the entire InnoDB tablespace, the approach to use depends on how you made your backup. (I assume that you have created one, using the instructions in the "Backing Up the InnoDB Tablespace or BDB Tables" section earlier in this chapter.)

  • If you used a direct-copy method, you should have copies of the tablespace files, the log files, the .frm file for each table, and the server option file that defines your InnoDB configuration. After making sure the server is down, 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 the InnoDB tablespace by running mysqldump to generate a SQL file containing the CREATE TABLE and INSERT statements necessary to recreate the tables from scratch, and then you should reinitialize the tablespace and reload the dump file into it. With the server down, throw away any existing InnoDB-related files: The tablespace files (other than raw partitions), log files, and the .frm files for all InnoDB tables. Reconfigure the tablespace the same way you did initially. (See the "Configuring the InnoDB Tablespace" section in Chapter 11. A saved copy of the server option file may be helpful as a record of what that configuration should look like. Also, remember that initializing the tablespace is a two-step process if you're using any raw partitions.) When the server finishes creating the new tablespace, reload the backup file that contains the SQL statements for recreating the InnoDB tables by using it as input to mysql.

After restoring the tablespace from the backups, re-apply any updates from your binary logs that occurred after the backup was made. This is easiest if you're restoring your tablespace as part of restoring your entire set of databases, because in that case you can apply all the updates. If you're restoring only the InnoDB tablespace, applying the logs will be trickier because you want to use only updates for InnoDB tables.

The BDB handler, like the InnoDB handler, 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 directly copied the relevant files, you should have the BDB table files and the BDB log files. With the server down, remove the existing BDB files from your data directory and replace them with your backups.

  • If you used mysqldump to generate a backup consisting of SQL statements to recreate the tables, bring down 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 caveat noted earlier with regard to InnoDB recovery).



MySQL
High Performance MySQL: Optimization, Backups, Replication, and More
ISBN: 0596101716
EAN: 2147483647
Year: 2003
Pages: 188

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net