MySQL data table Damage Repair method detailed

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
MYISAMCHK-OF TableName. Myi

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)
Mysqlcheck-a-o-r-P
---------------------------------------------------------------------------------

Example:

code is as follows	copy code
mysql> Check table tabftpaccountinstances;