MYSQL : Checking and Reparing tables

If your database is MySQL, though, there's still hope. Over the course of this article, I will show you how to use MySQL's built-in crash recovery tools to check your database, and hopefully recover all the data you just lost.

Built-in tools
When it comes to repairing and checking tables, MySQL offers two options:

1. myisamchk designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line.
2. MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

Most of the time, it's preferable to use myisamchk because it is significantly faster than using SQL commands.

Checking tables
If you're having trouble accessing a table, first try checking it for errors. To check a table, shut down the server and type myisamchk at the command prompt, followed by the table's file name, as shown below:
$ myisamchk /usr/local/mysql/data/phpbb/user.myi
Checking MyISAM file: /usr/local/mysql/data//user.MYI
Data records: 1153 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links

Use the complete path to the table file instead of just the table name. Remember to include the file extension as well.

myisamchk options :
myisamchk -- fast Perform a quick check, only verifying if the table handler closed successfully
myisamchk -- medium-check Perform a faster check, by calculating a checksum for the indexes in each record and verifying that checksum against the index tree
myisamchk -- extend-check Perform a thorough check of the table, verifying the data in each record
Command line arguments for myisamchk

Repairing tables

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Also advisable table physical backup of database.

Once the table file is backed up and the server down, you can run myisamchk with the --recover option, as shown below:
$ myisamchk --recover /usr/local/mysql/data/phpbb/user.myi
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/phpbb/user.MYI'Data records: 1153
- Fixing index 1

The --recover option reconstructs the MySQL table index file after weeding the corrupted table of invalid or previously deleted records that could be causing a problem.

In the unlikely event that the --recover option fails, revert to the original table file and try the --safe-recover option. This is slower, because MySQL needs to scan through the records in the data file one by one and them restore the index, but it can sometimes work better than a regular repair operation.
$ myisamchk --safe-recover /usr/local/mysql/data/phpbb/user.myi
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/phpbb/user.MYI'
Data records: 4

Using CHECK TABLE and REPAIR TABLE

check and repair operations are run from a MySQL prompt, via the CHECK TABLE and REPAIR TABLE commands.

Consider the following example of running a CHECK TABLE command:
mysql> CHECK TABLE user;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| phpbb.user | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.02 sec)

You can add the FAST, MEDIUM, and EXTENDED keywords to the command to obtain behavior similar to that available in myisamchk. For example:
mysql> CHECK TABLE user EXTENDED;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| phpbb.user| check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.02 sec)

When it comes to repairing tables, too, MySQL offers the REPAIR TABLE option, as below:
mysql> REPAIR TABLE user;
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| phpbb.user | repair | status | OK |
+--------------+--------+----------+----------+
1 row in set (0.01 sec)

As with the myisamchk tool, the REPAIR TABLE command can take one of two additional options: QUICK, which tries a quick repair, and EXTENDED, which rebuilds the index after reading each record in the table.

An option here is to use the mysqlcheck utility, which provides a command line front end to the CHECK TABLE and REPAIR TABLE commands, allowing you to check and repair tables without first taking the server down. Everything that you can do with CHECK TABLE and REPAIR TABLE can be done with mysqlcheck, by passing appropriate command line options to the program.