Can ' t Open file: ' [table]mytable. Myi ' _mysql

Source: Internet
Author: User
Tags character set unpack
In most cases, the destruction of the database simply means that the index file has been corrupted, and that the real data has been destroyed very little. The repair of most forms of database destruction is fairly straightforward.
As with the previous checksum, there are three different ways to fix it.
The following methods are valid only for tables in MyISAM format. Other types of corruption need to be recovered from the backup.
1,repair TABLE SQL statement (MySQL service must be running).
2, the command Mysqlcheck (the MySQL service can be in run state).
3, Command Myisamchk (the MySQL service must be stopped or the table being manipulated is inactive).
When repairing a table, it's a good idea to make a backup first. So you need two times the size of the original table hard disk space. Make sure your hard disk space is not run out before you fix it.
1> with "Repair table" way to fix
Syntax: Repair table name [options]
The options are as follows:
Quick is the fastest when the datasheet has not been modified
EXTENDED trying to recover each row of data, will produce some garbage data rows, helpless
Use_frm used in. myi files are missing or damaged in the head. Use the definition of. frm to rebuild the index
In most cases, it's easy to use the "repair table tablename" without the option to fix the problem. But when. This does not work when the Myi file is lost or if the head is damaged, 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 reason for the failure is missing the index file or its head has been damaged, in order to use the relevant definition file to repair, 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 the output information "OK" for the Msg_test table entry, and the table name has successfully repaired the damaged table.
2> with MySQL built-in command mysqlcheck to fix
When the MySQL service is running, it can also be repaired with the MySQL built-in command mysqlcheck.
Syntax: MYSQLCHECK-R database name Table name-uuser-ppass
%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:
%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 ok3> with Myisamchk repair
In this way, the MySQL service must be stopped or the table being manipulated is inactive (option skip-external-locking is not used). Remember to be relevant. Myi the path to the file or define its own path.
Syntax: myisamchk [options] [table name]
Here are the options and descriptions
–backup,-B to make a backup of the related table before the repair
–correct-checksum Correction Checksum
–data-file-length=#, D # When rebuilding a table, specify the maximum length of the data file
–extend-check, E attempts to recover each row of data, produces some garbage data rows, helpless
–force,-F when encountering the same file name. When TMD files, overwrite them.
keys-used=#, K # Specifies the keys used to speed up processing, with each bits representing a key. The first key is 0
–recover, r the most common option, most of the damage can be repaired by it. If your memory is large enough, you can increase the value of the parameter sort_buffer_size to speed up the recovery. However, this approach does not work when you encounter a table with unique keys that are not unique because they are corrupted.
–safe-recover-o is the most thorough method of repair, but is slower than-R, and is typically used after the-R repair fails. This way all rows are read out and the index is rebuilt on a behavioral basis. Its hard disk space needs are slightly smaller than the-R method because it does not create a category cache. You can increase the Key_buffer_size value to speed up the repair.
–sort-recover,-n MySQL uses its class to classify indexes, although the result is that temporary files can be very large
–character-sets-dir= ... directory containing set of character set
–set-character-set=name defines a new character set for the index
–tmpdir=path,-t if you do not want to use the environment variable Tmpdir value, you can customize the location of temporary files
–quick,-Q the quickest way to fix it, when the data file is not modified, and when multiple keys exist, the second-Q modifies the data file
–unpack,-U unpack the files that are myisampack packaged
An example of MYISAMCHK application
% Myisamchk-r mytable
-Recovering (with Keycache) myisam-table ' mytable. Myi '
Data records:0

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.