How to Implement fault detection in the MySQL database table?

When we use the MySQL database, we may encounter many unexpected damages to the database table, and the data is new data. We usually look for the backup data, but in general, it cannot be found. 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 the tables with errors. If the MySQL database table passes the check, 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 MySQL database table you want to check or repair. The usage of myisamchk and isamchk 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:

 
 

  
  
shell> myisamchk *.MYI shell>isamchk *.ISM   
 
 

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

 
 

  
  
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 MySQL database 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 MySQL database tables using standard methods. You can directly call them without any options, or use either the-s or -- silent options:

 
 

  
  
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:

 
 

  
  
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

 
 

  
  
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, you can be sure that the MySQL database 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:

 
 

  
  
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 modified MySQL database table after the last check is checked.