mysql cant open file: Table name modification method

mysql tutorial cant open file: Table name modification method

1.can ' t Open file: ' [table]mytable.myi '
2.table ' picture ' are marked as crashed and should be repaired

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 is used in cases where the. myi file is missing or the head is damaged. 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 a. myi file is lost or the head is damaged, such a way is not used, 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 USE_FRM option is required to fix the index file when the reason for the failure is lost or if its head is damaged in order to use the relevant definition file. 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 tutorial Name Table name-uuser-ppass

%mysqlcheck-r Sports_results Mytable-uuser-ppass
Sports_results.mytable OK
Mysqlcheck can be used to repair 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 OK
3> 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 that you must define the path of the. myi file or your own.
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 overwrites the. tmd file with the same file name when it is encountered.
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