"tbl_name.frm" table definition (table) file "tbl_name. MYD " Data File "tbl_name. MYI" index file
Each of these 3 file types can suffer from different forms of corruption, but the problem most often occurs in data files and index files.
myisamchk creates a ". MYD" (data) file to work, it works by removing 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 assuming". MYD" file is correct and only creates a new index file, without touching". MYD" file, which is safe because myisamchk automatically detects". MYD" file is corrupted and in this case, abandon repair. You can also give myisamchk two --quick options. In this case, myisamchk does not give up on some errors (like repeating keys), but instead tries to modify the ". MYD" file to resolve them. Usually, it is only useful if you implement a normal fix on too little free disk space, using the two --quick options. In this case, you should at least make a backup before running myisamchk.
1. How to check the errors in the table
To check a table, use the following command:
myisamchk tbl_name
This catches 99.99% of all errors. What it can't find out is that corruption is only involved in data files (which is uncommon). If you want to check a table, you should usually run myisamchk without the option or use either of the -s or --silent options.
myisamchk -e tbl_name
It does a completely thorough data check (-e means "extended check"). It does a per-key read check on each line to confirm that they do point to the correct line. This can take a long time on a large table with many keys. myisamchk will usually stop after it finds the first error. If you want more information, you can add the --verbose(-v) option. This makes myisamchk continue until a maximum of 20 errors. In general use, a simple myisamchk (with no arguments other than the table name) is sufficient.
myisamchk -e -i tbl_name
Like the previous command, but the -i option tells myisamchk to print out some statistics as well.
2. How to repair the table
The symptom of a corrupted table is usually an unexpected query break, and you can see, for example, these errors:
"tbl_name.frm" is locked and cannot be changed.
Unable to find the file "tbl_name. MYI”（Errcode ：### ）。
Get error ### from the table processor (in this case, error 135 is an exception).
Unexpected file end.
Record files were destroyed.
In these cases, you must fix the table. myisamchk usually detects and fixes most things that go wrong.
The repair process consists of up to 4 stages, described below. Before you start, you should cd permissions to the database directory and checklist files to make sure they are readable by Unix users running mysqld (and you, as you need access to the file you are checking). If it refuses to modify the file, they must also be writable to 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.
The file format used by MySQL to store data has been extensively tested, but there are always external conditions that can cause database tables to be corrupted:
mysqld process is killed in one write; An unexpected shutdown of the computer (for example, if the computer loses power); A hardware error.
This chapter describes how to check for and handle data corruption in a MySQL database. If your table is damaged a lot, you should try to find out why! See G.1 Debugging a MySQL server.
When performing crash recovery, it is important to understand that each table in a database tbl_name corresponds to the 3 files in the database directory |
Posted on 11/15/2017 1:23:05 PM
|
|
|
