Troubleshooting of MySQL tables _ MySQL

This article mainly introduces how to detect MySQL table faults. the methods provided in this article are based on the myisamchk and isamchk detection tools, you can refer to the following table for troubleshooting and correction. the general process is as follows:

Check the tables with errors. If the table check is successful, the task is completed. Otherwise, the database table with errors must be repaired.

Copy the table file before starting the restoration to ensure data security.

Start restoring database tables.

If the restoration fails, recover data from the database backup or update log.
Before using myisamchk or isamchk to check or repair a table, note the following:

Create database backup and use Update logs to prevent repair failures and data loss.

Read this chapter carefully before proceeding, especially before reading "avoid interacting with MySQL server. Because it may cause serious consequences if you do not have enough knowledge.

If you maintain a table on the Unix platform, you should first register a dedicated account mysql to avoid ownership issues arising from table read/write access and damage all permissions of the Database Directory.

Database table maintenance tools

MySQL's myisanchk and isamchk utilities are similar, basically they have the same usage method. The type of the table used for the main differences between them. To check/fix MyISAM tables (. MYI and. MYD), you should use the myisamchk utility. To check/repair ISAM tables (. ISM and. ISD), you should use the isamchk utility.

To use any program, specify the table you want to check or repair. use myisamchk and isamchk as follows:

The code is as follows:

Shell> myisamchk options tbl_name

Shell> isamchk options tbl_name

If you want to, you can name several tables in the command line.

You can also specify a name as an index file (using ". MYI "or". ISM), which allows you to use the mode "*. MYI "or". ISM specifies all tables in a directory. For example, if you are in a database directory, you can check all tables in the directory as follows:

The code is as follows:

Shell> myisamchk *. MYI

Shell> isamchk *. ISM

If you are not in the database directory, you can specify the directory path:

The code is as follows:

Shell> myisamchk options/path/to/database_dir/*. MYI

Shell> isamchk options/path/to/database_dir/*. ISM

You can even specify a wildcard for the path of the MySQL data directory to act on all tables in all databases:

The code is as follows:

Shell> myisamchk options/path/to/datadir/*. MYI

Shell> isamchk options/path/to/database_dir/*. ISM

This method cannot be used on windows.

Note that neither myisamchk nor isamchk can determine the location of the table. Therefore, you should run the program in the directory containing the table file or specify the table path name. This allows you to copy the table file to another directory and use the copy operation.

Check database tables

Myisamchk and isamchk provide table check methods that differ in the extent to which a thorough checklist is performed.

Standard method checklist

Generally, the standard method is enough. Check the table using a standard method. you can directly call the table without any options, or use either the-s or -- silent options:

The code is as follows:

Myisamchk tbl_name

Isamchk tbl_name

This can identify the 99.99% of all errors. What it cannot find is that it only involves corruption of data files (which is not common ).

Complete and thorough data check

To perform the expansion check, use the -- extend-check or-e option, which checks the data:

The code is as follows:

Myisamchk-e tbl_name

Isamchk-e tbl_name

It performs a completely thorough data check (-e means "extension check "). It performs a read check on each row for each key to verify that they actually point to the correct row. This can take a long time on a large table with many keys. Myisamchk usually stops after it discovers the first error. If you want more information, you can add the -- verbose (-v) option. This causes the myisamchk or isamchk to continue up to 20 errors. In general use, a simple standard check (without parameters other than the table name) is enough.

Moderate check

Specify options -- medium-check or-m

The code is as follows:

Myisamchk-m tbl_name

A moderate check is not as fast as a full extension check. It is of little significance and rarely used.

If no error is reported for the -- extend-check, the table is intact. If you still feel that the table is faulty, the reason must be somewhere else. Re-check and queries that appear to be problematic to verify that the query is correctly written. If you think the problem may be caused by the MySQL server, you should organize a fault report or upgrade it to a new version.
Possible check options:

1.-I or-information: print the statistical information, for example:

The code is as follows:

Myisamchk-e-I tbl_name

Like the previous command, the-I option tells myisamchk to print some statistics.

2.-C, -- check-only-changed

Only the table modified after the last check is checked