Maybe many people have encountered an error message like Can 'topenfile: '[Table] mytable. myi', but they don't know how to solve it. Let's introduce it as follows,
Many people may 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:
Mysql> repair table mytable;
+ --------- + --- + ---- + --------------- +
| Table | Op | Msg_type | Msg_text |
+ --------- + --- + ---- + --------------- +
| Sports_results.mytable | repair | error | Can't find file: 'mytable. myi' (errno: 2) |
+ --------- + --- + ---- + --------------- + The index file is lost or its header is damaged when the cause of the failure is fixed. in order to fix the problem by using the definition file, 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 the mysql built-in command mysqlcheck to fix the problem.
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
% 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:
% 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> fixed with myisamchk
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
