Checking and Fixing Databases

Checking and Fixing Databases

Over time, databases can become corrupted or store information inefficiently. MySQL comes with commands that you can use to check and repair your databases. The myisamchk and isamchk commands are available to check MyISAM and ISAM database tables, respectively.

MyISAM tables are used by default with MySQL. The tables are stored in the directory /var/lib/mysql/dbname by default, where dbname is replaced by the name of the database you are using. For each table, there are three files in this directory. Each file begins with the table name and ends with one of the following three suffixes:


Contains the definition (or form) or the table


Contains the table's index.


Contains the table's data.

The following procedure describes how to use the myisamchk command to check your MyISAM tables. (The procedure is the same for checking ISAM tables, except that you use the isamchk command instead.)


Do a backup of your database tables before running a repair with myisamchk. Though myisamchk is unlikely to damage your data, backups are still a good precaution.

  1. Stop MySQL temporarily by typing the following from a Terminal window as root user:

    # /etc/init.d/mysqld stop
  2. You can check all or some of your database tables at once. The first example shows how to check a table called names in the allusers database.

    # myisamchk /var/lib/mysql/allusers/names.MYI
    Checking MyISAM file: /var/lib/mysql/allusers/names.MYI
    Data records:       5   Deleted blocks:       0
    - check file-size
    - check key delete-chain
    - check record delete-chain
    - check index reference
    - check record links

    You could also check tables for all your databases at once as follows:

    # myisamchk /var/lib/mysql/*/*.MYI

    The preceding example shows a simple, five-record database where no errors were encountered. If instead of the output shown above, you see output like the following, you may need to repair the database:

    Checking MyISAM file: names.MYI
    Data records:       5   Deleted blocks:       0
    - check file-size
    myisamchk: warning: Size of datafile is: 89 Should be: 204
    - check key delete-chain
    - check record delete-chain
    - check index reference
    - check record links
    myisamchk: error: Found wrong record at 0
    MyISAM-table 'names.MYI' is corrupted
    Fix it using switch "-r" or "-o"
  3. To fix a corrupted database, you could run the following command

    # myisamchk -r /var/lib/mysql/allusers/names.MYI
    - recovering (with keycache) MyISAM-table 'names.MYI'
    Data records: 5
    Found wrong stored record at 0
    Data records: 4
  4. If for some reason the -r options doesn't work, you can try running the myisamchk command with the -o option. This is a slower, older method of repair, but it can handle a few problems that the -r option cannot. Here is an example:

    # myisamchk -o /var/lib/mysql/allusers/names.MYI

If your computer has a lot of memory, raise the key buffer size value on the myisamchk command line, which will lessen the time it takes to check the databases. For example, you could use the following command line:

 myisamchk -r -O --key_buffer_size=64M *.MYI

This would set the key buffer size to 64MB.

Part IV: Red Hat Linux Network and Server Setup