第5章:数据库管理 / 5.9. 备份与恢复 / 5.9.5. myisamchk:MyISAM表维护实用工具
5.9.5.1. 用于myisamchk的一般选
5.9.5.2. 用于myisamchk的检查选项
5.9.5.3. myisamchk的修复选项
5.9.5.4. 用于myisamchk的其它选项
5.9.5.5. myisamchk内存使用
5.9.5.6. 将myisamchk用于崩溃恢
5.9.5.7. 如何检查MyISAM表的错误
5.9.5.8. 如何修复表
5.9.5.9. 表优化

可以使用myisamchk实用程序来获得有关数据库表的信息或检查、修复、优化他们。myisamchk适用MyISAM(对应.MYI.MYD文件的表)

调用myisamchk的方法:

shell> myisamchk [options] tbl_name ...

options指定你想让myisamchk做什么。在后面描述它们。还可以通过调用myisamchk --help得到选项列表。

tbl_name是你想要检查或修复的数据库表。如果你不在数据库目录的某处运行myisamchk,你必须指定数据库目录的路径,因为myisamchk不知道你的数据库位于哪儿。实际上,myisamchk不在乎你正在操作的文件是否位于一个数据库目录;你可以将对应于数据库表的文件拷贝到别处并且在那里执行恢复操作。

如果你愿意,可以用myisamchk命令行命名几个表。还可以通过命名索引文件( .MYI后缀)来指定一个表。它允许你通过使用模式*.MYI指定在一个目录所有的表。例如,如果你在数据库目录,可以这样在目录下检查所有的MyISAM表:

shell> myisamchk *.MYI

如果你不在数据库目录下,可通过指定到目录的路径检查所有在那里的表:

shell> myisamchk /path/to/database_dir/*.MYI

你甚至可以通过为MySQL数据目录的路径指定一个通配符来检查所有的数据库中的所有表:

shell> myisamchk /path/to/datadir/*/*.MYI

推荐的快速检查所有MyISAM表的方式是:

shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI

如果你想要检查所有MyISAM表并修复任何破坏的表,可以使用下面的命令:

