How to Rebuild Indexes on MYSQL

When a system error happens to a mySql database, or when a table simple receives a large amount of incorrectly formatted data, the table indexes may become corrupt. The mySql system will inform a user that the table needs to be repaired when a CHECK TABLE command is run on the table. The command to verify the table needs repairing looks like this: CHECK TABLE tablename FOR UPGRADE. If the mySql system responds with "Table upgrade required," then the table needs the indexes rebuilt.

Difficulty: Challenging

Instructions

1. Using telnet, ssh, or a local machine, connect to the mySql server and log into the mySql command-line tool. This can be done with mysql -p.

2. Use the CHECK TABLE tablename FOR UPGRADE command to verify the table needs to be repaired.

3. Exit the mysql command-line tool by typing x and pressing the "Enter" key. You will return to the shell command prompt.

4. Dump the table with the mysqldump command: mysqldump databaseName tableName > dump.sql.

5. Re-create the table in the database with the mysql command: mysql databaseName < dump.sql. This will rebuild the table and the table indexes.

References

• Rebuilding Tables and Indexes