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 command is available to check MyISAM database tables.

MyISAM tables are used by default with MySQL. (To use a different table type, you can assign it when you first create your MySQL table.) 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:

.frm	Contains the definition (or form) or the table
.MYI	Contains the table's index.
.MYD	Contains the table's data.

The following procedure describes how to use the myisamchk command to check your MyISAM tables.

Caution 

Do a backup of your database tables before running a repair with myisamchk . Although 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 in the previous example, 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 option 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, you can 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.