MySQL常由于某种原因，出现表Crash，需要repair。MySQL提供了几种repair表的方法，整理出来，方便参考。

   在repair表之前，可能多数人会选择先check一下，看看那些表出问题，需要进行repair，所以，先提下check的方法。

   在MySQL中，提供三种方法来check表，而且，允许用户在数据库运行过程中对表进行check.

 

   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 手册）

    

 

 

QUICK	The quickest option, and does not scan the rows to check for incorrect links. Often used when you do not suspect an error.
FAST	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.
CHANGED	Same as FAST, but also checks tables that have been changed since the last check.
MEDIUM	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.
EXTENDED	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... ]

      options参数如下表所示（摘自MySQL手册）

 

--auto-repair	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

     3）运行myisamchk命令行工具检查（注意：该工具必须运行在服务终止条件下）

     语法：myisamchk [options] dir:/tablename.MYI

     参数解释：（摘自MySQL手册）

 

--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)数据表的方法与check类似，也有三种相似的方法。

     1）执行REPAIR TABLE SQL语句

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

     参数：

     

QUICK	The quickest, as the data file is not modified.
EXTENDED	Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.
USE_FRM	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) |
+-------------------------+--------+----------+---------------------------------------------+

  

  如果索引文件丢失或者头文件破坏，需要使用USE_FRM参数

    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

     可以同时对多个表repair，然后将结果列出来。

     %mysqlcheck -r sports_results fixtures events -uuser -ppass
      sports_results.fixtures                           OK
     sports_results.events                             OK

 

     3）使用myisamchk 命令行工具进行修复

     语法：myisamchk [options[ [dir：/tablenames]

     参数说明：

     

--backup, -B	Makes a .BAK backup of the table before repairing it
--correct-checksum	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.
--character-sets-dir=...	The directory containing the character sets
--set-character-set=name	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.