How to conduct MySQL database table fault detection _ MySQL

How to conduct MySQL database table fault detection you may encounter various unexpected damages to the database table when using MySQL, and the data is often the latest data, it is generally not possible to find the data in the backup. This chapter describes how to detect MySQL database table faults.

The general process of fault detection and correction in the table is as follows:

◆ Check 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 with the operation, 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 during 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. the usage of myisamchk and isamchk is as follows:

Shell> myisamchk options tbl_nameshell> 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 (". 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:

Shell> myisamchk *. MYIshell> isamchk *. ISM

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

Shell> myisamchk options/path/to/database_dir/*. MYIshell> 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:

Shell> myisamchk options/path/to/datadir/*. MYIshell> 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:

Myisamchk tbl_nameisamchk 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:

Myisamchk-e tbl_nameisamchk-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

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 statistical information, for example:

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