MySQL :: MySQL 8.4 参考手册 :: 9.6.3 如何修复 MyISAM 表

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 9 备份与恢复 / 9.6 MyISAM 表维护和崩溃恢复 / 9.6.3 如何修复 MyISAM 表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

9.6.3 如何修复 MyISAM 表9.6.3 How to Repair MyISAM Tables

本节讨论了如何在 MyISAM 表（扩展名为 .MYI 和 .MYD）上使用 myisamchk。The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD).

你也可以使用 CHECK TABLE 和 REPAIR TABLE 语句来检查和修复 MyISAM 表。参见第 15.7.3.2 节，“CHECK TABLE 语句”，以及第 15.7.3.5 节，“REPAIR TABLE 语句”。You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See Section 15.7.3.2, “CHECK TABLE Statement”, and Section 15.7.3.5, “REPAIR TABLE Statement”.

损坏的表的症状包括查询意外中止以及可观察到的错误，例如这些：Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

找不到文件 tbl_name.MYI (错误码: nnn)Can't find file tbl_name.MYI (Errcode: nnn)
意外的文件结尾Unexpected end of file
记录文件已损坏Record file is crashed
从表处理器收到错误 nnnGot error nnn from table handler

要获取有关错误的更多信息，请运行 perror nnn，其中 nnn 是错误编号。以下示例显示了如何使用 perror 查找最常见的指示表问题的错误编号的含义：To get more information about the error, run perror nnn, where nnn is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table:

$> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

请注意，错误 135（记录文件没有更多空间）和错误 136（索引文件没有更多空间）不是可以通过简单修复解决的错误。在这种情况下，您必须使用 ALTER TABLE 来增加 MAX_ROWS 和 AVG_ROW_LENGTH 表选项的值：Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

如果您不知道当前的表选项值，请使用 SHOW CREATE TABLE。If you do not know the current table option values, use SHOW CREATE TABLE.

对于其他错误，您必须修复您的表。myisamchk 通常可以检测并修复大多数发生的问题。For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

修复过程包括最多三个阶段，如下所述。在开始之前，你应该切换到数据库目录，并检查表文件的权限。在 Unix 系统上，确保它们对于运行 mysqld 的用户是可读的（以及对你也可读，因为你需要访问正在检查的文件）。如果需要修改文件，它们也必须对你可写。The repair process involves up to three stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

本节适用于表检查失败的情况（例如在第 9.6.2 节，“如何检查 MyISAM 表的错误”中所述的情况），或者您希望使用myisamchk 提供的扩展功能。This section is for the cases where a table check fails (such as those described in Section 9.6.2, “How to Check MyISAM Tables for Errors”), or you want to use the extended features that myisamchk provides.

用于表维护的 myisamchk 选项在第 6.6.4 节，“myisamchk — MyISAM 表维护工具” 中有描述。myisamchk 还有可以设置的变量，用于控制内存分配，可能提高性能。参见第 6.6.4.6 节，“myisamchk 内存使用”。The myisamchk options used for table maintenance with are described in Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”. myisamchk also has variables that you can set to control memory allocation that may improve performance. See Section 6.6.4.6, “myisamchk Memory Usage”.

如果你打算从命令行修复表，你必须先停止 mysqld 服务器。请注意，当你在远程服务器上执行 mysqladmin shutdown 时，mysqld 服务器在 mysqladmin 返回后仍然可用一段时间，直到所有语句处理停止并且所有索引更改已刷新到磁盘。If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still available for a while after mysqladmin returns, until all statement-processing has stopped and all index changes have been flushed to disk.

阶段 1：检查你的表Stage 1: Checking your tables

如果你有更多时间，可以运行 myisamchk *.MYI 或 myisamchk -e *.MYI。使用 -s（静默）选项可以抑制不必要的信息。Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

如果 mysqld 服务器已停止，你应该使用 --update-state 选项来告诉 myisamchk 将表标记为 “已检查。”If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as “checked.”

你只需要修复 myisamchk 报告有错误的表。对于这些表，请继续到第 2 阶段。You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

如果在检查时出现意外错误（例如 out of memory 错误），或者 myisamchk 崩溃，请进入阶段 3。If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

阶段 2：简单安全修复Stage 2: Easy safe repair

首先，尝试 myisamchk -r -q tbl_name (-r -q 意思是 “快速恢复模式”)。这会尝试修复索引文件而不触碰数据文件。如果数据文件包含所需的一切，且删除链接指向数据文件中的正确位置，这应该有效，表格就被修复了。然后开始修复下一个表。否则，请使用以下步骤：First, try myisamchk -r -q tbl_name (-r -q means “quick recovery mode”). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

在继续之前备份数据文件。Make a backup of the data file before continuing.
使用 myisamchk -r tbl_name（-r 表示 “恢复模式”）。这会从数据文件中删除错误行和已删除行，并重建索引文件。Use myisamchk -r tbl_name (-r means “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.
如果前一步骤失败，请使用 myisamchk --safe-recover tbl_name。安全恢复模式使用一种旧的恢复方法，能处理常规恢复模式无法处理的部分情况（但速度较慢）。If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note

如果你希望修复操作进行得更快，你应该在运行 myisamchk 时，将 sort_buffer_size 和 key_buffer_size 变量的值各设置为可用内存的约 25%。If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

如果在修复时遇到意外错误（例如 out of memory 错误），或者 myisamchk 崩溃，请进入阶段 3。If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

阶段 3：困难修复Stage 3: Difficult repair

只有在索引文件的第一个 16KB 块被破坏或包含错误信息，或者索引文件丢失时，您才应该到达此阶段。在这种情况下，需要创建一个新的索引文件。操作步骤如下：You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

将数据文件移动到一个安全的地方。Move the data file to a safe place.
使用表描述文件创建新的（空的）数据和索引文件：Use the table description file to create new (empty) data and index files:
```
$> mysql db_name
```
```
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
```
将旧数据文件复制回新创建的数据文件。（不要只是将旧文件移动回新文件。您需要保留一份副本，以防出现问题。）Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Important

如果您使用复制功能，应在执行上述操作之前停止它，因为该操作涉及文件系统操作，而这些操作不会被 MySQL 记录。If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.

返回到第二阶段。myisamchk -r -q 应该可以工作。（这不应是一个无限循环。）Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

您也可以使用 REPAIR TABLE tbl_name USE_FRM SQL 语句，它会自动执行整个过程。当您使用 REPAIR TABLE 时，工具和服务器之间也不会有不希望的交互，因为所有工作都由服务器完成。请参见第 15.7.3.5 节，“REPAIR TABLE 语句”。You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 15.7.3.5, “REPAIR TABLE Statement”.

首页上一级下一节