MYSQL 表的错误修复

itghost

MYSQL 表的错误修复  
MySQL提供了几种repair表的方法，整理出来，方便参考。

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

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

1) 执行 check table tablename [opti**] SQL语句

mysql> CHECK TABLE fixtures;

+————————-+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————————-+——-+———-+———-+

| sports_results.fixtures | check | status | OK |

+————————-+——-+———-+———-+

1 row in set (0.01 sec)

opti**参数，可选值为：（下表摘自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 [opti**] dbname tablename [tablename2... ]

opti**参数如下表所示（摘自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 [opti**] 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...] [opti**]

参数：

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 definiti** 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 [opti**[ [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.

示例：% myisamchk -r fixtures

- recovering (with keycache) MyISAM-table ‘fixtures.MYI’

Data records: 0