Windows repair MySQL table and database

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.