MySQL database index corruption and repair experience sharing

This article mainly introduces the MySQL database index damage and repair experience sharing, need friends can refer to the following

The problem and solution of MySQL table index being corrupted

Afternoon work, surprised to smell my Dedecms website problem, visit a look, really full screen error, check MySQL log, error message is:

Table '. Dedecmsv4dede_archives ' is marked as crashed and should are repaired

Hint that the CMS article table dede_archives is marked with problems that need to be repaired. So quickly restore historical data, the Internet to find out why. Eventually solve the problem. The workaround is as follows:

Locate the Bin/myisamchk tool for the MySQL installation directory and enter at the command line:

Myisamchk-c-R. /data/dedecmsv4/dede_archives. Myi

The Myisamchk tool will then help you recover the index of the datasheet. Reboot MySQL, problem solved.

Problem Analysis:

1, the cause of the error, a netizen said is frequent query and update dede_archives table caused by the index error, because my page is not static generation, but dynamic page, so more agree

This statement. It is also said that the MySQL database for some reason has been damaged, such as: Database server sudden power outages, in the database table to provide services to the table of the original file

Doing something may cause the MySQL database table to be corrupted and not read data. In short, the damage to the table is caused by some unpredictable problems.

2. Problem solving method.

When you try to fix a problem with a damaged table, there are three types of fixes. If you get an error message stating that a temporary file cannot be created, delete the information indicated by the file and then

Try it once--this is usually left over from the last repair operation.

The three repair 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, to fix the most common problems, and the last one is the slowest to fix problems that some other methods cannot fix.

Review and repair MySQL data files

If the above method cannot fix a damaged table, you can try these two techniques before you give up:

If you suspect that the index file of the table (*. Myi An unrecoverable error occurred, or even lost the file, you can use the data file (*. MYD) and data Format files (*.frm) re-Renaissance

Into it. First, make a copy of the data file (TBLNAME.MYD). To restart your MySQL service and connect to this service, delete the contents of the table using the following command:

Mysql> DELETE from Tblname;

When you delete the contents of a table, a new index file is created. Quit logging on and shut down the service and overwrite the new (empty) data with the data file (tblname.myd) you just saved

File. Finally, use MYISAMCHK to perform a standard fix (the second method above) to regenerate index data based on the contents of the table's data and the table's format file.

If your table's format file (TBLNAME.FRM) is missing or an unrecoverable error occurs, you know how to use the corresponding create

Table statement, you can regenerate a new. frm file and and your data files and index files (if there is a problem with the index file, use the method above to reconstruct a

A new one) is used together. First make a copy of the data and index file, and then delete the original file (delete all the records in the data directory about the table).

Start the MySQL service and use the original create

Table file to create a new one. The new. frm file should work, but the best thing to do is to perform the standard fix (the second method above).

3, Myisamchk tool Introduction (see the official MySQL manual)

You can use the Myisamchk utility to get information about a database table or to check, fix, and optimize them. Myisamchk applicable to the MyISAM table (corresponding. Tables for myi and. myd files).

method to invoke Myisamchk:

Shell> myisamchk [options] tbl_name ...

Options specify what you want Myisamchk to do. Describe them later. You can also get a list of options by calling Myisamchk--help.

Tbl_name is the database table you want to check or fix. If you do not run Myisamchk somewhere in the database directory, you must specify the path to the database directory because Myisamchk does not know

Where your database is located. In fact, Myisamchk doesn't care if the file you're working on is in a database directory; You can copy files that correspond to a database table to another location and

and perform a recovery operation there.

If you prefer, you can name several tables using the MYISAMCHK command line. You can also name an index file by using the

. Myi "suffix) to specify a table. It allows you to use the mode "*. Myi "Specifies all the tables in a directory. For example, if you are in the database directory, you can check this in the directory

All MyISAM tables:

Shell> Myisamchk *. Myi

If you are not in the database directory, you can check all tables that are there by specifying the path to the directory:

Shell> myisamchk/path/to/database_dir*. Myi

The recommended way to quickly check all MyISAM tables is:

Shell> myisamchk--silent--fast/path/to/datadir*. Myi

This command assumes that you have free memory greater than 64MB. For more information about allocating memory with MYISAMCHK, see section 5.9.5.5, "Myisamchk memory Usage"

When you run Myisamchk, you must make sure that the other programs do not use the table. Otherwise, when you run Myisamchk, the following error message is displayed:

Warning:clients are using or haven ' t closed the table properly

This means that you are trying to check a program that is being shut down by another file that has not been closed or has terminated without properly closing the file (for example, the MYSQLD server)

If Mysqld is running, you must pass flush

Tables force empty Any table modifications that are still in memory. When you run Myisamchk, you must make sure that the other programs do not use the table. The easiest way to avoid this problem is to use check

Table without myisamchk to check the tables.