shell> myisamchk --silent --force --fast --update-state \
          -O key_buffer=64M -O sort_buffer=64M \
          -O read_buffer=1M -O write_buffer=1M \
          /path/to/datadir/*/*.MYI

该命令假定你有大于64MB的自由内存。关于用myisamchk分配内存的详细信息,参见5.9.5.5节,“myisamchk内存使用”

当你运行myisamchk时,必须确保其它程序不使用表。否则,当你运行myisamchk时,会显示下面的错误消息:

warning: clients are using or haven't closed the table properly

这说明你正尝试检查正被另一个还没有关闭文件或已经终止而没有正确地关闭文件的程序(例如mysqld服务器)更新的表。

如果mysqld正在运行,你必须通过FLUSH TABLES强制清空仍然在内存中的任何表修改。当你运行myisamchk时,必须确保其它程序不使用表。避免该问题的最容易的方法是使用CHECK TABLE而不用myisamchk来检查表。

5.9.5.1. 用于myisamchk的一般选

本节描述的选项可以用于用myisamchk执行的任何类型的表维护操作。本节后面的章节中描述的选项只适合具体操作,例如检查或修复表。

·         --help-?

显示帮助消息并退出。

·         --debug=debug_options, -# debug_options

输出调试记录文件。debug_options字符串经常是'd:t:o,filename'

·         --silent-s

沉默模式。仅当发生错误时写输出。你能使用-s两次(-ss)使myisamchk沉默。

·         --verbose-v

冗长模式。打印更多的信息。这能与-d-e一起使用。为了更冗长,使用-v多次(-vv, -vvv)

·         --version, -V

显示版本信息并退出。

·         --wait, -w

如果表被锁定,不是提示错误终止,而是在继续前等待到表被解锁。请注意如果用--skip-external-locking选项运行mysqld,只能用另一个myisamchk命令锁定表。

还可以通过--var_name=value选项设置下面的变量:

变量

默认值

decode_bits

9

ft_max_word_len

取决于版本

ft_min_word_len

4

ft_stopword_file

内建列表

key_buffer_size

523264

myisam_block_size

1024

read_buffer_size

262136

sort_buffer_size

2097144

sort_key_blocks

16

stats_method

nulls_unequal

write_buffer_size

262136

可以用myisamchk --help检查myisamchk变量及其 默认值:

当用排序键值修复键值时使用sort_buffer_size,使用--recover时这是很普通的情况。

当用--extend-check检查表或通过一行一行地将键值插入表中(如同普通插入)来修改键值时使用Key_buffer_size。在以下情况通过键值缓冲区进行修复:

·         使用--safe-recover

·         当直接创建键值文件时,需要对键值排序的临时文件有两倍大。通常是当CHARVARCHAR、或TEXT列的键值较大的情况,因为排序操作在处理过程中需要保存全部键值。如果你有大量临时空间,可以通过排序强制使用myisamchk来修复,可以使用--sort-recover选项。

通过键值缓冲区的修复占用的硬盘空间比使用排序么少,但是要慢。

如果想要快速修复,将key_buffer_sizesort_buffer_size变量设置到大约可用内存的25%。可以将两个变量设置为较大的值,因为一个时间只使用一个变量。

myisam_block_size是用于索引块的内存大小。

stats_method影响当给定--analyze选项时,如何为索引统计搜集处理NULL值。它如同myisam_stats_method系统变量。详细信息参见5.3.3节,“服务器系统变量”7.4.7节,“MyISAM索引统计集合”myisam_stats_method的描述。

ft_min_word_lenft_max_word_len表示FULLTEXT索引的最小和最大字长。ft_stopword_file为停止字文件的文件名。需要在以下环境中对其进行设置。

如果你使用myisamchk来修改表索引(例如修复或分析),使用最小和最大字长和停止字文件的 默认全文参数值(除非你另外指定)重建FULLTEXT索引。这样会导致查询失败。

出现这些问题是因为只有服务器知道这些参数。它们没有保存在MyISAM索引文件中。如果你修改了服务器中的最小或最大字长或停止字文件,要避免该问题,为用于mysqldmyisamchk指定相同的ft_min_word_lenft_max_word_lenft_stopword_file值。例如,如果你将最小字长设置为3,可以这样使用myisamchk来修复表:

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

要想确保myisamchk和服务器使用相同的全文参数值,可以将它们放入选项文件的[mysqld][myisamchk]小节:

[mysqld]
ft_min_word_len=3
 
[myisamchk]
ft_min_word_len=3

除了myisamchk,还可以使用REPAIR TABLEANALYZE TABLEOPTIMIZE TABLEALTER TABLE。这些语句由服务器执行,知道要使用的正确的全文参数值。

5.9.5.2. 用于myisamchk的检查选项

myisamchk支持下面的表检查操作选项:

·         --check, -c

检查表的错误。如果你不明确指定操作类型选项,这就是默认操作。

·         --check-only-changed, -C

只检查上次检查后有变更的表。

·         --extend-check, -e

非常仔细地检查表。如果表有许多索引将会相当慢。该选项只能用于极端情况。一般情况下,可以使用myisamchkmyisamchk --medium-check来确定表内是否有错误。

如果你使用了--extend-check并且有充分的内存,将key_buffer_size变量设置为较大的值可以使修复操作运行得更快。

·         --fast-F

只检查没有正确关闭的表。

·         --force, -f

如果myisamchk发现表内有任何错误,则自动进行修复。维护类型与--repair-r选项指定的相同。

·         --information, -i

打印所检查表的统计信息。

·         --medium-check, -m

--extend-check更快速地进行检查。只能发现99.99%的错误,在大多数情况下就足够了。

·         --read-only, -T

不要将表标记为已经检查。如果你使用myisamchk来检查正被其它应用程序使用而没有锁定的表很有用,例如当用--skip-external-locking选项运行时运行mysqld

·         --update-state, -U

将信息保存在.MYI文件中,来表示表检查的时间以及是否表崩溃了。该选项用来充分利用--check-only-changed选项,但如果mysqld服务器正使用表并且正用--skip-external-locking选项运行时不应使用该选项。

5.9.5.3. myisamchk的修复选项

myisamchk支持下面的表修复操作的选项:

·         --backup, -B

.MYD文件备份为file_name-time.BAK

·         --character-sets-dir=path

字符集安装目录。参见5.10.1节,“数据和排序用字符集”

·         --correct-checksum

纠正表的校验和信息。

·         --data-file-length=len, -D len

数据文件的最大长度(当重建数据文件且为“满”时)

·         --extend-check-e

进行修复,试图从数据文件恢复每一行。一般情况会发现大量的垃圾行。不要使用该选项,除非你不顾后果。

·         --force, -f

覆盖旧的中间文件(文件名类似tbl_name.TMD),而不是中断。

·         --keys-used=val, -k val

对于myisamchk,该选项值为位值,说明要更新的索引。选项值的每一个二进制位对应表的一个索引,其中第一个索引对应位0。选项值0禁用对所有索引的更新,可以保证快速插入。通过myisamchk -r可以重新激活被禁用的索引。

·         --no-symlinks, -l

不跟随符号连接。通常myisamchk修复一个符号连接所指的表。在MySQL 4.0中该选项不存在,因为从4.0开始的版本在修复过程中不移除符号链接。

·         --parallel-recover, -p

-r-n的用法相同,但使用不同的线程并行创建所有键。这是alpha代码。自己承担风险!

·         --quick-q

不修改数据文件,快速进行修复。出现复制键时,你可以两次指定该项以强制myisamchk修改原数据文件。

·         --recover, -r

可以修复几乎所有一切问题,除非唯一的键不唯一时(对于MyISAM表,这是非常不可能的情况)。如果你想要恢复表,这是首先要尝试的选项。如果myisamchk报告表不能用-r恢复,则只能尝试-o。在不太可能的情况下-r失败,数据文件保持完好)

如果你有大量内存,你应增加sort_buffer_size的值

·         --safe-recover, -o

使用一个老的恢复方法读取,按顺序读取所有行,并根据找到的行更新所有索引树。这比-r慢些,但是能处理-r不能处理的情况。该恢复方法使用的硬盘空间比-r少。一般情况,你应首先用-r维修,如果-r失败则用-o

如果你有大量内存,你应增加sort_buffer_size的值

·         (OBSOLETE) --set-character-set=name

MySQL 5.1中不使用。参见--set-collation

·         --set-collation=name

更改用来排序表索引的校对规则。校对规则名的第一部分包含字符集名。

·         --sort-recover, -n

强制myisamchk通过排序来解析键值,即使临时文件将可能很大。

·         --tmpdir=path, -t path

用于保存临时文件的目录的路径。如果未设置,myisamchk使用TMPDIR环境变量的值。tmpdir可以设置为一系列目录路径,用于成功地以round-robin模式创建临时文件。在Unix中,目录名之间的间隔字符为冒号(:),在WindowsNetWareOS/2中为分号 ()

·         --unpack-u

将用myisampack打包的表解包。

5.9.5.4. 用于myisamchk的其它选项

myisamchk支持以下表检查和修复之外的其它操作的选项:

·         --analyze-a

分析键值的分布。这通过让联结优化器更好地选择表应该以什么次序联结和应该使用哪个键来改进联结性能。要想获取分布相关信息,使用myisamchk --description --verbose tbl_name命令或SHOW KEYS FROM tbl_name语句。

·         --description, -d

打印出关于表的描述性信息。

·         --set-auto-increment[=value], -A[value]

强制从给定值开始的新记录使用AUTO_INCREMENT编号(或如果已经有AUTO_INCREMENT值大小的记录,应使用更高值)。如果未指定value,新记录的AUTO_INCREMENT编号应使用当前表的最大值加上1

·         --sort-index, -S

以从高到低的顺序排序索引树块。这将优化搜寻并且将使按键值的表扫描更快。

·         --sort-records=N, -R N

根据一个具体索引排序记录。这使你的数据更局部化并且可以加快在该键上的SELECTORDER BY的范围搜索。(第一次做排序可能很慢!)为了找出一张表的索引编号,使用SHOW INDEX,它以myisamchk看见他们的相同顺序显示一张表的索引。索引从1开始编号。

如果键没有打包(PACK_KEYS=0),它们的长度相同,因此当myisamchk 排序并移动记录时,只覆盖索引中的记录偏移量。如果键已经打包(PACK_KEYS=1)myisamchk必须先解开打包的键块,然后重新创建索引并再次将键块打包。(在这种情况下,重新创建索引比更新每个索引的偏移量要快)

5.9.5.5. myisamchk内存使用

当你运行myisamchk时内存分配重要.MYIsamchk使用的内存大小不能超过用-O选项指定的。如果你想对每一个大表使用myisamchk,你必须首先确定你想使用多少内存。修复时可以使用的 默认值只有3MB。使用更大的内存,可以让myisamchk工作得更快一些。例如,如果有大于32MBRAM,可以使用如下所示选项(除了你可以指定的其它选项)

shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...

对于大多数情况,使用-O sort=16M应该足够了。

应记住myisamchk使用TMPDIR中的临时文件。如果TMPDIR指向内存文件系统,你可能很容易得到内存溢出的错误。如果发生,设定TMPDIR指向有更多空间的文件系统目录并且重启myisamchk

修复时myisamchk也需要大量硬盘空间:

·         将数据文件大小扩大一倍(原文件和复制文件)。如果你用--quick修复则不需要该空间;在这种情况下,只重新创建了索引文件。在文件系统上需要的空间与原数据文件相同!(创建的复制文件位于原文件所在目录)

·         代替旧索引文件的新索引文件所占空间。修复工作一开始,就对旧索引文件进行了删减,因此你通常会忽略该空间。在文件系统上需要的该空间与原数据文件相同!

·         当使用--recover---sort-recover(但不使用--safe-recover)时,需要排序缓冲区空间。需要的空间为:

·                (largest_key + row_pointer_length) * number_of_rows * 2

可以用myisamchk -dv tbl_name检查键值和row_pointer_length的长度。在临时目录分配该空间(TMPDIR--tmpdir=path指定)

如果在修复过程中出现硬盘空间问题,可以试试用--safe-recover代替--recover

5.9.5.6. 将myisamchk用于崩溃恢

如果用--skip-external-locking运行mysqld(在某些系统上为 默认设置,例如Linux),当mysqld使用某个表时,你不能可靠地使用myisamchk来检查相同的表。当你运行myisamchk时如果可以确保没有人在通过mysqld访问表,在开始检查表前,你只需要运行mysqladmin flush-tables。如果你不能保证,则你检查表时你必须停止mysqld。如果mysqld更新表时运行myisamchk,你可能会获得表被破坏的警告,即使事实并非如此。

如果不使用--skip-external-locking,可以随时使用myisamchk来检查表。当检查表时,所有尝试更新表的客户端将等待,直到myisamchk准备好可以继续。

如果使用myisamchk来修复或优化表,必须确保mysqld服务器没有在使用该表(如果你正使用--skip-external-locking选项也适用)。如果不关闭mysqld,在运行myisamchk之前至少应执行mysqladmin flush-tables。如果服务器和myisamchk同时访问表,表可能会被破坏

本节描述如何检查和处理MySQL数据库中的数据破坏。如果表经常被破坏,你应尽力找到原因。参见A.4.2节,“如果MySQL依然崩溃,应作些什么”

关于MyISAM表怎样会被破坏的解释,参见15.1.4节,“MyISAM表方面的问题”

在执行崩溃恢复时,理解在一个数据库中的每一个MyISAMtbl_name对应的在数据库目录中的3个文件是很重要的:

文件

目的

tbl_name.frm

定义(格式)文件

tbl_name.MYD

数据文件

tbl_name.MYI

索引文件

3类文件的每一类都可能遭受不同形式的损坏,但是问题最常发生在数据文件和索引文件。

myisamchk通过一行一行地创建一个.MYD数据文件的副本来工作,它通过删除旧的.MYD 文件并且重命名新文件到原来的文件名结束修复阶段。如果你使用--quickmyisamchk不创建一个临时.MYD文件,只是假定.MYD文件是正确的并且仅创建一个新的索引文件,不接触.MYD文件,这是安全的,因为myisamchk自动检测.MYD文件是否损坏并且在这种情况下,放弃修复。你也可以给myisamchk两个--quick选项。在这种情况下,myisamchk不会在一些错误上(象重复键)放弃,相反试图通过修改.MYD文件解决它们。通常,只有在太少的空闲磁盘空间上实施正常修复,使用两个--quick选项时才有用。在这种情况下,你至少应该在运行myisamchk前做进行备份。

5.9.5.7. 如何检查MyISAM表的错误

要想检查MyISAM表,应使用下面的命令:

·         myisamchk tbl_name

这样能找出99.99%的错误。它不能找出的是仅仅涉及数据文件的损坏(这很不常见)。如果想要检查一张表,通常应该没有选项地运行myisamchk或用-s--silent选项的任何一个。

·         myisamchk -m tbl_name

这样能找出99.99%的错误。它首先检查所有索引条目的错误并通读所有行。它还计算行内所有键值的校验和,并确认校验和与索引树内键的校验和相匹配。

·         myisamchk -e tbl_name

可以完全彻底地检查数据(-e意思是“扩展检查”)。它对每一行做每个键的读检查以证实它们确实指向正确的行。这在一个有很多键的大表上可能花很长时间。myisamchk通常将在它发现第一个错误以后停止。如果你想要获得更多的信息,可以增加--verbose(-v)选项。这使得myisamchk继续一直到最多20个错误。

·         myisamchk -e -i tbl_name

象前面的命令一样,但是-i选项告诉myisamchk还打印出一些统计信息。

在一般使用中,一个简单的myisamchk(没有除表名以外的参数)就足够检查表了。

5.9.5.8. 如何修复表

本节描述如何对MyISAM表使用myisamchk(扩展名.MYI.MYD)

你还可以(并且应该,如果可能)使用CHECK TABLEREPAIR TABLE语句来检查和修复MyISAM表。参见13.5.2.3节,“CHECK TABLE语法”13.5.2.6节,“REPAIR TABLE语法”

一张损坏的表的症状通常是查询意外中断并且能看到下述错误:

  • tbl_name.frm被锁定不能更改。
  • 不能找到文件tbl_name.MYIErrcodennn)。
  • 文件意外结束。
  • 记录文件被毁坏。
  • 从表处理器得到错误nnn

要想得到错误相关的详细信息,你可以运行perror nnn,其中nnn为错误编号。下面的示例显示了如何使用perror来找到最常用错误编号(用表的方式指出问题)的含义:

shell> perror 126 127 132 134 135 136 141 144 145
126 = Index file is crashed / Wrong file format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on write or update
144 = Table is crashed and last repair failed

145 = Table was marked as crashed and should be repaired

请注意错误135(记录文件中没有更多的空间)和错误136(索引文件中没有更多的空间)不是可以通过简单修复可以修复的错误。在这种情况下,必须使用ALTER TABLE来增加MAX_ROWSAVG_ROW_LENGTH表选项值:

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

如果你不知道当前的表的选项值,使用SHOW CREATE TABLEDESCRIBE来查询

对于其它的错误,你必须修复表。myisamchk通常可以检测和修复大多数问题。

修复过程包括四个阶段,此处将进行描述。开始修复前,应进入数据库目录并检查表文件的许可。在Unix中,确保它们对于运行mysqld用户可读(你也应可读,因为你需要访问检查的文件)。如果你需要修改文件,你还必须拥有写访问权限。

myisamchk修复表的选项的描述参见5.9.5节,“myisamchk:MyISAM表维护实用工具”的前几节。

下面几节列出了上述命令失败或你想要使用myisamchk提供的扩展特性等情况的例子。

如果你要通过命令行来修复表,必须首先停止mysqld服务器。请注意当你在远程服务器上运行mysqladmin shutdown时,mysqladmin返回后,mysqld服务器将仍然运行一会儿,直到停止所有查询并将所有键清空到硬盘上。

阶段1:检查你的表

如果你有很多时间,运行myisamchk *.MYImyisamchk -e *.MYI。使用-s(沉默)选项禁止不必要的信息。

如果mysqld服务器处于宕机状态,应使用--update-state选项来告诉myisamchk将表标记为'检查过的'

你必须只修复那些myisamchk报告有错误的表。对这样的表,继续到阶段2

如果在检查时,你得到奇怪的错误(例如out of memory错误),或如果myisamchk崩溃,到阶段3

阶段2:简单安全的修复

注释:如果想更快地进行修复,当运行myisamchk时,你应将sort_buffer_sizeKey_buffer_size变量的值设置为可用内存的大约25%

首先,试试myisamchk -r -q tbl_name(-r -q意味着“快速恢复模式”)。这将试图不接触数据文件来修复索引文件。如果数据文件包含它应有的一切内容和指向数据文件内正确地点的删除连接,这应该管用并且表可被修复。开始修复下一张表。否则,执行下列过程:

  1. 在继续前对数据文件进行备份。
  2. 使用myisamchk -r tbl_name(-r意味着“恢复模式”)。这将从数据文件中删除不正确的记录和已被删除的记录并重建索引文件。
  3. 如果前面的步骤失败,使用myisamchk --safe-recover tbl_name。安全恢复模式使用一个老的恢复方法,处理常规恢复模式不行的少数情况(但是更慢)

如果在修复时,你得到奇怪的错误(例如out of memory错误),或如果myisamchk崩溃,到阶段3

阶段3:困难的修复

只有在索引文件的第一个16K块被破坏,或包含不正确的信息,或如果索引文件丢失,你才应该到这个阶段。在这种情况下,需要创建一个新的索引文件。按如下步骤操做:

  1. 把数据文件移到安全的地方。
  2. 使用表描述文件创建新的()数据文件和索引文件:
  3. shell> mysql db_name
  4. mysql> SET AUTOCOMMIT=1;
  5. mysql> TRUNCATE TABLE tbl_name;
  6. mysql> quit

如果你的MySQL版本没有TRUNCATE TABLE,则使用DELETE FROM tbl_name

  1. 将老的数据文件拷贝到新创建的数据文件之中。(不要只是将老文件移回新文件之中;你要保留一个副本以防某些东西出错。)

回到阶段2。现在myisamchk -r -q应该工作了。(这不应该是一个无限循环)。

你还可以使用REPAIR TABLE tbl_name USE_FRM,将自动执行整个程序。

阶段4:非常困难的修复

只有.frm描述文件也破坏了,你才应该到达这个阶段。这应该从未发生过,因为在表被创建以后,描述文件就不再改变了。

  1. 从一个备份恢复描述文件然后回到阶段3。你也可以恢复索引文件然后回到阶段2。对后者,你应该用myisamchk -r启动。
  2. 如果你没有进行备份但是确切地知道表是怎样创建的,在另一个数据库中创建表的一个拷贝。删除新的数据文件,然后从其他数据库将描述文件和索引文件移到破坏的数据库中。这样提供了新的描述和索引文件,但是让.MYD数据文件独自留下来了。回到阶段2并且尝试重建索引文件。

5.9.5.9. 表优化

为了组合碎片记录并且消除由于删除或更新记录而浪费的空间,以恢复模式运行myisamchk

shell> myisamchk -r tbl_name

你可以用SQLOPTIMIZE TABLE语句使用的相同方式来优化表,OPTIMIZE TABLE可以修复表并对键值进行分析,并且可以对索引树进行排序以便更快地查找键值。实用程序和服务器之间不可能交互操作,因为当你使用OPTIMIZE TABLE时,服务器做所有的工作。参见13.5.2.5节,“OPTIMIZE TABLE语法”

myisamchk还有很多其它可用来提高表的性能的选项:

·         -S, --sort-index

·         -R index_num, --sort-records=index_num

·         -a, --analyze

关于这些选项的完整的描述,参见5.9.5节,“myisamchk:MyISAM表维护实用工具”