MySQL database table repair MyISAM

I. Summary of the causes of MyISAM Table Corruption in MySQL:

1. The data file is damaged due to a sudden power failure on the server. The mysql service is forcibly shut down without being shut down first. The mysqld process is killed when writing a table.
2. the disk is damaged.
3. The server crashes.
4. mysql bug.

Ii. Summary of symptoms of MyISAM Table Corruption in MySQL:

1. An error is returned when querying data: Incorrect key file for table: '...'. Try to repair it
2. the query cannot find rows in the table or return incomplete data.
3. Error: Table '...' is marked as crashed and shoshould be retried red.
4. failed to open the table: Can't open file: '×××. myi' (errno: 145 ).

Iii. Prevention of MyISAM Table Corruption in MySQL:

1. Check the MyISAM table regularly. You can use myisamchk, mysqlcheck, or check table.
2. After a large number of update or delete operations, use mysqlcheck or optimize table to optimize the table. This reduces file fragmentation and the probability of table corruption. 3. Shut down mysqld before shutting down the server (close the service normally, do not use kill-9 to kill the process ).
4. Use the ups power to avoid sudden power failure.
5. Use the latest stable mysql version to reduce table corruption caused by mysql bugs.
6. Perform raid on the disk to reduce disk errors and improve performance.
7. It is best for the database server to run only mysqld and other necessary services. Do not run other services. This reduces the possibility of table corruption caused by crashes.

Iv. Repair Summary of MyISAM Table Corruption in MySQL:

1. If mysqld is down and cannot be started, you can use mysiamchk to fix it. This tool can be used when the mysqld service is not started. This tool can check and analyze and repair the MyISAM table.
2. If mysqld is still running, or you can restart it, you can fix it using the mysqlcheck tool. Alternatively, you can use mysql's built-in repair SQL statement to fix: CHECK TABLE, REPAIR TABLE, ANALYSE TABLE, and OPTIMIZE TABLE. These two methods can also achieve table repair. The two methods have their own application scenarios.

Before restoring a MySQL table, you must first know which table is faulty.

You can use the root user to log on to information_schema and find the problematic table name.

Select TABLE_SCHEMA as DB_DBNAME, TABLE_NAME from INFORMATION_SCHEMA.tables where engine is null;

Can be found in the event viewer?

Application log -- error log of the type "error" -- error log of the event source "Mysql", the approximate content is

1 xxxxx: Table '. xxxx' is marked as crashed and shoshould be retried red

You can also directly check which tables have problems in phpmyadmin, such as displaying "in use"

If the problem table is found, run the following command to fix the MYISAM table in the mysql database:

1. check, repair

For example, the test table of abc has a problem.

Copy codeThe Code is as follows:
# Mysql-u root-p 'abc'; // log on to the mysql console and enter the abc Database
Mysql> check table test; // if there is a problem Msg_type, there will be a warning or error. If no problem occurs, the result Status is OK.
Mysql> repair table test; // fix the test table (multiple table names are separated by commas)
Mysql> check table test; // check again to check whether the repair is successful.

2. myisamchk, isamchk

Here, myisamchk applies to MYISAM data tables, while isamchk applies to ISAM-type MySQL Data Tables. The main parameters of these two commands are the same. Generally, new systems use MYISAM as the default data table type. Here we use myisamchk as an example. When a problem occurs in a data table, you can use:

Myisamchk tablename. MYI

To fix the issue, use:

Myisamchk-of tablename. MYI

For detailed parameter descriptions of myisamchk, see its help. When modifying the data table, make sure that the MySQL server does not access the data table. It is safe to shut down the MySQL server during detection.

Mysqladmin-uroot-p shutdown

In addition, you can place the following command in your rc. local to start the MySQL server:

[-X/tmp/MySQL. sock] & amp;/pathtochk/myisamchk-of/DATA_DIR/*. MYI

/Tmp/MySQL. sock is the location of the Sock file monitored by MySQL. For RPM-based installation, it should be/var/lib/MySQL. sock. For source code installation, it is/tmp/MySQL. sock can be changed based on actual conditions, while pathtochk is the location where myisamchk is located, and DATA_DIR is the location where your MySQL database is stored.

Note that if you plan to place this command in your rc. local, make sure that the MySQL server is not started when executing this command!

3. Check and repair all databases (tables)

Copy codeThe Code is as follows:
MySQLcheck-A-o-r-p
Mysqlcheck -- all-databases-r