Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to repair database tables in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The symptom of a corrupted table is usually that the query is accidentally interrupted and you can see errors such as these:

◆ "tbl_name.frm" is locked and cannot be changed.

◆ could not find the file "tbl_name.MYI" (Errcode: #).

◆ gets error # # from the table processor (in this case, error 135 is an exception).

Unexpected end of file for ◆.

The ◆ record file was destroyed.

In these cases, you must repair the watch. Repairing the watch is a very difficult task, and in many cases there is nothing one can do about it. However, there are some conventional ideas and processes that can be followed to increase the chances of correcting the table. Usually, the quickest fix can be used at first to see if a pocket failure can be achieved. If you find it unsuccessful, you can gradually upgrade to a more thorough but slower fix. If it is still difficult to repair, it should be restored from the backup. This part has been introduced in detail in the previous chapter.

Simple and safe repair

Perform the following steps to repair a table:

◆ first modifies the table with the-- recover,-r option and uses the-- quick,-q option to restore only based on the contents of the index file. This does not touch the data file to repair the index file. (- r means "recovery model")

Myisamchk-r-Q tbl_nameisamchk-r-Q tbl_name

◆ if the problem persists, ignore the-- quick option and allow the fix to modify the data file because there may be a problem. The following command removes incorrect and deleted records from the data file and rebuilds the index file:

Myisamchk-r tbl_nameisamchk-r tbl_name

◆ if the previous steps fail, use. The secure recovery model uses an old recovery method to handle a few (but slower) cases where the conventional recovery model does not work.

Myisamchk-safe-recover tbl_nameisamchk-safe-recover tbl_name

Difficult repairs

If the first 16K block of the index file is corrupted, or contains incorrect information, or if the index file is missing, you should only get to this stage. In this case, it is necessary to create a new index file. Follow these steps:

◆ navigates to the database directory that contains the crash table

◆ moves the data files to a safer place.

◆ uses table description files to create new (empty) data and index files:

Shell > db_namemysql > DELETE FROM tbl_name;mysql > quit

The above statement recreates the new empty table and regenerates the new data and index files using the table's description file tbl_name.frm.

◆ copies the old data file into the newly created data file. Don't just move the old file back to the new one; keep a copy in case something goes wrong. )

◆ is using standard fixes. Myisamchk-r-Q should be working now. This should not be an infinite loop.

If you have a backup file of the table, the process is much easier. You can restore the description file of the table from the backup file, then check the table, and it is possible to continue to use standard repair methods, which should solve the problem.

A very difficult repair.

You should reach this stage only if the description file is also destroyed. This should never happen, because after the table is created, the description file no longer changes.

Restore the description file from a backup and return to phase 2. You can also restore the index file and return to stage 1. For the latter, you should start with myisamchk-r.

If, for some reason, the backup file of the data is missing or there is no backup file, but you still remember the CREATE TABLE statement to create the table, then great, you can still restore the index file:

◆ navigates to the database directory that contains the crash table

◆ moves the data files to a safer place. Then delete the corresponding directory in the database directory.

◆ calls mysql and sends a complex CREATE TABLE statement to establish the table.

◆ exits mysql, moves the original data file and index file back to the database directory, and replaces the file you just created.

◆ then goes back to stage 2 and repairs the table. You can also just move back to the data file to keep the new description and index file, then go back to stage 1 and continue to repair the table in a standard way.

After reading the above, do you have any further understanding of how to repair database tables in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.