MySQL data table Damage Repair method detailed

Source: Internet
Author: User
Tags mysql in

1. Table Damage Reason analysis

The following are common causes of MySQL table corruption:
1, the sudden loss of power to the server caused data file corruption.
2, forced shutdown, did not first shut down the MySQL service.
3. The mysqld process was killed while writing the table.
4, the use of myisamchk at the same time, Mysqld also in the operating table.
5, disk failure.
6, the server crashed.
7, the MySQL itself bug.

2. Symptom of table damage

A typical symptom of a damaged table is as follows:
1. When selecting data from a table, you get the following error:
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 ' P ' is marked as crashed and should to be repaired
4, open the table failed: Can ' t open file: ' xxx. Myi ' (errno:145)

3. Repair method

Log on to MySQL using the command line (MySQL in the bin under the MySQL installation path)

Login MySQL, execute

The code is as follows Copy Code

Use DatabaseName;

Repair table TableName;

It's the simplest way, so let's take a look at it.

1. Check table and Repair table

Login to MySQL terminal:

The code is as follows Copy Code

Mysql-uxxxxx-p dbname
> Check table tabtest;

If the results say that the status is OK, then do not repair, if there is error, you can use:

The code is as follows Copy Code

> Repair table tabtest;

Repair and can be checked with the check Table command after repair. The Check/repair feature can also be used in the new version of phpMyAdmin.

2. Myisamchk, Isamchk

Where Myisamchk applies to myisam types of data tables, ISAMCHK to ISAM types of data tables. The main parameters of these two commands are the same, and generally the new system uses MyISAM as the default datasheet type, which is illustrated with Myisamchk as an example. When you find a problem with a data table, you can use:

The code is as follows Copy Code

MYISAMCHK TableName. Myi

For testing, if you need to fix it, you can use:

The code is as follows Copy Code


For a detailed parameter description of MYISAMCHK, see its use help. Need to be aware of when making changes must ensure that the MySQL server does not access this data table, insurance is the best in the case of the MySQL server shutdown off.


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

The code is as follows Copy Code

[-x/tmp/mysql.sock] &&/pathtochk/myisamchk-of/data_dir/*/*. Myi

The/tmp/mysql.sock is the sock file location that MySQL listens to, the user that uses RPM installs should be/var/lib/mysql/mysql.sock, to use the source code installs is/tmp/ Mysql.sock can be changed according to their actual situation, and Pathtochk is Myisamchk location, Data_dir is your MySQL database storage location.

To be aware, if you are going to put this command in your rc.local, you must confirm that the MySQL server must not be started when executing this instruction!

Detect repair of all databases (table)


  code is as follows copy code

mysql> Check table tabftpaccountinstances;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.