MySQL InnoDB：尽管存在表的 .frm 文件，但无法从 InnoDB 的内部数据字典打开表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40117532/
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
InnoDB: Cannot open table from the internal data dictionary of InnoDB though the .frm file for the table exists
提问by John Alexander
I am running Windows 10 with XAMPP and several dozen Drupal sites installed on localhost. Everything has been working fine for months.
我正在运行带有 XAMPP 的 Windows 10，并且在本地主机上安装了几十个 Drupal 站点。几个月来，一切都运行良好。
This morning I performed a Windows restore from a restore point two days ago to get rid of an unwanted Windows update. After I did that, my MySQL stopped working. I tried deleting the file ibdata1(I now know that that was a bad idea), but when things got worse, I restored the initial ibdata1that I'd deleted. All the table data (.frmand .ibdfiles) is still in C:\xampp\mysql\data. Now MySQL will at least start, but all the tables are "gone"... I can load phpMyAdmin in the browser, and when I drop down the databases at left, all the tables show... but when I try to click on one, it tells me "table not found."
今天早上，我从两天前的还原点执行了 Windows 还原，以摆脱不需要的 Windows 更新。在我这样做之后，我的 MySQL 停止工作。我尝试删除该文件ibdata1（我现在知道这是一个坏主意），但是当事情变得更糟时，我恢复了ibdata1我删除的初始文件。所有表数据（.frm和.ibd文件）仍然在C:\xampp\mysql\data. 现在 MySQL 至少会启动，但所有的表都“消失了”......我可以在浏览器中加载 phpMyAdmin，当我下拉左边的数据库时，所有的表都会显示......但是当我尝试点击一，它告诉我“找不到表”。
In mysql_error.logthere are several errors like InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists.There is a URL mentioned in the error message that supposedly tells you how to resolve this issue, but it is uninformative.
在mysql_error.log有几个这样的错误InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists.有一个在错误消息中提到的URL，理应告诉你如何解决这个问题，但它是无信息。
I don't thinkibdata1is corrupted... and all the articles regarding recovering data are complicated to a degree that is insanelyout of proportion to the problem. No one has a simple spelled-out solution. Am I Googling wrong? This has happened to me at least twice before, and each time the problem spontaneously resolved itself before I was able to start following the 10-hour-long procedures I found online. Not this time, unfortunately. Surely I'm missing something simple. I had hundreds and hundreds of tables in a few dozen databases, and it seems hopeless to try to find the schema for each one and manually plug it in, which is what most recovery articles seem to suggest.
我不觉得ibdata1被破坏......和所有有关数据恢复的文章很复杂的程度，是出奇的不相称问题。没有人有一个简单明了的解决方案。我谷歌搜索错了吗？这在我身上至少发生过两次，每次在我能够开始遵循我在网上找到的长达 10 小时的程序之前，问题都会自行解决。不幸的是，这次不是。当然，我错过了一些简单的东西。我在几十个数据库中有成百上千个表，尝试为每个表找到模式并手动插入似乎没有希望，这似乎是大多数恢复文章所建议的。
Shouldn't the database just know how to read the .frmand .ibdfiles, and, you know... work? It feelslike there's just one value out of place somewhere, and if I could just find it, everything would just "pop" back into place.
数据库不应该只知道如何读取.frm和.ibd文件，并且，您知道...工作吗？它感觉像有只有一个值了某处的，如果我能找到它，一切都只是“啪”回原位。
回答by Ratata Tata
What worked for me:
什么对我有用：
https://dba.stackexchange.com/a/42932
https://dba.stackexchange.com/a/42932
My dbs was InnoDB, so there is a problem with id and when upgrading the macos, I got this. I will quote the main part:
我的dbs是InnoDB，所以id有问题，升级macos时，我得到了这个。我将引用主要部分：
You must have:
-ibdata1
-ib_logfile0
-ib_logfile1
-.FRM files from your mysql_database folder
-Fresh installation of MAMP / MAMP Pro that you are willing to destroy (if need be)
- SSH into your web server (dev, production, no difference) and browse to your mysql folder (mine was at /var/lib/mysql for a Plesk installation on Linux)
- Compress the mysql folder
- Download an archive of mysql folder which should contain all mySQL databases, whether MyISAM or innoDB (you can scp this file, or move this to a downloadable directory, if need be)
- Install MAMP (Mac, Apache, MySQL, PHP)
- Browse to /Applications/MAMP/db/mysql/
- Backup /Applications/MAMP/db/mysql to a zip archive (just in case)
Copy in all folders and files included in the archive of the mysql folder from the production server (mt Plesk environment in my case) EXCEPT DO NOT OVERWRITE:
-/Applications/MAMP/db/mysql/mysql/
-/Applications/MAMP/db/mysql/mysql_upgrade_info
-/Applications/MAMP/db/mysql/performance_schema
你必须有：
-ibdata1
-ib_logfile0
-ib_logfile1
mysql_database 文件夹中的 -.FRM 文件
- 您愿意销毁的 MAMP / MAMP Pro 的全新安装（如果需要）
- SSH 连接到您的 Web 服务器（开发、生产、没有区别）并浏览到您的 mysql 文件夹（我的文件夹位于 /var/lib/mysql 以在 Linux 上安装 Plesk）
- 压缩mysql文件夹
- 下载 mysql 文件夹的存档，该文件夹应包含所有 mySQL 数据库，无论是 MyISAM 还是 innoDB（如果需要，您可以 scp 此文件，或将其移动到可下载的目录）
- 安装 MAMP（Mac、Apache、MySQL、PHP）
- 浏览到 /Applications/MAMP/db/mysql/
- 将 /Applications/MAMP/db/mysql 备份到 zip 存档（以防万一）
从生产服务器（在我的情况下为 mt Plesk 环境）复制 mysql 文件夹存档中包含的所有文件夹和文件，除了不要覆盖：
-/应用程序/MAMP/db/mysql/mysql/
-/Applications/MAMP/db/mysql/mysql_upgrade_info
-/Applications/MAMP/db/mysql/performance_schema
回答by Shudipta Sharma
In my case, I was restoring the backup (done by galera-arbitrator and xtrabackup. Using the first one we took state-snapshot and using the last one we backed up the physical data) for Percona_XtraDB_Cluster. So, after placing the backed up data to the dir /var/lib/mysqlof the new mysql server, we did the following selectquery and got error.
以我为例，我是在还原备份（通过加莱拉仲裁员和xtrabackup完成。我们使用了状态快照的第一个和使用最后一个我们备份的物理数据）Percona_XtraDB_Cluster。因此，将备份的数据放置到/var/lib/mysql新的 mysql 服务器的目录后，我们进行了以下select查询并出现错误。
$ mysql -uroot -p$MYSQL_ROOT_PASSWORD -h<NEW_SERVER_HOST_NAME> -e 'show columns from <MY_DB>.<MY_TABLE>'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'playground.equipment' doesn't exist
And the server log was:
服务器日志是：
[Warning] InnoDB: Cannot open table <MY_DB>/<MY_TABLE> from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
We just restarted the container the new data. That's it
我们刚刚重新启动了容器中的新数据。就是这样
回答by Paco Lora
What did the work for us was this link(Restoring Orphan File-Per-Table ibd Files).
这个链接（Restoring Orphan File-Per-Table ibd Files）对我们有用。
In brief, you do:
简而言之，您可以：
ALTER TABLE ..... DISCARD TABLESPACE;
For each affected table. Then, for each affected table:
对于每个受影响的表。然后，对于每个受影响的表：
ALTER TABLE .... IMPORT TABLESPACE; SHOW WARNINGS;
You could do that on the same schema or create a new schema in other database and copy all *.ibd files. In this case:
您可以在同一架构上执行此操作，也可以在其他数据库中创建新架构并复制所有 *.ibd 文件。在这种情况下：
- Create the new schema with table definitions.
- Do the discard tablespace for all tables.
- Copy the new *.ibd files to the right folder of new schema.
- Do de import tablespace for all tables.
- 使用表定义创建新架构。
- 为所有表做丢弃表空间。
- 将新的 *.ibd 文件复制到新架构的正确文件夹中。
- 为所有表执行 de import tablespace。
IMPORTANT!It's better that the new schema has the same name as the old one.
重要的！新模式最好与旧模式同名。
回答by Nime Cloud
Just rename slave_master_info.frm to slave_master_info.BAK and that's it. Restart the mysql service.
只需将 slave_master_info.frm 重命名为 slave_master_info.BAK 就可以了。重启mysql服务。
