Windows repair MySQL table and database

Source: Internet
Author: User
Tags flush mysql client phpmyadmin

Let's analyze the problem first.

A typical symptom of a damaged table is as follows:
1. When selecting data from a table, you get the following error: Incorrect key file for table: ' ... '. Try to repair it
2. The query cannot find rows in the table or return incomplete data.
3. Error:table ' P ' is marked as crashed and should to be repaired.
4, open the table failed: Can ' t open file: ' xxx. Myi ' (errno:145).

If you are a MyISAM table you can often use the following methods to fix

Repair steps:

1 Stop the MySQL service before fixing it.
2 Open the command line, and then go to the MySQL/bin directory.
3 Execute MYISAMCHK--recover database path/*.   Myi. Note: Do not follow the prompts behind;


Syntax: MYSQLCHECK-R database name Table name-uuser-ppass

The code is as follows Copy Code

%mysqlcheck-r Sports_results Mytable-uuser-ppass

Sports_results.mytable OK utilizes mysqlcheck to fix multiple tables at once. Just list the name of the table after the database name (separated by a space). or without a table name after the database name, all tables in the database will be repaired, for example:

The code is as follows Copy Code

%mysqlcheck-r sports_results mytable Events-uuser-ppass
Sports_results.mytable OK
Sports_results.events OK

%mysqlcheck-r Sports_results-uuser-ppass
Sports_results.mytable OK
Sports_results.events OK


The data table must be locked in read/write mode for the repair operation, as follows:

The code is as follows Copy Code

% MySQL

mysql> Use DB

mysql> LOCK TABLE table_name WRITE; Lock data table #以读/write mode

mysql> FLUSH TABLE table_name;

To maintain the MySQL client connection status, switch to the Second Shell window and run the repair command:

The code is as follows Copy Code
% MYISAMCHK--recover table_name


It is best to back up the data file before running the repair command.

When the repair is complete, switch back to the MySQL Client Connection window and run the following command to unlock the datasheet:

The code is as follows Copy Code

mysql> FLUSH TABLE table_name; #使服务器觉察新产生的索引文件

Mysql> UNLOCK TABLE;

You can also use the command to lock all tables, and after locking, all users can only read and write data, which allows us to securely copy data files.

The code is as follows Copy Code

Mysql> FLUSH TABLES with READ LOCK;

The following is a unlock statement:

The code is as follows Copy Code

Mysql> UNLOCK TABLES;


Small Tips

Generally we will have phpmyadmin this function, so if the table is broken, you can directly use the phpMyAdmin function for table repair, the method is the same.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.