MySQL MariaDB 崩溃：引擎中不存在表 && 无法恢复表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19926802/
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
MariaDB crash: Table doesn't exist in engine && can't recover tables
提问by SonicFC
I'm running:
我在跑：
MariaDB 10.0.5
Ubuntu 12.04 LTS
Both up to the latest patch level as per last Friday
I've gotten into quite a pickle with a previous issue:
我对以前的问题很不满意：
http://stackoverflow.com/questions/19910996/mysql-connect-headers-and-client-library-minor-version-mismatch-library10000
This has resulted in massive corruption because I copied & removed my ibdata1, ib_logfile0 and ib_logfile1 in order to solve a symptom of the above problem. This issue began, I think, when I next started the mariadb service. When I try to select one of the affected tables or run a mysqldump I get the following:
这导致了大量损坏，因为我复制并删除了 ibdata1、ib_logfile0 和 ib_logfile1 以解决上述问题的症状。我想这个问题是在我下次启动 mariadb 服务时开始的。当我尝试选择受影响的表之一或运行 mysqldump 时，我得到以下信息：
mysqldump -u backupusr -p'somepass' --all-databases > dump.sql
mysqldump: Got error: 1932: "Table 'some_db.dw_commentmeta' doesn't exist in engine" when using LOCK TABLES
Luckily (EXTREMELY luckily) the data of my big production client website is still intact and the site is online. Though there is at least one corrupt table in that DB too. Now to recover all of my other websites' databases.
幸运的是（非常幸运）我的大生产客户网站的数据仍然完好无损并且该网站在线。尽管该数据库中也至少有一个损坏的表。现在恢复我所有其他网站的数据库。
I've still got the original ibdata1, ib_logfile0 and ib_logfile1 files. When I move these files back to /var/lib/mysql/ the mariaDB service fails to start.
我仍然有原始的 ibdata1、ib_logfile0 和 ib_logfile1 文件。当我将这些文件移回 /var/lib/mysql/ 时，mariaDB 服务无法启动。
One of the things I've tried is forcing an InnoDB recovery as per:
我尝试过的一件事是根据以下内容强制 InnoDB 恢复：
http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
But that has no effect. After that I tried force-using the Mysql upgrade tool to see if it could repair the tables:
但这没有效果。之后我尝试强制使用Mysql升级工具，看看它是否可以修复表：
mysql_upgrade --password --force
FATAL ERROR: Upgrade failed
So that doesn't tell me anything. Next I've used the REPAIR TABLE & ALTER TABLE ENGINE commands:
所以这并没有告诉我任何事情。接下来我使用了 REPAIR TABLE & ALTER TABLE ENGINE 命令：
http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html
Alter engine:
改变引擎：
MariaDB [sictnl_db]> ALTER TABLE si_users ENGINE = MyISAM;
ERROR 1932 (42S02): Table 'sictnl_db.si_users' doesn't exist in engine
repair table:
维修表：
MariaDB [sictnl_db]> REPAIR TABLE si_users;
+--------------------+--------+----------+----------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+----------------------------------------------------+
| sictnl_db.si_users | repair | Error | Table 'sictnl_db.si_users' doesn't exist in engine |
| sictnl_db.si_users | repair | status | Operation failed |
+--------------------+--------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)
So this all boils down to getting the table information back into the engine's tables. The documentation at https://mariadb.com/kb/en/mariadb-storage-engines/states that there are two storage engines either of which I'm using per default:
所以这一切都归结为将表信息返回到引擎的表中。https://mariadb.com/kb/en/mariadb-storage-engines/ 上的文档指出，我默认使用两个存储引擎：
Aria
MyISAM
When performaning the SHOW CREATE TABLE function on one of the uncorrupted tables I get the following:
在未损坏的表之一上执行 SHOW CREATE TABLE 函数时，我得到以下信息：
ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
So MyISAM it is, googling 'mysql repair myisam' leads me to the following site:
所以 MyISAM 是，谷歌搜索 'mysql repair myisam' 引导我到以下站点：
Note: Would Aria have protected me against this issue?
http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
This yields me the myisamchk command, which needs to be pointed to the local storage of the tables like so:
这产生了 myisamchk 命令，它需要指向表的本地存储，如下所示：
myisamchk /var/lib/mysql/sictnl_db/*
myisamchk: error: '/var/lib/mysql/sictnl_db/db.opt' is not a MyISAM-table
---------
myisamchk: error: '/var/lib/mysql/sictnl_db/si_commentmeta.frm' is not a MyISAM-table
---------
myisamchk: error: '/var/lib/mysql/sictnl_db/si_commentmeta.ibd' is not a MyISAM-table
So the tool seems to be missing the .MYI files, but these files don't exist in the shell.
所以该工具似乎缺少 .MYI 文件，但这些文件在 shell 中不存在。
I'm posting this now to see whether you've got any helpful comment, Ill continue my analysis in the mean time and update the post should I get any further.
我现在发布这个是为了看看你是否有任何有用的评论，我会同时继续我的分析，如果我有进一步的进展，我会更新帖子。
Thanks in advance!
提前致谢！
采纳答案by SonicFC
In the end I wasn't able to recover the above server. I tried disabling one site at a time to prevent DB-access of the corrupted tables. In the end one DB contained the issue and by disabling the site mariadb didn't crash anymore.
最后我无法恢复上述服务器。我尝试一次禁用一个站点以防止对损坏的表进行 DB 访问。最后，一个数据库包含了这个问题，并且通过禁用站点 mariadb 不再崩溃。
To be sure I extracted the database data and started over on a new server. That was about 5 hours of work but after that I'm confident in my setup once again.
可以肯定的是，我提取了数据库数据并在新服务器上重新开始。那是大约 5 个小时的工作，但在那之后我再次对我的设置充满信心。
I've started work on restoring an old backup of the corrupted data, I've also switched back from MariaDB to Mysql as I suspect MariaDB of being the culprit.
我已经开始恢复损坏数据的旧备份，我也从 MariaDB 切换回 Mysql，因为我怀疑 MariaDB 是罪魁祸首。
So backups saved my day here...
所以备份在这里拯救了我的一天......
