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:

.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. (The procedure is the same for checking ISAM tables, except that you use the isamchk command instead.)

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 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, 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.




Red Hat Fedora Linux 3 Bible
Red Hat Fedora Linux 3 Bible
ISBN: 0764578723
EAN: 2147483647
Year: 2005
Pages: 286

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net