How to Use Myisamchk for MySQL crash recovery

The file format used by MySQL to store data has been widely tested, but there are always external conditions that can cause damage to the database table:

The mysqld process is killed in a write; the computer is accidentally shut down (for example, if the computer powers down); a hardware error.

This chapter describes how to check and process data corruption in the MySQL database. If your table is damaged a lot, you should try to find out the cause! See G.1 debug a MySQL server.

During crash recovery, it is important to understand the three files in the database directory corresponding to each table tbl_name in a database:

File Usage

 

"Tbl_name.frm" table definition (table) file "tbl_name.MYD" data file "tbl_name.MYI" index file

Each file type of these three files may be damaged in different forms, but the problem occurs most often in data files and index files.

Myisamchk creates a ". MYD "(data) file copy to work, it is deleted by the old". MYD file and rename the new file to the original file name to end the repair phase. If you use -- quick, myisamchk does not create a temporary ". MYD file, just assume ". MYD "file is correct and only one new index file is created, not touching". MYD file, which is safe, because myisamchk automatically detects ". whether the MYD file is damaged and in this case, discard the repair. You can also give myisamchk two -- quick options. In this case, myisamchk will not give up on some errors (such as duplicate keys). Instead, it tries to solve them by modifying the ". MYD" file. Generally, two -- quick options are useful only when you implement a normal restoration in a small amount of free disk space. In this case, you should make a backup at least before running myisamchk.

I. How to check table errors

To check a table, run the following command:

Myisamchk tbl_name

This can identify the 99.99% of all errors. What it cannot find is that it only involves corruption of data files (which is not common ). If you want to check a table, you should usually run myisamchk or any one with the-s or -- silent options without any options.

Myisamchk-e tbl_name

It performs a completely thorough data check (-e means "extension check "). It performs a read check on each row for each key to verify that they actually point to the correct row. This can take a long time on a large table with many keys. Myisamchk usually stops after it discovers the first error. If you want more information, you can add the -- verbose (-v) option. This allows myisamchk to continue up to 20 errors. In general use, a simple myisamchk (without parameters other than the table name) is enough.

Myisamchk-e-I tbl_name

Like the previous command, the-I option tells myisamchk to print some statistics.

Ii. how to repair a table

The symptoms of a damaged table are usually query unexpected interruptions and you can see such errors:

"Tbl_name.frm" is locked and cannot be changed.

The file "tbl_name.MYI" (Errcode: ###) cannot be found :###).

Error ### obtained from the table processor (error 135 is an exception ).

Unexpected File termination.

The record file is destroyed.

In these cases, you must repair the table. Myisamchk can usually detect and fix most of the errors.

The repair process consists of up to four phases, which are described below. Before you start, you should cd the permissions to the database directory and checklist files to ensure that they can be read by Unix users running mysqld (and you, because you need to access the file you are checking ). If it rejects file modification, they must also be writable by you.

Phase 1: Check your table

Run

Myisamchk *. MYI

Or (myisamchk-e *. MYI, if you have more time ). Use the-s (SILENCE) option to disable unnecessary information.

You must fix only tables whose myisamchk reports have an error. For such a table, continue to Stage 2.

If you get a strange error (for example, out of memory error) during the check, or if myisamchk crashes, to stage 3.