How to fix the mysql database

Friends who know mysql know that some problems will occur in the database after a long period of use, which requires quick repair and damage to the mysql database to facilitate our work and study. The following section describes two methods for quick MySQL database overhaul.

I often use this code and directly put it in the mysql database directory.

Copy codeThe Code is as follows:
Cmd/k myisamchk-r jb51_tablename

Jb51_tablename is the name of jb51_tablename.MYD. Run the following command.

Sometimes the database is damaged due to power loss or other reasons. We can use the mysql command to quickly fix all databases or specific databases. For example
Check to optimize and fix all databases:

1. Enter CMD in the running process to start the command line.

2. Go to the Bin directory of Mysql: E: \ Program Files \ MySQL Server 5.0 \ bin. If you do not know how to enter another directory, you should refer to the online materials for basic knowledge.
Common Methods:
Run E:
Run CD "E: \ Program Files \ MySQL Server 5.0 \ bin"

3. Run: mysqlcheck-A-o-r-uroot-p888888
Note: Change 888888 to your own root user password.

Mysql. columns_priv OK
Mysql. db OK
Mysql. func OK
Mysql. help_category OK
Mysql. help_keyword OK
Mysql. help_relation OK
Mysql. help_topic OK
Mysql. host OK
Mysql. tables_priv OK
Mysql. time_zone OK
Mysql. time_zone_leap_second OK
Mysql. time_zone_name OK
Mysql. time_zone_transition OK
Mysql. time_zone_transition_type OK
Mysql. user OK
......
......
......

Note!

If an error prompt is displayed during the repair process, it indicates that the table is broken and cannot be repaired. For databases with bad tables, you can only delete or stop it, otherwise, it will affect the stability of Mysql and cause mysql to stop automatically. (The table "the storage engine for The table doesn't support repair" does not need to be processed)

You can run
Mysqlcheck-A-o-r-uroot-p888888> C: \ test.txt

After running test.txt, you can see it.
　　
1. myisamchk

To use myisamchk, you must temporarily stop the MySQL server. For example, we want to repair the discuz database. Perform the following operations:

Copy codeThe Code is as follows:
# Service mysql stop (stop MySQL );
# Myisamchk-r/absolute path of the database file/* MYI
# Service mysql start


Myisamchk automatically checks and fixes index errors in the data table.

2. mysqlcheck

You can use mysqlcheck to perform hotfix without stopping MySQL. The procedure is as follows:
　　
> # Mysqlcheck-r discuz .*

Note: Do not use-f to forcibly fix myisamchk or mysqlcheck. If the-f parameter fails to be repaired, delete some data with errors to try to fix it. Therefore, do not use-f unless necessary.

The following is a supplement:

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

◆ "Tbl_name.frm" cannot be changed when it is locked.

◆ 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_name
Isamchk-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_name
Isamchk-r tbl_name

◆ If the previous step fails, use it. 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_name
Isamchk -- 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:

◆ Locate the database directory containing the crashed table

◆ Move data files to a safer place.

◆ Use the table description file to create new (empty) 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 repair methods. 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:

◆ Locate the database directory containing the crashed table

◆ Move data files to a safer place. Delete the corresponding directory in the database directory ..

◆ Call the mysql concurrent re-create table statement to CREATE the TABLE.

◆ Exit mysql, move the original data file and index file back to the database directory, and replace the new file.

◆ Return to Stage 2 and 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.