Repair mysql table

The repair data table (mysql REPAIR table-Can ‘t open file:’ [Table] mytable. MYI ‘.)

Perhaps many people encountered similar CAN ‘t open file:’ [Table] mytable. MYI ‘this error message, but do not know how to fix him, let’s be introduced,
In most cases, the database is destroyed only refers to the index file is destroyed, very little real data is destroyed. Most forms of database damage repair is quite simple.
And the front of the check, repair, there are three.
Speak below is valid only for MyISAM table format. The other type of damage needs to be restored from a backup.
1, REPAIR TABLE SQL statement (mysql service must be running).
2 command mysqlcheck (mysql service can be running).
3, the command myisamchk (mysql service must be stopped, or the operating table is not active).
Repair the table, it is best to make a few backups. So you need twice the size of hard disk space of the original table. Please ensure that your hard disk space during repair has not run out.
> Repair table “way to fix syntax: Repair table table name [option]
The options are as follows:
QUICK used in the case of the data table has not been modified, the fastest
EXTENDED trying to recover each data line will produce some garbage data line, Left
USE_FRM. MYI file is missing or head damage. Use frm the definition to rebuild the index most cases, a simple starting a “repair table tablename” no option can handle the problem. But when the the. MYI file is missing or head damage, in such a way regardless of use, 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) |
+ ——— + — + —- + ————— +
Fix the cause of the failure index file is missing or was destroyed on its head, to take advantage of the relevant definition file to repair need 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 of the Msg_test entry information “ok”, the name of the table has been successfully repair damaged table.
2> mysql built-in command mysqlcheck to repair when mysql service is running, you can also use mysql built-in command mysqlcheck to repair.
Syntax: mysqlcheck-r database watches name-Uuser-ppass
% Mysqlcheck-r sports_results mytable-uuser-ppass
sports_results.mytable OK
Use mysqlcheck can repair multiple tables at once. As long as the corresponding table name can be listed in the database name (separated by a space). Or database name without the table name, it will fix all the tables in the database, such as:
% 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
3> myisamchk to repair this way, mysql service must be stopped, or the operating table is inactive (option skip-external-locking is not used). Remember must be related the MYI file path or define its own path.
Syntax: myisamchk [options] [table name]
The following are the options and their descriptions
-Backup-B during the repair of related tables before backup
-Correct-checksum correct checksum
-Data-file-length = #-D # rebuild the table, the maximum length of the specified data file
-Extend-check,-e try to restore each data row, will produce some garbage data line, Left
-Force-f when faced with the same file name. TMD file to overwrite.
keys-used = #-k # specify the keys can speed up the process, each binary bit represents a key first key 0
-Recover,-r is the most commonly used options, most of the destruction can use it to repair. If you have a large enough memory, you can increase the value of sort_buffer_size to accelerate the speed of recovery. But encountered a unique key destruction and not only table, this approach does not work.
-Safe-recover-o most thorough repair, but slower than-r-r repair failure after use. In this way to read all lines, and behavior-based to rebuild the index. Its hard disk space needs a little smaller than-r, because it did not create a classified cache. You can increase the value of key_buffer_size to accelerate the speed of the repair.
-Sort-recover,-n mysql class classification index, although the results are temporary files will be very large
-Character-sets-dir = … directory contains the character set
-Set-character-set = name index to define a new character set
-Tmpdir = path, – t If you do not want the value of the environment variable TMPDIR, you can customize the temporary file storage location
-Quick,-q the fastest repair mode, when the data file has not been modified with the data file, will be modified when there are multiple keys, and the second-q
-Unpack,-u unlock the packed files myisampack
the myisamchk applications an example
% Myisamchk-r mytable
– Recovering (with keycache) MyISAM-table ‘mytable. MYI’
Data records: 0