当MYI文件损坏或丢失时，如何从MySQL命令提示符修复所有数据库中的所有表？

When dealing with MySQL database corruption, if the MYI index file is missing or if its header is corrupted you can't use a myisamchk command:

处理MySQL数据库损坏时，如果MYI索引文件丢失或其标头已损坏，则无法使用myisamchk命令：

myisamchk --safe-recover --force --sort_buffer_size=2G --key_buffer_size=2G /var/lib/mysql/*/*.MYI

You have to do the repair from the MySQL command prompt with the use_frm option:

您必须使用use_frm选项从MySQL命令提示符进行修复：

repair tbl_name use_frm;

Per MySQL documentation's on repairing tables

每个MySQL文档都在修复表

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

如果.MYI索引文件丢失或其标头已损坏，则可以使用USE_FRM选项。此选项告诉MySQL不要信任.MYI文件头中的信息，并使用.frm文件中的信息重新创建它。使用myisamchk无法进行此类修复。

With myisamchk, you can easily drop into each database folder and repair every table by using asterisks at the end of command:

使用myisamchk，您可以轻松地放入每个数据库文件夹并使用命令末尾的星号修复每个表：

/var/lib/mysql/*/*.MYI

You can't do anything similar from the MySQL command prompt.

你不能在MySQL命令提示符下做任何类似的事情。

There's a * question with an answer that explains how to repair all tables within one specific database from the MySQL command prompt with a procedure:

有一个*问题的答案解释了如何使用以下过程从MySQL命令提示符修复一个特定数据库中的所有表：

CREATE DEFINER = 'root'@'localhost'
PROCEDURE MYDATABASE.repair_all()
BEGIN
  DECLARE endloop INT DEFAULT 0;
  DECLARE tableName char(100);
  DECLARE rCursor CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE();
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endloop=1;

  OPEN rCursor;
  FETCH rCursor INTO tableName;

  WHILE endloop = 0 DO
    SET @sql = CONCAT("REPAIR TABLE `", tableName, "`");
    PREPARE statement FROM @sql;
    EXECUTE statement;

    FETCH rCursor INTO tableName;
  END WHILE;

  CLOSE rCursor;
END

Is it possible to modify a procedure like this to loop through all your MySQL databases and repair every table within those databases?

是否可以修改这样的过程来遍历所有MySQL数据库并修复这些数据库中的每个表？

I think this could be useful for anyone who has a large number of databases and runs into serious corruption.

我认为这对于拥有大量数据库并遇到严重损坏的人来说非常有用。

2 个解决方案

#1

mysqlcheck is a more convenient command-line interface to the MySQL CHECK, REPAIR, ANALYZE and OPTIMIZE statements.

mysqlcheck是MySQL CHECK，REPAIR，ANALYZE和OPTIMIZE语句的更方便的命令行界面。

mysqlcheck --repair --use-frm --all-databases

#2

Here's my solution for when I had to fix all of the MyISAM files in my DB:

这是我必须修复数据库中所有MyISAM文件时的解决方案：

find ./ -name "*.MYI" -exec myisamchk -r {} \;

It traverses all of the databases.

它遍历所有数据库。

#1