How to repair MySQL database tables

Question

Repairing MySQL database tables that will not open.

Answer 1

This should be handled on a case by case basis, but if you are using the default MySQL table type of MyISAM (which is the default storage engine unless changed or specified differently) here are a few options:

1. The myisamchk utility can be run from a command line to checks, repairs, or optimizes tables. It is normally run while the database is not running.

# service mysql stop
# myisamchk -rf /var/lib/mysql/DATABASE/*.MYI

-r (recover) : Do a repair that can fix almost any problem except unique keys that aren't unique
-f (force) : Do a repair operation automatically if myisamchk finds any errors in the table

# service mysql start


2. mysqlcheck is similar in function to myisamchk, but can be run while the database is running.

For Check tables
# mysqlcheck -c DATABASENAME
For Repair tables
# mysqlcheck -r DATABASENAME
For Optimize tables
# mysqlcheck -o DATABASENAME


3. If you login to the database, you can also run sql commands that might fix your problem.
Examples:

mysql> use DATABASENAME;
mysql> optimize table DB-tablename;
mysql> analyze table DB-tablename;
mysql> repair table DB-tablename;

4. If you are getting MySQL error numbers and are not sure what they are. From a command line you can use the perror utility to lookup errors.
Examples:
shell> perror 13
Error code 13: Permission denied