Teach you how to fix Mysql database _mysql

Will MySQL friends know that MySQL after a long time to use the database will be a number of problems, which requires rapid repair damage to the MySQL database to facilitate our work and learning. The following small series for you to introduce two quick methods to overhaul the MySQL database.

I use this kind of code, directly into the MySQL database directory

Copy Code code as follows:

cmd/k Myisamchk-r Jb51_tablename

Jb51_tablename is jb51_tablename. The name of the myd. Run the following is OK.

Sometimes we can use MySQL's own mysqlcheck command to quickly fix all the databases or specific databases because of the corruption of the database due to power off or other reasons.
Check to optimize and fix all databases using:

1. First enter CMD in the run, start the command line.

2. Access to MySQL Bin directory: E:\Program files\mysql\mysql Server 5.0\bin, if you do not know how to enter a different directory, you need to refer to the online Information tutorial basics.
Common ways:
Run E:
Run CD "E:\Program files\mysql\mysql Server 5.0\bin"

3. Operation: Mysqlcheck-a-o-r-uroot-p888888
Note that changing 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
......
......
......

Attention!

In the repair process, if you see a hint of error, indicating that the table is bad, can not be repaired, for the database containing bad tables, you can only delete it, or stop it, otherwise it will affect the stability of the entire MySQL, resulting in MySQL automatically stop. (Prompt "The storage engine for the table doesn ' t support repair" does not need to be processed)

If the fix is too fast to see the result, you can run
Mysqlcheck-a-o-r-uroot-p888888 >>c:\test.txt

After running, open the test.txt and you can see it.
　　
1, Myisamchk

The MySQL server must be temporarily stopped using MYISAMCHK. For example, we need to overhaul the Discuz database. Perform the following actions:

Copy Code code as follows:

# service MySQL stop (stop MySQL);
# myisamchk-r/Database file absolute path/*myi
# service MySQL Start

Myisamchk automatically checks for and repairs index errors in the datasheet.

2, Mysqlcheck

With Mysqlcheck you do not need to stop MySQL for hot fixes. Action steps are as follows:
　　
># Mysqlcheck-r discuz.*

Small Series Reminder: Whether it is myisamchk or mysqlcheck, do not normally use-F forced repair, the-f parameter will be encountered in the general repair can not succeed when the deletion of some of the error data to try to repair. Therefore, you should not use-f unless you have to.

The following are supplementary:

The symptom of a damaged table is usually an unexpected interruption of the query and you can see such errors as:

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

Unable to find file ' Tbl_name. Myi "(Errcode: ###).

Error ### from the table processor (at this point, error 135 is an exception).

Unexpected end of file.

The record file was destroyed.

In these cases, you have to fix the table. The repair of a table is a very difficult task, and in many cases it is helpless. However, there are some conventional knowledge of ideas and processes that can be followed to increase the chance of modifying the table. Usually, you can start with the fastest fix to see if it can be a pocket-sized failure. If the discovery is unsuccessful, you can incrementally upgrade to a more thorough but slower fix. If it is still difficult to repair, it should be recovered from the backup. This section has been covered in detail in the previous chapter.

Simple and safe fixes

To repair a table perform the following steps:

First, the table is fixed with the--RECOVER,-R option, and the--QUICK,-Q option is used to recover only from the contents of the index file. This does not touch the data file to fix 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 hotfix to modify the data file because there may be a problem. The following command deletes the incorrect records and deleted records from the data file and rebuilds the index files:

Myisamchk-r Tbl_name
Isamchk-r Tbl_name

If the previous step fails, use the. The security recovery model uses an old recovery method to handle a small number of cases (but more slowly) that do not work with the normal recovery model.

Myisamchk--safe-recover Tbl_name
Isamchk--safe-recover Tbl_name

Difficult to repair

If the first 16K block in the index file is corrupted, or contains incorrect information, or if the index file is lost, you should only be at this stage. In this case, it is necessary to create a new index file. Follow these steps as follows:

Navigate to the database directory containing the crash table

Move the data file to a more secure location.

To create a new (empty) data and index file by using a table description file:

shell> MySQL db_namemysql> DELETE from tbl_name;mysql> quit

The above statement will recreate the new empty table and use the table's description file tbl_name.frm to regenerate the new data and index files.

Copy old data files to the newly created data file. (Don't just move old files back into new files; you need to keep a copy in case something goes wrong.) ）

In the use of standard repair methods. Now Myisamchk-r-Q should be working. (This should not be an infinite loop).

If you have a backup file for a table, then everything is much easier. You can recover the table's description from the backup file, and then in the checklist, you may want to continue using the standard fix method, which should correct the problem.

Very difficult to fix

Only the description file is destroyed, you should reach this stage. This should never have happened because the description file does not change after the table is created.

Restore the description file from a backup and return to Stage 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 data's backup file is missing or there is no backup file, but you remember to create the table's CREATE TABLE statement, so good, so that you can restore the index file:

Navigate to the database directory containing the crash table

Move the data file to a more secure location. Then delete the corresponding directory in the database directory.

Invoke the MySQL concurrent CREATE TABLE statement to establish the table.

Exit MySQL, and move the original data files and index files back to the database directory, replacing the newly created file.

Then go back to Stage 2 and fix the table. You can also just move back to the data file, keep the new description and index file, and then go back to Phase 1 and continue with the standard method to fix the table.