《MySQL排错指南》——1.8　特定于存储引擎的问题及解决方案

本节书摘来自异步社区出版社《MySQL排错指南》一书中的第1章，第1.8节，作者：【美】Sveta Smirnova（斯维特 斯米尔诺娃），更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.8　特定于存储引擎的问题及解决方案

实际上，本书讨论的任何问题在你使用的存储引擎下都可能会有细微的差别。这种情况将在本书中一直存在。本节展示一些不依赖于其他问题的存储引擎自身的特性。我们将使用针对MyISAM或InnoDB存储引擎的工具来解决一些问题，因为这两种引擎是最受欢迎和最频繁使用的存储引擎。如果你使用第三方的存储引擎，那么可以查阅它的用户手册来获取有用的工具。

与存储引擎相关的错误要么会反馈到客户端，要么会记录在错误日志文件中。一般情况下，存储引擎的名字也会出现的错误消息中。偶尔，你可能会获得一个用perror工具也查不到的未知错误码。这一般是表明问题来自某存储引擎的信号。

一个常见的存储引擎问题是数据损坏。这不一定总是存储引擎的错误，也可能是由于磁盘损毁、系统崩溃或者MySQL服务器崩溃等外部原因。例如，如果有人使用kill –9终止服务器的进程，那么就很有可能招致数据损坏。这里将讨论如果MyISAM和InnoDB引擎发生数据损坏该怎么做。不会讨论如何修复第三方存储引擎的崩溃；读者可以查询该存储引擎的文档以获得相关指导。作为针对一般情况的建议，可以尝试使用CHECK TABLE命令，很多存储引擎都支持该工具（MyISQM引擎的CHECK TABLE工具将在1.8.1节中详细介绍）。

数据损坏是一个很难诊断的问题，因为往往直到MyISQM服务器访问损坏的表时用户才会发现问题。并且，错误表现出来的特征也有可能产生误导。在最好的情况下，你会得到一条错误消息。然而，问题也可能表现为查询执行错误或者服务器停止。如果问题在某个特定表上突然出现，就始终要检查数据是否损坏。

提示　提示

一旦你怀疑是数据损坏了，就需要修复损坏的表。要养成始终在修复之前备份表文件的习惯，这样你就可以在出错的时候恢复数据了。

1.8.1　MyISAM损坏

MyISAM引擎按照三个文件一组保存每张表：table_name.frm文件包含表的结构（schema），table_name.MYD文件存储数据，以及table_name.MYI文件保存索引。崩溃会损坏数据或者索引文件，或者二者都损坏了。在这种情况下，当访问表时，你就会获得类似“ERROR 126 (HY000): Incorrect key file for table './test/t1.MYI'; try to repair it”或“Table './test/t2' is marked as crashed and last(automatic?) repair failed”的错误消息。错误消息可能各种各样，不过可以检索关键字“repair”或“crashed”来判断是否是表损坏。

SQL语句CHECK TABLE和REPAIR TABLE专门针对数据损坏问题。在操作系统shell里，也可以使用myisamchk工具进行同样的工作。使用myisamchk工具的一个好处就是，可以不必访问正在运行的MySQL服务器。例如，用户可以在崩溃后，在服务器再次启动前尝试修复表。

1．通过SQL修复MyISAM表
CHECK TABLE命令不加参数可以展示当前表的状态：

这是一个损坏的表的输出示例。解决问题的第一步应该是执行不带参数的 REPAIR TABLE命令：

现在很幸运，表修复成功了。可以再次执行CHECK TABLE命令进行确认：

如果单纯地执行REPAIR TABLE没有起到什么效果，那么可以选择另外两个选项。REPAIR TABLE EXTENDED执行速度比REPAIR TABLE慢得多，但是可以修复99%的错误。作为最后的选择，可以执行REPAIR TABLE USE_FRM命令，该命令会不相信索引文件中的信息。它会删除索引并利用table_name.frm文件中的描述重建索引，并通过table_name.MYD文件填充键对应的值。

提示　提示

