Repair mysql tables and databases in Windows

In windows, mysql users may encounter tables that suddenly break down and may encounter tablepismarkedascrashedandshouldberesponred. The following describes how to fix mysql Databases and tables.

In windows, mysql users may encounter tables that suddenly break down, such as Table p is marked as crashed and shoshould be retried red, the following describes how to fix mysql Databases and tables.

Let's analyze the problem first.

Typical symptoms of a damaged table are 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 shoshould be retried red.
4. failed to open the table: Can't open file: '×××. myi' (errno: 145 ).

If you are a myisam table, you can use the following methods to fix it.

Repair procedure:

1) Stop the mysql service before the fix.
2) Open the command line and enter the/bin directory of mysql.
3) execute myisamchk -- the path of the recover database/*. MYI. Note: Do not follow the prompt; No.

Syntax: mysqlcheck-r database name table name-uuser-ppass

The Code is as follows:
% Mysqlcheck-r sports_results mytable-uuser-ppass

Sports_results.mytable OK uses mysqlcheck to repair multiple tables at a time. You only need to list the corresponding table names after the database names (separated by spaces ). Or if the table name is not added after the database name, all tables in the database will be repaired. For example:

The Code is as follows:
% 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

To fix the problem, you must lock the data table in read/write mode. The command is as follows:

The Code is as follows:
% Mysql Mysql> use db Mysql> lock table table_name WRITE; # LOCK data tables in read/WRITE mode Mysql> flush table table_name;

Maintain the mysql client connection status, switch to the second shell window, and run the repair command:

The Code is as follows:
% Myisamchk -- recover table_name

You are advised to back up the data file before running the repair command.

After the repair is complete, switch back to the mysql client connection window and run the following command to unlock the data table:

The Code is as follows:
Mysql> flush table table_name; # enable the server to detect the newly generated index file Mysql> unlock table;

You can also use the following command to lock all tables. After locking, all users can only read and write data, so that we can securely copy data files.

The Code is as follows:
Mysql> flush tables with read lock;

The following is the unlock statement:

The Code is as follows:
Mysql> unlock tables;

Tips

We usually have the phpmyadmin function, so if the table is broken, you can directly use the phpmyadmin function to repair the table, the method is the same.