MySQL 如何修复 InnoDB 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/226172/
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 do I repair an InnoDB table?
提问by lefticus
We (apparently) had poorly executed of our Solaris MySQL database engine last night. At least some of the InnoDB tables are corrupted, with timestamp out of order errors in the transaction log, and a specific error about the index being corrupted.
我们（显然）昨晚对我们的 Solaris MySQL 数据库引擎执行得很差。至少有一些 InnoDB 表已损坏，事务日志中出现时间戳乱序错误，以及有关索引损坏的特定错误。
We know about the tools available for MyISAM table repairs, but cannot find anything for InnoDB.
我们知道可用于 MyISAM 表修复的工具，但找不到任何适用于 InnoDB 的工具。
Side note: attempting a table optimize (in my attempt to rebuild the corrupted index) causes the database server to crash.
旁注：尝试优化表（在我尝试重建损坏的索引时）会导致数据库服务器崩溃。
采纳答案by Jon Topper
回答by Sandro Frattura
stop your application...or stop your slave so no new rows are being added
停止您的应用程序...或停止您的从属程序，以便不会添加新行
create table <new table> like <old table>;
insert <new table> select * from <old table>;
truncate table <old table>;
insert <old table> select * from <new table>;
restart your server or slave
重新启动您的服务器或从站
回答by jpetazzo
The following solution was inspired by Sandro's tip above.
以下解决方案的灵感来自上面 Sandro 的提示。
Warning: while it worked for me, but I cannot tell if it will work for you.
警告：虽然它对我有用，但我不知道它是否适合你。
My problem was the following: reading some specific rows from a table (let's call this table broken) would crash MySQL. Even SELECT COUNT(*) FROM brokenwould kill it. I hope you have a PRIMARY KEYon this table (in the following sample, it's id).
我的问题如下：从表中读取一些特定的行（我们称之为表broken）会使 MySQL 崩溃。甚至SELECT COUNT(*) FROM broken会杀了它。我希望你PRIMARY KEY在这张桌子上有一个（在下面的示例中，它是id）。
- Make sure you have a backup or snapshot of the broken MySQL server (just in case you want to go back to step 1 and try something else!)
CREATE TABLE broken_repair LIKE broken;INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;- Repeat step 3 until it crashes the DB (you can use
LIMIT 100000and then use lower values, until usingLIMIT 1crashes the DB). - See if you have everything (you can compare
SELECT MAX(id) FROM brokenwith the number of rows inbroken_repair). - At this point, I apparently had all my rows (except those which were probably savagely truncated by InnoDB). If you miss some rows, you could try adding an
OFFSETto theLIMIT.
- 确保您有损坏的 MySQL 服务器的备份或快照（以防万一您想返回第 1 步并尝试其他操作！）
CREATE TABLE broken_repair LIKE broken;INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;- 重复步骤 3，直到它使数据库崩溃（您可以使用
LIMIT 100000然后使用较低的值，直到使用LIMIT 1使数据库崩溃）。 - 看看您是否拥有一切（您可以
SELECT MAX(id) FROM broken与 中的行数进行比较broken_repair）。 - 在这一点上，我显然拥有我所有的行（除了那些可能被 InnoDB 粗暴截断的行）。如果您错过了一些行，您可以尝试
OFFSET在LIMIT.
Good luck!
祝你好运！
回答by freytag
Here is the solution provided by MySQL: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
以下是 MySQL 提供的解决方案：http: //dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
回答by Ragen Dazs
See this article: http://www.unilogica.com/mysql-innodb-recovery/(It's in portuguese)
见这篇文章：http: //www.unilogica.com/mysql-innodb-recovery/（它是葡萄牙语）
Are explained how to use innodb_force_recoveryand innodb_file_per_table. I discovered this after need to recovery a crashed database with a single ibdata1.
解释了如何使用innodb_force_recovery和innodb_file_per_table。我在需要使用单个ibdata1恢复崩溃的数据库后发现了这一点。
Using innodb_file_per_table, all tables in InnoDB will create a separated table file, like MyISAM.
使用 innodb_file_per_table，InnoDB 中的所有表都会创建一个单独的表文件，如 MyISAM。
回答by Samuel Jaison
Note: If your issue is, "innodb index is marked as corrupted"! Then, the simple solution can be, just remove the indexes and add them again. That can solve pretty quickly without losing any records nor restarting or moving table contents into a temporary table and back.
注意：如果您的问题是“innodb 索引被标记为损坏”！然后，简单的解决方案可以是，只需删除索引并再次添加它们。这可以很快解决，而不会丢失任何记录，也不会重新启动或将表内容移动到临时表中并返回。
回答by Terry Lin
Step 1.
第1步。
Stop MySQL server
停止 MySQL 服务器
Step 2.
第2步。
add this line to my.cnf ( In windows it is called my.ini )
将此行添加到 my.cnf （在 Windows 中称为 my.ini ）
set-variable=innodb_force_recovery=6
Step 3.
第 3 步。
delete ib_logfile0and ib_logfile1
删除ib_logfile0和ib_logfile1
Step 4.
第四步。
Start MySQL server
启动 MySQL 服务器
Step 5.
第 5 步。
Run this command:
运行此命令：
mysqlcheck --database db_name table_name -uroot -p
After you have successfully fixed the crashed innodb table, don't forget to remove #set-variable=innodb_force_recovery=6 from my.cnf and then restart MySQL server again.
成功修复崩溃的innodb 表后，不要忘记从my.cnf 中删除#set-variable=innodb_force_recovery=6，然后再次重新启动MySQL 服务器。