为了达到同样的目的，还可以使用mysqlcheck工具。该工具通过向服务器发送CHECK和REPAIR命令进行工作。它还有非常好用的选项，如 --all-databases，该参数可以帮助用户高效地执行表的维护。

mysqlcheck像其他客户端一样连接到MySQL服务器工作，因此它可以远程使用。
2．使用myisamchk修复MyISAM表
所有这些步骤也可以通过使用myisamchk完成，该命令包含许多额外的表维护选项。这里不会一一介绍该工具的所有特性，而只是重点介绍与表修复相关的特性。

myisamchk可以直接访问表文件，而无须启动MySQL服务器。在某些情况下，这是非常有用的。同时，myisamchk需要对表文件保持独立的、排他的访问，因此用户也应该避免在MySQL服务器运行过程中使用该工具。

警告　警告

如果必须在服务器运行期间使用myisamchk工具，那么先执行FLUSH TABLES和LOCK TABLE table_name WRITE语句，然后等待直到最后的查询返回命令提示符，接着再在并行会话中执行myisamchk。如果有除了myisamchk以外的进程在myisamchk运行期间访问表，就可能会产生更糟糕的损坏情况。
一条基本的恢复命令是：

其中，--backup选项通知myisamchk在尝试修复表之前先进行数据文件备份，--recover选项执行实际修复。如果这条命令还不够，可以使用--safe-recover选项。该选项会使用在早期的MySQL版本中就存在的恢复模式进行修复，并且会找到简单的--recover选项无法发现的问题。当然，还有更加严格的选项--extend-check。

也可以使用--sort-recover选项，该选项会使用排序来解析键，甚至在临时文件很大的时候。

在其他选项中，推荐你仔细研究非常有用的--description选项，该选项会输出表的描述信息。结合-v或者其等价的--verbose选项，将会输出额外的信息。可以指定两次甚至三次-v选项去获得更多的信息。

1.8.2　InnoDB数据损坏

InnoDB在共享的表空间中存储其数据和索引。如果服务器在创建表时是以选项--innodb_file_per_table选项启动的，那么它也会有自己的数据文件，不过表的定义仍然在共享的表空间中。理解表文件是如何存储的，将有助于高效地维护数据目录和备份。

InnoDB是带有事务的存储引擎，并且其内部机制会自动修复大部分数据损坏错误。它会在服务器启动时进行修复。下面的摘要信息是在MySQL企业级备份（MEB）中执行mysqlbackup --copy-back命令完成备份后从错误日志中摘录的，它展示了一种典型的恢复情况[4]：

不过，有时候数据损坏得很严重并且InnoDB无法在没有用户交互的情况下完成修复。在这种情况下，有--innodb_force_recovery启动选项。该选项可以设置为0～6的任意值（0意味着不强制修复，1是最低级别，6是最高级别）。当修复成功的时候，可以在已修复的表上执行特定类型的请求，不过应该避免执行某些特定的命令。不能执行修改数据的操作，不过该选项仍允许特定的SELECT选择语句和DROP语句。例如，在级别6的情况下，仅可以执行形如SELECT * FROM table_name且不带WHERE条件、ORDER BY排序或者其他语句的查询。

如果发生了损坏，可从1开始依次尝试每个级别的--innodb_force_recovery选项，直到可以启动服务器并且可以访问有问题的表为止。你之前的检查应该已经发现了哪个表损坏了。使用SELECT INTO OUTFILE将表转储到文件中，然后使用DROP和CREATE命令重新创建表。最后，用配置--innodb_force_recovery=0重新启动服务器，然后加载转储的数据。如果问题还存在，尝试找到其他损坏的表然后执行同样的过程直到服务器恢复正常。

当需要在--innodb_force_recovery选项的值是正数的情况下开始修复数据库时，错误日志通常会有类似下面的明确提示消息：

你也会从中发现关于自动修复失败和启动失败的信息。

提示　提示

InnoDB在写实际数据前会立即对数据、索引和日志页写校验和（checksum），并且在从磁盘读数据之后立即确认校验和。这可以避免大多数问题。通常，一旦遭遇InnoDB数据损坏，这就意味着磁盘或内存有问题。