1) 执行 check table tablename [options] SQL语句
   mysql> CHECK TABLE fixtures;
   | Table                  | Op   | Msg_type | Msg_text |
   | sports_results.fixtures | check | status  | OK      |
   1 row in set (0.01 sec)
   options参数,可选值为:(下表摘自MySQL 手册)




The quickest option, and does not scan the rows to check for incorrect links.
Often used when you do not suspect an error.
Only checks tables if they have not been closed properly.
Often used when you do not suspect an error, from a cron, or after a
power failure that seems to have had no ill-effects.
Same as FAST, but also checks tables that have been changed since the last check.
The default if no option is supplied. Scans rows to check that deleted links are correct,
and verifies a calculated checksum for all keys with a calculated a key checksum for the rows.
The slowest option, only used if the other checks report no errors but you still suspect corruption.
Very slow, as it does a full key lookup for all keys for every row. Increasing the key-buffer-size
variable in the MySQL config. file can help this go quicker.


值得注意的是,check table 只针对MyISAM和InnoDB,如果检查发现corruption,那么就返回corrupt,而退出执行。

     2) 运行mysqlcheck命令行工具(该工具可以在服务运行状态下执行)
     语法为:mysqlcheck [options] dbname tablename [tablename2... ]



Used together with a check option, it will automatically begin repairing if corruption is found.
--check, -c
Checks tables (only needed if using mysqlcheck under another name, such as mysqlrepair.
See the manual for more details)
--check-only-changed, -C
Same as the CHECK TABLE ... CHANGED option above.
--extended, -e
Same as the CHECK TABLE ... EXTENDED option above.
--fast, -F
Same as the CHECK TABLE ... FAST option above.
--medium-check, -m
Same as the CHECK TABLE ... MEDIUM option above.
--quick, -q
Same as the CHECK TABLE ... QUICK option above.



     % mysqlcheck -uuser -ppass sports_results fixtures
     sports_results.fixtures                           OK
     语法:myisamchk [options] dir:/tablename.MYI


--check, -c

The default option

--check-only-changed, -C

Same as the CHECK TABLE ... CHANGED option above.

--extend-check, -e

Same as the CHECK TABLE ... EXTENDED option above.

--fast, -F

Same as the CHECK TABLE ... FAST option above.

--force, -f

Will run the myisamchk repair option if any errors are found

--information, -i

Display statistics about the checked table

--medium-check, -m

Same as the CHECK TABLE ... MEDIUM option above.

--read-only, -T

Does not mark the table as checked

--update-state, -U

This option stores when the table was checked, and the time of crash, in .MYI file.



     % myisamchk fixtures.MYI
     Checking MyISAM file: fixtures.MYI
     Data records:   1415  Deleted blocks:      2
     - check file-size
     - check key delete-chain
     - check record delete-chain
     - check index reference
     - check data record references index: 1

     - check record links



      语法:REPAIR TABLE tablename[,tablename1...] [options]


The quickest, as the data file is not modified.


Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.


To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes

     mysql> REPAIR TABLE fixtures;
| Table                  | Op    | Msg_type | Msg_text                                   |
| sports_results.fixtures | repair | error   | Can't find file: 'fixtures.MYI' (errno: 2) |



    mysql> REPAIR TABLE fixtures USE_FRM;



| Table                  | Op    | Msg_type | Msg_text                          |
| sports_results.fixtures | repair | warning | Number of rows changed from 0 to 2 |
| sports_results.fixtures | repair | status  | OK                                |

     2) 使用mysqlcheck命令行工具修复数据表
     语法:%mysqlcheck -r sports_results fixtures -uuser -ppass
             sports_results.fixtures                           OK

     %mysqlcheck -r sports_results fixtures events -uuser -ppass
      sports_results.fixtures                           OK                             OK
     3)使用myisamchk 命令行工具进行修复
     语法:myisamchk [options[ [dir:/tablenames]
--backup, -B
Makes a .BAK backup of the table before repairing it
Corrects the checksum
--data-file-length=#, -D #
Specifies the maximum length of the data file, when recreating
--extend-check, -e
Attempts to recover every possible row from the data file. This option should not be used except
as a last resort, as it may produce garbage rows.
--force, -f
Overwrites old temporary .TMD files instead of aborting if it encounters a pre-existing one.
keys-used=#, -k #
Can make the process faster by specifying which keys to use. Each binary bit stands for one key
starting at 0 for the first key.
--recover, -r
The most commonly used option, which repairs most corruption. If you have enough memory, increase
the sort_buffer_size to make the recover go more quickly. Will not recover from the rare form of corruption
where a unique key is not unique.
--safe-recover, -o
More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows
and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since
a sort buffer is not created. You should increase the key_buffer_size value to improve repair speed if there
is available memory.
--sort-recover, -n
MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.
The directory containing the character sets
Specifies a new character set for the index
--tmpdir=path, -t
Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR
environment variable
--quick, -q
The fastest repair, since the data file is not modified. A second -q will modify the data file if there
are duplicate keys. Also uses much less disk space since the data file is not modified.
--unpack, -u
Unpacks a file that has been packed with the myisampack utility.


     示例:% myisamchk -r fixtures
             - recovering (with keycache) MyISAM-table 'fixtures.MYI'
             Data records: 0

mysql 修复表
SQLSTATE[HY000]: General error: 126 Incorrect key file for table  *
REPAIR TABLE  table_name;

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Can't connect to the server

myisamchk -c -r /var/lib/mysql/test/posts.MYI
myisamchk -c -r -f /var/lib/mysql/test/posts.MYI

要是还依旧报错 Can't connect to the server   
则在 my.cnf 里面的 [mysqld] 段增加一个启动参数
重启 登录 mysql
REPAIR TABLE  table_name;



您的电子邮箱地址不会被公开。 必填项已用 * 标注