This section describes how to rebuild a table, following changes to MySQL such as how data types or character sets are handled. For example, an error in a collation might have been corrected, requiring a table rebuild to update the indexes for character columns that use the collation. (For examples, see Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.) You might also need to repair or upgrade a table, as indicated by a table check operation such as that performed by CHECK TABLE, mysqlcheck, or mysql_upgrade.

Methods for rebuilding a table include dumping and reloading it, or using ALTER TABLE or REPAIR TABLE. REPAIR TABLE only applies to MyISAM, ARCHIVE, and CSV tables.

To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:

shell> mysqldump db_name t1 > dump.sql
shell> mysql db_name < dump.sql

To rebuild all the tables in a single database, specify the database name without any following table name:

shell> mysqldump db_name > dump.sql
shell> mysql db_name < dump.sql

To rebuild all tables in all databases, use the --all-databases option:

shell> mysqldump --all-databases > dump.sql
shell> mysql < dump.sql

To rebuild a table with ALTER TABLE, use a “null” alteration; that is, an ALTER TABLE statement that “changes” the table to use the storage engine that it already has. For example, if t1 is an InnoDB table, use this statement:

mysql> ALTER TABLE t1 ENGINE = InnoDB;

If you are not sure which storage engine to specify in the ALTER TABLE statement, use SHOW CREATE TABLE to display the table definition.

If you need to rebuild an InnoDB table because a CHECK TABLE operation indicates that a table upgrade is required, use mysqldump to create a dump file and mysql to reload the file, as described earlier. If the CHECK TABLE operation indicates that there is a corruption or causes InnoDB to fail, refer to Section 14.19.2, “Forcing InnoDB Recovery” for information about using the innodb_force_recovery option to restart InnoDB. To understand the type of problem that CHECK TABLE may be encountering, refer to the InnoDB notes in Section 13.7.2.2, “CHECK TABLE Syntax”.

For MyISAM, ARCHIVE, or CSV tables, you can use REPAIR TABLE if the table checking operation indicates that there is a corruption or that an upgrade is required. For example, to repair a MyISAM table, use this statement:

mysql> REPAIR TABLE t1;

mysqlcheck --repair provides command-line access to the REPAIR TABLE statement. This can be a more convenient means of repairing tables because you can use the --databases or --all-databases option to repair all tables in specific databases or all databases, respectively:

shell> mysqlcheck --repair --databases db_name ...
shell> mysqlcheck --repair --all-databases

For incompatibilities introduced in MySQL 5.1.24 by the fix for Bug #27877 that corrected the utf8_general_ci and ucs2_general_ci collations, a workaround is implemented as of MySQL 5.1.62, 5.5.21, and 5.6.5. Upgrade to one of those versions, then convert each affected table using one of the following methods. In each case, the workaround altering affected columns to use the utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations, which preserve the original pre-5.1.24 ordering of utf8_general_ci and ucs2_general_ci.

After making the appropriate changes, CHECK TABLE should report no error.