Rebuild or repair a MySQL table or index

# Table and index changes refer to the way MySQL handles data types and character sets. CHECK TABLE, mysqlcheck, or mysql_upgrade reports necessary table repairs or upgrades.

Rebuild the table

There are many ways to rebuild the table. Some of these methods are listed below -

• Dump and Reload Method

• ALTER TABLE Method

• Repair Table Methods

Let us briefly understand them -

Dump and Reload Method

If due to different version of MySQL version This dump and reload method is required when a table cannot be processed after a binary (in-place) upgrade or download and the table needs to be rebuilt.

Tables can be rebuilt by dumping and reloading. This can be done by using "mysqldump" and creating a dump file and allowing mysql to reload the file. This can be done using the following command -

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

If all tables have to be rebuilt in a single database, you can specify the database name without using any table name. This can be done using the following command -

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

If all tables in all databases have to be rebuilt, the "--all databases" option must be used. This can be done using the following command -

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

ALTER TABLE method

If you need to rebuild the table using the ALTER TABLE method, you can use the "null" change. You can use the ALTER TABLE statement to alter a table so that it can use the storage engine. Let us take an example: Suppose TblName is an InnoDB table, the following statement can be used to rebuild the table -

ALTER TABLE TblName ENGINE = InnoDB;

REPAIR TABLE method

REPAIR TABLE method is only available for MyISAM, ARCHIVE and CSV tables . If a table check operation indicates that the file is corrupted or needs to be upgraded, you can use the statement REPAIR TABLE. Let us take an example: To repair a MyISAM table, the following statement can be executed -

REPAIR TABLE TblName;

mysqlcheck --repair Provides command line access to the REPAIR TABLE statement. This may be a more convenient way to repair tables, as you can use --databases to repair specific tables in a database, or the --all-databases option to repair all tables in all databases. This can be done using the following command -

mysqlcheck −−repair −−databases db_name ...
mysqlcheck −−repair −−all−databases

The above is the detailed content of Rebuild or repair a MySQL table or index. For more information, please follow other related articles on the PHP Chinese website!