How to fix the corrupted mysql data file _ MySQL-mysql tutorial

Due to temporary power failure, kill-9 can be used to stop the MySQL service process. all these may destroy MySQL data files. If the service is changing the file when it is disturbed, the file may be in an incorrect or inconsistent state. This destruction is sometimes not easy to detect. when you find that this is caused by a temporary power failure, use kill-9 to stop the MySQL service process, all of these may destroy MySQL data files. If the service is changing the file when it is disturbed, the file may be in an incorrect or inconsistent state. This destruction is sometimes not easy to find. it may be a long time before you find this mistake. So when you find this problem, maybe all the backups have the same error.

Chapter 1 of the MySQL Reference Manual describes MySQL's built-in myisamchk function and how to use it to check and repair your MySQL data files. Although this chapter is recommended for anyone who wants to build a strong MySQL service, we still need to discuss the key points here.

Before proceeding, you must realize that the myisamchk program should be unique in accessing the MySQL data file used for checking and modifying. If MySQL is using a file and modifying the file myisamchk is checking, myisamchk will mistakenly think that an error has occurred, and will try to fix it -- this will cause the MySQL service to crash! In this way, to avoid this situation, we usually need to close the MySQL service at work. You can also temporarily disable the service to make a copy of a file and then work on the copy. After you have finished, close the service again and replace the original file with the new file (you may also need to use the change log during the period ).

The MySQL data directory is not hard to understand. Each database corresponds to a sub-directory, and each sub-directory contains files corresponding to the data tables in the database. Each data table corresponds to three files with the same name but different extensions. The tblName. frm file is a table definition that stores the content and type of data columns contained in the table. The tblName. MYD file contains the table data. The tblName. MYI file contains the table index (for example, it may contain a lookup table to help improve the query of the table's primary key columns ).

To check the error of a table, you only need to run myisamchk (in the bin directory of MySQL) and provide the file location and table name, or the index file name of the table:

Myisamchk/usr/local/mysql/var/dbName/tblName
Myisamchk/usr/local/mysql/var/dbName/tblName. MYI

You can run the preceding two commands to check the specified table. To check all tables in the database, you can use wildcards:

Myisamchk/usr/local/mysql/var/dbName/*. MYI

To check all tables in all databases, you can use two wildcards: myisamchk/usr/local/mysql/var/*. MYI

Without any options, myisamchk performs a normal check on the table file. If you have doubts about a table, but normal checks cannot find any errors, you can perform a more thorough check (but it is also slower !), This requires the -- extend-check option:

Myisamchk -- extend-check/path/to/tblName

The check for errors is not destructive, which means you don't have to worry about executing the check on your data files will make the existing problems worse. On the other hand, the repair option is usually secure, but its changes to your data files cannot be undone. For this reason, we strongly recommend that you first make a backup when trying to repair a corrupted table file and ensure that your MySQL service is disabled before the backup is made.

There are three repair types when you try to fix a broken table. If you get an error message indicating that a temporary file cannot be created, delete the file indicated by the information and try again-this is typically left behind by the last repair operation.
The three methods are as follows:

Myisamchk -- recover -- quick/path/to/tblName
Myisamchk -- recover/path/to/tblName
Myisamchk -- safe-recover/path/to/tblName

The first is the fastest, which is used to fix the most common problems, and the last is the slowest, which is used to fix problems that cannot be fixed by other methods.

Check and repair MySQL data files
If the above method cannot repair a damaged table, you can try the following two tips before giving up:

If you suspect that the index file of the table (*. MYI) an irreparable error occurs, or even this file is lost. you can use the data file (*. MYD) and data format file (*. frm. Create a copy of the data file (tblName. MYD. Restart your MySQL service and connect to the service. run the following command to delete the table content:

Mysql delete from tblName;

When deleting the table content, a new index file is created. Log out and close the service again, and overwrite the new (empty) data file with the data file (tblName. MYD) you just saved. Finally, use myisamchk to execute the standard repair (the second method above) and regenerate the index data based on the table data content and the table format file.

If your table format file (tblName. frm) is lost or an irreparable error occurs, but you know how to use the corresponding create table statement to regenerate the TABLE, you can regenerate a new one. frm files are used together with your data files and index files (if there is a problem with the index file, use the above method to recreate a new one. Create a copy of the data and index file, and then delete the original file (delete all records related to the table in the data directory ).

Start MySQL and use the original create table file to CREATE a new TABLE. The new. frm file should work properly, but you 'd better execute the standard fix (the second method above ).

You can use a mysql statement and check table $ table; // $ table indicates the name of the data TABLE. CHECK whether the table is damaged. if the TABLE is damaged, you can use REPAIR table $ table to fix the problem. if not, it can be repaired several times more!

If you use the WIN server, you can first log on to the DB server and enter this path, instead of entering mysql.

D: \ CD MYSQL

D: \ mysql> CD BIN

D: \ mysql \ bin> MYISAMCHK-r d: \ mysql \ data \ guild \ msg_table.myi (msg_table)
-Check key delete-chain
-Check record delete-chain
-Recovering (with sort) MyISAM-table 'd: \ mysql \ data \ guild \ msg_table.myi'
Data records: 23
-Fixing index 1

D: \ mysql \ bin> MYISAMCHK -- safe-recover d: \ mysql \ data \ guild \ msg_table.myi
-Recovering (with keycache) MyISAM-table 'd: \ mysql \ data \ guild \ msg_table.myi'
Data records: 89

D: \ mysql \ bin>

If you do not know which table is broken, you can access the mysql database:
Use guild;
Desc msg_table; check whether msg_table is bad.

Fixed the problem by entering the MYSQL database and running the command check: show table;