Repair table syntax-MySQL database

Tip:

Generally, you do not have to run this statement. However, if a disaster occurs, the repair table may retrieve all data from the MyISAM table.

If your table is often damaged, you should try your best to find the cause to avoid using repair talbe. Check "What should I do if MySQL Crashes" and "MyISAM Table problems ".

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE

   tbl_name[,tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

Repair table is used to repair damaged tables. By default, repair table andMyisamchk -- recoverTbl_nameWith the same effect. Repair table applies to MyISAM and archive tables. See section 15.1, "MyISAM storage engine", section 15.8, "ARCHIVE storage engine ".

Generally, you do not have to run this statement. However, if a disaster occurs, the repair table may retrieve all data from the MyISAM table. If your table is often damaged, you should try your best to find the cause to avoid using repair talbe. See section A.4.2, "What should I do if MySQL still crashes ". For more information, see section 15.1.4 "MyISAM Table problems ".

This statement returns a table containing the following columns:

Column	Value
Table	Table Name
OP	Repair
Msg_type	Status, error, message, or warning
Msg_text	Message

For each repaired table, the repair TABLE statement generates multiple rows of information. The previous row contains a msg_type status value. Msg_test should generally be OK. If you do not get OK, you should try to useMyisamchk -- safe-recoverFix the table because the repair table does not execute allMyisamchk. We plan to make it more flexible in the future.

If quick is given, repair table tries to repair only the index tree. This type of repair and useMyisamchk -- recover -- quickSimilar.

If you use extended, MySQL creates an index row in one row instead of a category. This type of repair and useMyisamchk -- safe-recoverSimilar.

For repair table, another USE_FRM mode can be used. This mode is used if the. MYI index file is missing or the title is damaged. In this mode, MySQL can use the. frm file to recreate the. MYI file. This fix cannot be used.Myisamchk. Note: This mode can only be used when you cannot use the regular REPAIR mode .. The MYI title contains important table metadata (especially the current AUTO_INCREMENT value and Delete link ). These metadata is lost in REPAIR... USE_FRM. If the table is compressed, USE_FRM cannot be used. This information is also stored in the. MYI file.

The repair table statement is written to the binary log unless the NO_WRITE_TO_BINLOG keyword (or its alias is LOCAL) is used ).

Warning if the server is down while the repair table is running, you must execute another repair table statement immediately after the restart and before other operations are performed. (It is a good way to start a backup .) In the worst case, you can have a new clean index file that does not contain information about the data file. The next operation you perform will overwrite the data file. This rarely happens, but it is possible.

 

 

MySQL repair table-can't open file: '[Table] mytable. myi '.
　　

 

Maybe many people have encountered an error message like Can't open file: '[Table] mytable. myi', but do not know how to solve it. Let's introduce it as follows:

In most cases, the database is damaged only when the index file is damaged, and the real data is damaged rarely. Most forms of database corruption can be easily repaired.
As with the previous verification, there are three methods to fix the vulnerability.

The method described below is only valid for tables in MyISAM format. Other types of damages must be recovered from the backup.

1. repair table SQL statement (mysql service must be running ).
2. Command mysqlcheck (mysql service can be running ).
3. Command myisamchk (MySQL service must be stopped or the operated table is inactive ).

 

When repairing a table, you 'd better back up the table first. So you need two times the hard disk space of the original table. Make sure that your disk space is not used up before the restoration.

 

 

1> use the "repair table" method to fix

　

Syntax: repair table name [Option]
　　　

The options are as follows:
Quick is the fastest speed when the data table has not been modified
Extended attempts to restore each data row will produce some junk data rows.
Use_frm is used when the. myi file is lost or the header is damaged. Re-indexing using the definition of. FRM

In most cases, you can simply use "repair table tablename" without the option. However, this method does not work when the. myi file is lost or the header is damaged. For example:

 

For example:

Mysql> repair table mytable;
+ --------- + --- + ---- + ----------------------------- +
| Table | op | msg_type | msg_text |
+ --------- + --- + ---- + ----------------------------- +
| Sports_results.mytable | repair | error | can't find file: 'mytable. myi' (errno: 2) |
+ --------- + --- + ---- + ----------------------------- +
　　

When the repair fails, the index file is lost or its header is damaged. To use the relevant definition file for repair, you need to use the use_frm option. For example:

Mysql> repair table mytable use_frm;
+ --------- + --- + ---- + ---------------------------- +
| Table | op | msg_type | msg_text |
+ --------- + --- + ---- + ---------------------------- +
| Sports_results.mytable | repair | warning | number of rows changed from 0 to 2 |
| Sports_results.mytable | repair | status | OK |
+ --------- + --- + ---- + ---------------------------- +
We can see that the output information of the Msg_test table item is "OK", and the table name has successfully repaired the damaged table.

 

 

 

2>Use MySQL built-in command mysqlcheck to fix

 

When the mysql service is running, you can also use the mysql built-in command mysqlcheck to fix it.
　　

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

Command: % mysqlcheck-r sports_results mytable-uuser-ppass

Result: sports_results.mytable OK
　　

You can use 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:

Command: % mysqlcheck-r sports_results mytable events-uuser-ppass

Result: sports_results.mytable OK
Result: sports_results.events OK
 
Command: % mysqlcheck-r sports_results-uuser-ppass
　　　　

Result: sports_results.mytable OK
Result: sports_results.events OK

 

 

3> Use myisamchk to fix

 

In this way, the MySQL service must be stopped, or the operated table is inactive (the option skip-external-locking is not used ). Remember to define the path under the relevant. myi file or by yourself.
　　

Syntax: myisamchk [Option] [Table name]
　　

The options and descriptions are as follows:
-Backup,-B backs up related tables before repairing

-Correct-checksum

-Data-file-length = #,-D # specify the maximum length of the data file when recreating the table.

-Extend-check,-E tries to restore each data row, which will generate some junk data rows.

-Force and-F overwrite a. TMD file with the same file name.

Keys-used = #,-K # specify the keys used to speed up processing. Each binary represents a key. The first key is 0.

-Recover, the most common option of-R, which can be used to fix most damages.

If your memory is large enough, you can increase the value of sort_buffer_size to speed up recovery. However, this method does not work when the table is not unique due to the destruction of the unique key.
　　　　

-Safe-recover-o is the most thorough repair method, but it is slower than the-r method. It is generally used only after the-r repair fails.

In this way, all rows are read and the index is rebuilt based on the behavior. Its hard disk space requirement is slightly smaller than the-r method, because no category cache is created for it. You can increase the value of key_buffer_size to speed up the repair.

-Sort-recover,-n mysql uses its Class Classification Index, although the result is temporary files will be very large

-Character-sets-dir =... Directory containing Character Set settings

-Set-character-set = name defines a new character set for the index.

-Tmpdir = path,-t. If you do not want to use the value of the environmental variable TMPDIR, you can customize the storage location of temporary files.

-Quick,-q: the fastest way to fix the problem. It is used when the data file is not modified. If there are multiple keys, the second-q will modify the data file.

-Unpack,-u unpack the files packaged by myisampack

 

 

An example of myisamchk Application

% Myisamchk-r mytable
-Recovering (with keycache) MyISAM-table 'mytable. myi'
Data records: 0