Checking and Repairing Database Tables
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 also is 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.
This section describes table checking and repair procedures you can use to deal with more minor forms of damage. If you suspect that a table has become corrupted, check it for errors. If the table checks okay, you're done. If not, try to repair it using these guidelines:
In practice, most problems can be fixed without going to more extensive and slower repair methods.
In the event that tables or databases are lost or irreparably damaged, you'll need to restore them from your database backups and binary logs. "Using Backups for Data Recovery" later in the chapter discusses how to do that.
A general outline of the alternatives available to you for checking and repairing MyISAM and InnoDB tables follows. Specific details are given after that.
As mentioned earlier in the chapter, if you have a choice when performing table maintenance between letting the server do the work or running an external utility, it's generally better to let the server do the work. Then you need not be concerned about using any locking protocols to coordinate table access. That advantage applies when using CHECK TABLE and REPAIR TABLE (or mysqlcheck). If you use myisamchk, you must ensure that the server does not use the tables while you're working on them. Nevertheless, you might decide to use myisamchk for the following reasons:
To check an InnoDB table, use CHECK TABLE or mysqlcheck. If the table is found to have problems, dump it with mysqldump. Then drop the table and reload the dump file to re-create it. The following sequence of commands shows how you might check, dump, and reload the absence table in the sampdb database:
% mysqlcheck sampdb absence % mysqldump --opt sampdb absence > absence.sql % mysql sampdb < absence.sql
Using CHECK TABLE to Check Tables
The CHECK TABLE statement provides an interface to the server's table checking capabilities. It works for MyISAM and InnoDB tables.
To use CHECK TABLE, provide a list of one or more table names, optionally followed by modifiers that indicate what type of check to do. 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;
CHECK TABLE supports the following check mode options:
The check options apply only to checking MyISAM tables. They are ignored for InnoDB tables.
It's possible that CHECK TABLE will actually modify a table in some cases. For example, if a table is marked as corrupt or as not having been closed properly, but the check finds no problems, CHECK TABLE marks the table as okay. This change involves only modifying an internal flag.
Using REPAIR TABLE to Repair Tables
The REPAIR TABLE statement provides an interface to the server's table repair capabilities. It works only for MyISAM tables.
To use REPAIR TABLE, provide a list of one or more table names, optionally followed by modifiers that indicate what type of repair to do. For example, the following statement tries to repair three tables in quick repair mode:
REPAIR TABLE tbl1, tbl2, tbl3 QUICK;
REPAIR TABLE supports the following repair mode options:
Using mysqlcheck to Check and Repair Tables
The mysqlcheck program provides a command-line interface to the CHECK TABLE and REPAIR TABLE statements. It connects to the server and issues the appropriate statements for you based on the options you specify. mysqlcheck can check MyISAM and InnoDB tables and repair MyISAM tables.
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, mysqlcheck checks only those tables:
% mysqlcheck sampdb president member
% mysqlcheck --databases sampdb test
% mysqlcheck --all-databases
mysqlcheck is more convenient than issuing the CHECK TABLE and REPAIR TABLE statements directly, because those statements require that you explicitly name each table to be checked or repaired. With mysqlcheck, it's much easier to check all tables in a database: It looks up the names of the tables in the database for you and constructs statements that name the appropriate tables.
By default, mysqlcheck checks tables using a medium check, but supports options that enable explicit selection of the type of operation to perform. The following table shows some mysqlcheck options and the CHECK TABLE options to which they correspond. (As with CHECK TABLE, these options apply only to MyISAM tables and are ignored for InnoDB tables.)
Using myisamchk to Check and Repair Tables
The myisamchk utility can check or repair MyISAM tables. myisamchk does its work by accessing table files directly, so before invoking it, you might want to stop the server to prevent it from accessing the table files while you're using them. If you leave the server running, make sure you read "Performing Database Maintenance with the Server Running." That section discusses the proper locking protocols for preventing the server from using a table at the same time that you're performing checking or repair procedures on it with myisamchk. The following discussion assumes that you have either stopped the server or are using the appropriate locking protocol.
myisamchk makes no assumptions about where tables are located. To run it, 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. Typically, you change location into the relevant database directory first before invoking myisamchk, and 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 ...
% myisamchk member % myisamchk member.MYI
To name all the relevant index files in the database directory, use a filename pattern:
% myisamchk options *.MYI
If you don't want to attempt a maintenance operation with myisamchk on the original table files, 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
The normal check method usually is sufficient to identify problems. If it reports no errors but you still suspect damage (perhaps because queries do not seem to return the correct results), perform an intermediate-level check by specifying the --medium-check option. This is somewhat slower but more thorough. Finally, you can perform the most 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.
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 statements that seem to yield problematic results to verify that they are written correctly. If you believe the problem might be with the MySQL server, consider filing a bug report or upgrading to a newer version.
Repairing Tables with myisamchk
To perform a repair operation on a table with myisamchk, use the following procedure. It first tries the repair types that are faster but less thorough, and then escalates to slower but more thorough methods if faster methods fail to correct the problems. Before using the repair procedure, make copies of the table files 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.
Note: If the table contains any FULLTEXT indexes, it might be necessary to use additional myisamchk options during repairs. See the FULLTEXT-related notes in the myisamchk description in Appendix F for details.
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. Typically, this indicates the presence of a temporary file that was left around from a previous failed repair attempt. To force removal of the temporary file, repeat the command but add the --force option.
If the preceding repair procedure fails to repair the table, your index file may be missing or damaged beyond repair. It's also possible, although unlikely, that the table's .frm format file is missing. In either of these cases, you'll need to replace the affected files, and then try the repair procedure again.
To regenerate the index file for a table t, use this procedure if you have the table's format file, t.frm:
If you don't have the table's .frm format file, you'll need to recover it from your backup files first. Then use the procedure at the beginning of this section. If you are missing the .frm file and also have no backup, but you know the CREATE TABLE statement that must be issued to create the table, you still might be able to repair it as follows:
Getting myisamchk to Run Faster
myisamchk can take a long time to run, especially if you're working with a large 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 variables that can be set, the most important of which control the sizes of the buffers that it uses:
To find out what values myisamchk uses for these variables by default, run it with the --help option. To specify a different value, use --var_name=value on the command line. 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 --sort_buffer_size=512M --read_buffer_size=1M \ --write_buffer_size=1M other-options tbl_name
For repair operations, --sort_buffer_size applies when the --recover option is given, but not with --safe-recover. The --key_buffer_size option applies when --safe-recover is given or when you check the table with --extend-check.