15.6 Checking and Repairing InnoDB Tables

To check InnoDB tables for problems, use the CHECK TABLE statement:

mysql> CHECK TABLE table_name;

Another way to check InnoDB tables is to use the mysqlcheck client program, which acts as a command-line interface to the CHECK TABLE statement:

shell> mysqlcheck db_name table_name

More detailed instructions for using mysqlcheck can be found in the "Core Study Guide."

If a table check indicates that an InnoDB table has problems, you should be able to restore the table to a consistent state by dumping it with mysqldump, dropping it, and re-creating it from the dump file.

In the event of a crash of the MySQL server or the host on which it runs, some InnoDB tables might need repairs. Normally, it suffices simply to restart the server because the InnoDB storage engine performs auto-recovery as part of its startup sequence. In rare cases, the server might not start up due to failure of InnoDB auto-recovery. If that happens, use the following procedure:

• Restart the server with the --innodb_force_recovery option set to a value in the range from 1 to 6. These values indicate increasing levels of caution in avoiding a crash, and increasing levels of tolerance for possible inconsistency in the recovered tables. A good value to start with is 4.
  
  
• When you start the server with --innodb_force_recovery set to a nonzero value, InnoDB treats the tablespace as read-only. Consequently, you should dump the InnoDB tables and then drop them while the option is in effect. Then restart the server without the --innodb_force_recovery option. When the server comes up, recover the InnoDB tables from the dump files.
  
  
• If the preceding steps fail, it's necessary to restore the InnoDB tables from a previous backup.