当 MYI 文件损坏或丢失时，如何从 MySQL 命令提示符修复所有数据库中的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7082043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can you repair all tables in all databases from the MySQL command prompt when MYI file is corrupted or missing?
提问by Drew
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
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 StackOverflow question with an answer that explains how to repair all tables within one specific database from the MySQL command prompt with a procedure:
有一个 StackOverflow 问题，其答案解释了如何从 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.
我认为这对于拥有大量数据库并遇到严重损坏的人来说可能很有用。
回答by John Flatness
mysqlcheckis a more convenient command-line interface to the MySQL CHECK, REPAIR, ANALYZEand OPTIMIZEstatements.
mysqlcheck是一种更方便的命令行界面到MySQL CHECK，REPAIR，ANALYZE和OPTIMIZE语句。
mysqlcheck --repair --use-frm --all-databases
回答by Shawn DeWolfe
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.
它遍历所有数据库。
