How to fix MySQL database tables

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. Table repair is a very difficult task and is often helpless. However, there are some conventional ideas and processes that can follow to increase the chance of correcting tables. Usually, you can start with the fastest way to fix the fault. If it fails, you can gradually upgrade it to a more thorough but slow solution. If it is still difficult to repair, it should be recovered from the backup. This section is detailed in the previous chapter.
Simple and secure repair
To repair a table, perform the following steps:
First, use the -- recover and-r options to modify the table and use the -- quick and-q options to restore the table based on the content of the index file. In this way, you do not need to contact the data file to repair the index file. (-R means "recovery mode ")
Myisamchk-r-q tbl_nameisamchk-r-q tbl_name
If the problem persists, ignore the -- quick option and allow the repair program to modify the data file, because the problem may exist. The following command deletes incorrect records and deleted records from the data file and re-creates the index file:
Myisamchk-r tbl_nameisamchk-r tbl_name
If the previous step fails, use. The security recovery mode uses an old recovery method to handle a few cases (but slower) where the conventional recovery mode does not work ).
Myisamchk -- safe-recover tbl_nameisamchk -- safe-recover tbl_name
Difficult repairs
If the first 16 K block in the index file is damaged, or contains incorrect information, or if the index file is lost, you should only go to this stage. In this case, it is necessary to create a new index file. Follow these steps:
Go to the database directory that contains the crashed table
Move data files to a safer place.
Use the table description file to create new (null) data and index files:
Shell> mysql db_namemysql> delete from tbl_name; mysql> quit
The preceding statement re-creates a new empty table and uses the table description file tbl_name.frm to re-generate a new data and index file.
Copy the old data file to the newly created data file. (Do not just move the old file back to the new file; keep a copy to prevent some errors .)
Use standard fixes. Now myisamchk-r-q should work. (This should not be an infinite loop ).
If you have a backup file for a table, all the procedures are much easier. The description file of the table can be restored from the backup file. In the checklist, it is possible to continue to use the standard repair method. The problem should be rectified.
Very difficult repairs
Only when the description file is damaged should you reach this stage. This should never happen, because after the table is created, the description file will not change.
Recover the description file from a backup and return to phase 2. You can also restore the index file and return to phase 1. For the latter, you should start with myisamchk-r.
If, for some reason, the data backup file is lost or there is no backup file, but you still remember the create table statement for creating the TABLE, it would be great to restore the index file:
Go to the database directory that contains the crashed table
Move data files to a safer place. Delete the corresponding directory in the database directory ..
Call the mysql statement to CREATE a TABLE. (Www.bkjia.com)
Log out of mysql, move the original data file and index file back to the database directory, and replace the new file.
Then return to Stage 2 to repair the table. You can also only move the data file back, so that the new description and index files are retained, and then return to stage 1 to continue to use the standard method to repair the table.