表在引擎中不存在&不能恢复表

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://*.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:

它什么都没告诉我。接下来，我使用了修复表和修改表引擎命令:

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修复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!

提前谢谢!

1 个解决方案

#1

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...

所以备份保存了我在这里的一天……

#1