Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to detect the fault of the data table in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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

◆ checks the table that is in error. If the table check passes, the task is completed, otherwise the incorrect database table must be repaired.

◆ copies the table file before starting the repair to keep the data safe.

◆ begins to repair the database tables.

◆ recovers data from the backup or update log of the database if the repair fails.

Before using myisamchk or isamchk to check or repair a table, you should first note that:

◆ sets up database backups and uses update logs in case repair fails and data is lost.

◆ read this chapter carefully before doing anything, especially before you read "avoid interacting with MySQL servers." Because, operating rashly before you do not have enough knowledge may lead to serious consequences.

◆ if you maintain tables on the Unix platform, you should first register with the dedicated account mysql to avoid ownership problems with read and write access to the tables and to break all permissions in the database directory.

Maintenance tools for database tables

MySQL's myisanchk and isamchk utilities are very similar, basically they have the same usage. The main difference between them is the type of table used. To check / repair 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.

In order to use any of the user programs, ◆ should specify the tables you want to check or repair. The ways to use myisamchk and isamchk are:

Shell > myisamchk options tbl_nameshell > isamchk options tbl_name

You can name several tables on the command line if you like.

◆ you can also specify a name as an index file (with the suffix ".MYI" or ".ISM"), which allows you to specify all tables in a directory by using the pattern "* .MYI" or ".ISM". 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/*.MYIshell > isamchk options / path/to/database_dir/*.ISM

◆ you can even act on all tables in all databases by specifying a wildcard for the path to the MySQL data directory:

Shell > myisamchk options / path/to/datadir/*/*.MYIshell > isamchk options / path/to/database_dir/*/*.ISM

This method cannot be used under the platform.

Note that neither myisamchk nor isamchk makes any judgment about the location of the table, so you should either run the program in the directory that contains the table file, or specify the path name of the table. This allows you to copy the table file to another directory and operate with that copy.

Check list

Myisamchk and isamchk provide methods for checking tables, which differ in the extent to which tables are thoroughly checked.

Standard method checklist

Standard methods are usually sufficient. Check the table using the standard method, call it directly without any options, or use any of the-s or-- silent options:

Myisamchk tbl_nameisamchk tbl_name

This can find 99.99% of all errors. What it cannot find is that it only involves corruption in the data file (which is very unusual).

A complete and thorough data check

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

Myisamchk-e tbl_nameisamchk-e tbl_name

It does a complete and thorough data check (- e means "extension check"). It does a read check of each key on each line to confirm that they do 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 more information, you can add the-verbose (- v) option. This causes myisamchk or isamchk to continue to a maximum of 20 errors. In general use, a simple standard check (no parameters other than the table name) is sufficient.

A moderate inspection

Specify options-- medium-check or-m

Myisamchk-m tbl_name

The moderate inspection is not as thorough as the extended inspection, but it is faster. It has little significance and is seldom used.

If the-- extend-check check does not report an error, you can be sure that the table is intact. If you still feel that there is something wrong with your watch, it must be somewhere else. People and seemingly problematic queries should be re-examined to verify that the queries are written correctly. If you think the problem may be the cause of the MySQL server, you should consider compiling a failure report or upgrading to a new version.

Check options that may be useful:

1. Muri or-information prints statistics, such as:

Myisamchk-e-I tbl_name

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

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

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

After reading the above, have you mastered the method of fault detection of the data table in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.