MySQL common fault detection and correction

The general process of fault detection and correction for a table is as follows:

Check for errors in the table. If the table checks through, complete the task, or you must fix the database table that is wrong.

Copy the table files before starting the repair to keep the data secure.

Start repairing database tables.

If the repair fails, restore the data from the backup or update log of the database.

Before you use Myisamchk or isamchk to check or fix a table, you should first note:

Establish database backups and use update logs to prevent repair failures and data loss.

Read this chapter carefully before you do it, especially before reading the "Avoid interaction with MySQL server." Because, before you do not have enough knowledge to operate rashly, can cause serious consequences.

If you are maintaining a table on a UNIX platform, you should first register to a dedicated account MySQL to avoid the problem of ownership of table read and write access, and to destroy all permissions on the database directory.

Maintenance tools for database tables

MySQL's myisanchk and Isamchk utilities are similar, and basically they have the same usage. The type of table used when the main difference between them is made. To check/repair the MyISAM table (. Myi and. MyD), you should use the Myisamchk utility. To check/repair the ISAM table (. ISM and. ISD), you should use the Isamchk utility.

In order to use any one of the programs, you should indicate the table you want to check or fix, and the Myisamchk and Isamchk methods are:

Shell>myisamchk options tbl_name shell>isamchk options Tbl_name
If you want, you can name several tables at the command line.

You can also specify a name as an index file (with ". Myi "or". ISM "suffix", which allows you to use mode "*. Myi "or". ISM "Specifies all tables in a directory. For example, if you are in a database directory, you can check all the tables in the directory like this:

Shell> Myisamchk *. Myi Shell>isamchk *. Ism
If you are not in the database directory, you can specify the path to the directory:

Shell> Myisamchk options/path/to/database_dir/*. Myi shell> isamchk options/path/to/database_dir/*. Ism
You can even specify a wildcard character for the MySQL data directory to use for all tables in all databases:

Shell> Myisamchk options/path/to/datadir/*/*. Myishell> Isamchk options/path/to/database_dir/*/*. Ism
This method is not available under Windows platform.

Note that both Myisamchk and isamchk do not make any judgments about where the table is located, so you should either run the program in the directory containing the table file or specify the path name of the table. This allows you to copy the table file to another directory and use the copy to operate it.

Check database tables

Myisamchk and Isamchk provide table-checking methods that differ in the degree to which the table is thoroughly examined.

　　Standard Method Checklist

It is usually sufficient to use the standard method. Use a standard method for checking a table, call it directly without using any option, or use any of the-s or--silent options:

Myisamchk tbl_name Isamchk Tbl_name

This will find 99.99% of all errors. What it does not find is just the damage to the data file (which is very uncommon).

　　Complete and thorough data checking

To perform an extended check, use the--extend-check or the-e option to check the data:

　　

Myisamchk-e tbl_name isamchk-e Tbl_name

It does a completely thorough data check (-e means "extended check"). It does a read check of each key for each row to verify that they actually point to the correct line. This can take a long time on a large table with many keys. Myisamchk usually stops after it finds the first error. If you want to get more information, you can add the--verbose (-V) option. This allows myisamchk or isamchk to continue until up to 20 errors. In general use, a simple standard check (without a parameter other than the table name) is sufficient.

　　Moderate level of inspection

Specify options--medium-check or-M

Myisamchk-m Tbl_name

A moderate check is not as thorough as an extended check, but faster. It has little significance and is less used.

If you do not report an error for the--extend-check check, you can be sure that the table is intact. If you still feel a problem with your watch, the reason must be somewhere else. You should re-examine people and queries that appear to be problematic to verify that the query is written correctly. If you think the problem may be the result of a MySQL server, you should consider organizing a fault report or upgrading to a new version.

　　Potentially useful check options:

1.-i or-information Print statistical information, such as:

Myisamchk-e-I. Tbl_name

Like the previous command, but the-I option tells Myisamchk to print out some statistics as well.

2.-c,--check-only-changed

Check only the tables that have been modified since the last check