This section describes how to rebuild a table. This can be
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating 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”.) It might also
be that a table repair or upgrade should be done as indicated by a
table check operation such as that performed by CHECK
TABLE
or mysqlcheck.
Methods for rebuilding a table include dumping and reloading it,
or using ALTER TABLE
or
REPAIR TABLE
.
If you are rebuilding tables because a different version of MySQL will not handle them after a binary (in-place) upgrade or downgrade, you must use the dump-and-reload method. Dump the tables before upgrading or downgrading using your original version of MySQL. Then reload the tables after upgrading or downgrading.
If you use the dump-and-reload method of rebuilding tables only for the purpose of rebuilding indexes, you can perform the dump either before or after upgrading or downgrading. Reloading still must be done afterward.
To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:
shell>mysqldump
shell>db_name
t1 > dump.sqlmysql
db_name
< dump.sql
To rebuild all the tables in a single database, specify the database name without any following table name:
shell>mysqldump
shell>db_name
> dump.sqlmysql
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
Before MySQL 4.1, use the --opt
and --quote-names
options. As of
4.1, those options are enabled by default.
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 a
MyISAM
table, use this statement:
mysql> ALTER TABLE t1 ENGINE = MyISAM;
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 must rebuild a table because a table checking operation
indicates that the table is corrupt or needs an upgrade, you can
use REPAIR TABLE
if that statement
supports the table's storage engine. For example, to repair a
MyISAM
table, use this statement:
mysql> REPAIR TABLE t1;
For storage engines such as InnoDB
that
REPAIR TABLE
does not support, use
mysqldump to create a dump file and
mysql to reload the file, as described earlier.
For specifics about which storage engines
REPAIR TABLE
supports, see
Section 12.4.2.6, “REPAIR TABLE
Syntax”.
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
shell>db_name
...mysqlcheck --repair --all-databases
User Comments
Add your own comment.