MySQL InnoDB 表缺失且未计入数据库结构中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6224158/
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
MySQL InnoDB tables are missing and not counted in database structure
提问by Mac Taylor
I have a serious problem in my MySQL tables , once there were InnoDB tables which were IN USE and now are somehow hidden
我的 MySQL 表中有一个严重的问题，曾经有 InnoDB 表在使用中，现在不知怎么隐藏了
look at this [pic] *Link removed- the number of tables in heading is 79 and actual counted number is 74.
看看这个 [图片] *链接已删除- 标题中的表格数为 79，实际计数为 74。
these tables are those that were IN USE
这些表是那些正在使用的表
I don't have any recent backup of my database , so this would game of life and death for me
我的数据库最近没有任何备份，所以这对我来说是生死攸关的游戏
I checked my VPS, I found them at /etc/lib/mysql/db_name/.
我检查了我的 VPS，我在 /etc/lib/mysql/db_name/ 找到了它们。
EDIT :
编辑 ：
I Searched around internet and I found out that every table should have 3 files related to it.
我在互联网上搜索，发现每个表都应该有 3 个与之相关的文件。
For example, the table table_usershas:
例如，该表table_users具有：
-- table_users.frm
-- table_users.MYD
-- table_users.MYI
and for those hidden table , there are only .frm files and the other two files of a table are missing.
对于那些隐藏的表，只有 .frm 文件，表的其他两个文件丢失。
I should change my question to: How to recover a innodb table from a .frm file?
我应该将我的问题更改为：如何从 .frm 文件中恢复 innodb 表？
回答by Geoffrey
InnoDB does not have those three files
InnoDB 没有这三个文件
InnoDB data is stored in "ibdata1" for all databases and tables. the table definition is stored in "tablename.frm"
InnoDB 数据存储在所有数据库和表的“ibdata1”中。表定义存储在“tablename.frm”中
I would say that your InnoDB file has become corrupted, you may want to have a look at these tools: https://launchpad.net/percona-innodb-recovery-tool
我会说你的 InnoDB 文件已经损坏，你可能想看看这些工具：https: //launchpad.net/percona-innodb-recovery-tool
回答by kpower
UPDATED
更新
First of all, about the files:
首先，关于文件：
- .frm - table structure;
- .myd - table data;
- .myi - indexes.
- .frm - 表结构；
- .myd - 表格数据；
- .myi - 索引。
To recover tables, you can try (make backup first):
要恢复表，您可以尝试（先备份）：
1) run check table tablename- for all db tables;
2) run repair table tablename- for necessary tables.
1) 运行check table tablename- 对于所有数据库表；
2）运行repair table tablename- 必要的表。
UPDATED ONCE AGAIN
再次更新
Another idea... Try this:
另一个想法......试试这个：
- Create a new database to restore and create the tables with same name as .frm files (with the one field - only to create new .frm files);
- Stop mysql service and replace the created .frm files with yours;
- Start mysql service and check.
- 创建一个新的数据库来恢复和创建与 .frm 文件同名的表（只有一个字段 - 仅用于创建新的 .frm 文件）；
- 停止 mysql 服务并将创建的 .frm 文件替换为你的；
- 启动mysql服务并检查。
I expect correct tables (without data, of course). And sorry, for now I have no PC to check, before suggesting...
我期望正确的表格（当然没有数据）。抱歉，在建议之前，我现在没有要检查的 PC...
回答by Sid GM
actually me too was having the same problem with the missing two files. later i found that when the table's type is innodb then the database folder would have only one associated file.
实际上，我也遇到了与丢失两个文件相同的问题。后来我发现当表的类型是 innodb 时，数据库文件夹将只有一个关联文件。
but you can change the table type to myisam to get all three file for the table.
但是您可以将表类型更改为 myisam 以获取表的所有三个文件。
now as per the backup, you can export the database whenever and wherever you want :)
现在根据备份，您可以随时随地导出数据库:)
PHP is GREAT :)
PHP 很棒:)
回答by vikas093
![innodb image][1] INNODB SYSTEM TABLESPACE
![innodb image][1] INNODB 系统表空间
INNODB system tablespace is contain in the mysql data directory---
INNODB系统表空间包含在mysql数据目录中---
INNODB is system tablespace is divde into two parts
INNODB是系统表空间分为两部分
1>.frm it can describe the table format or you can say it is a table *definition*
1>.frm 可以描述表格格式也可以说是表格*定义*
2>.ibd it is contain all system related file and it is also contain data and index and ( InnoDB main table space contain – ibdata1 – and redo logs – ib_logfile*.) ibdata1 contains your InnoDB database and ib_logfile0 and ib_logfile1 are log files for InnoDB.
2>.ibd 它包含所有系统相关文件，它还包含数据和索引和（InnoDB 主表空间包含 – ibdata1 – 和重做日志 – ib_logfile*。）ibdata1 包含您的 InnoDB 数据库和 ib_logfile0 和 ib_logfile1 是日志文件InnoDB。
If you delete your ibdata1 file, then all your InnoDB tables will be lost.
如果删除 ibdata1 文件，则所有 InnoDB 表都将丢失。
By default, InnDB uses a shared "tablespace," which is one or more files from a single logical storage area. All InnoDB tables are stored together within the tabespace (from all the databases). By default, InnoDB creates two 5MB log files in the data directory: iblogfile0 and iblogfile1. The information is logged in circular fashion, with old information at the front of the log being overwritten when the log fills up.. Consequently, a larger log allows InnoDB to run longer without having to force changes recorded in the logs to be applied to the tablespace on disk.
默认情况下，InnDB 使用共享“表空间”，它是来自单个逻辑存储区域的一个或多个文件。所有 InnoDB 表都存储在 tabespace 中（来自所有数据库）。默认情况下，InnoDB 在数据目录中创建两个 5MB 的日志文件：iblogfile0 和 iblogfile1。信息以循环方式记录，当日志填满时，日志前面的旧信息将被覆盖。因此，更大的日志允许 InnoDB 运行更长时间，而不必强制将日志中记录的更改应用于磁盘上的表空间。
