MySQL InnoDB 表丢失且未计入数据库结构
我的 MySQL 表中存在严重问题，曾经有一些 InnoDB 表正在使用中，但现在以某种方式隐藏起来，
看看这个[图片]*链接已删除 - 标题中的表数量为 79 个，实际计数的数字是 74。
这些表是那些正在使用的表
我最近没有任何数据库备份，所以这对我来说是生死游戏
我检查了我的 VPS，我在以下位置找到了它们/etc/lib/mysql/db_name/.
编辑：
我在互联网上搜索，发现每个表应该有 3 个与之相关的文件。
例如，表 table_users 有：
-- table_users.frm
-- table_users.MYD
-- table_users.MYI
对于那些隐藏的表，只有 .frm 文件，而表的其他两个文件丢失。
我应该将问题更改为：如何从 .frm 文件恢复 innodb 表？
I have a serious problem in my MySQL tables , once there were InnoDB tables which were IN USE and now are somehow hidden
look at this [pic] *Link removed - the number of tables in heading is 79 and actual counted number is 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/.
EDIT :
I Searched around internet and I found out that every table should have 3 files related to it.
For example, the table table_users has:
-- 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.
I should change my question to: How to recover a innodb table from a .frm file?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
InnoDB没有这三个文件
InnoDB所有数据库和表的数据都存储在“ibdata1”中。
表定义存储在“tablename.frm”中
我想说你的InnoDB文件已损坏，你可能想看看这些工具：
https://launchpad.net/percona-innodb-recovery-tool
InnoDB does not have those three files
InnoDB data is stored in "ibdata1" for all databases and tables.
the table definition is stored in "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
更新
首先，关于文件：
要恢复表，您可以尝试（先进行备份）：
1) 运行
check table tablename- 对于所有数据库表；2) 运行
repair table tablename- 对于必要的表。再次更新
另一种想法...试试这个：
我期望正确的表格（当然没有数据）。抱歉，在建议之前我暂时没有电脑可以检查......
UPDATED
First of all, about the files:
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.UPDATED ONCE AGAIN
Another idea... Try this:
I expect correct tables (without data, of course). And sorry, for now I have no PC to check, before suggesting...
实际上我也遇到了同样的问题，缺少两个文件。后来我发现，当表的类型是innodb时，数据库文件夹将只有一个关联文件。
但您可以将表类型更改为 myisam 以获取表的所有三个文件。
现在根据备份，您可以随时随地导出数据库:)
PHP 很棒:)
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.
but you can change the table type to myisam to get all three file for the table.
now as per the backup, you can export the database whenever and wherever you want :)
PHP is GREAT :)
![innodb 映像][1] INNODB 系统表空间
INNODB 系统表空间分为两部分
1>.frm
它可以描述表格格式，或者你可以说它是一个表格*定义*
2>.ibd
它包含所有系统相关文件，还包含数据和索引（InnoDB 主表空间包含 - ibdata1 - 和重做日志 - ib_logfile*。）
ibdata1 包含您的 InnoDB 数据库，ib_logfile0 和 ib_logfile1 是 InnoDB 的日志文件。
如果删除 ibdata1 文件，那么所有 InnoDB 表都将丢失。
默认情况下，InnDB 使用共享“表空间”，它是来自单个逻辑存储区域的一个或多个文件。所有 InnoDB 表都一起存储在表空间中（来自所有数据库）。默认情况下，InnoDB 在数据目录中创建两个 5MB 的日志文件：iblogfile0 和 iblogfile1。信息以循环方式记录，当日志填满时，日志前面的旧信息将被覆盖。因此，较大的日志允许 InnoDB 运行更长时间，而不必强制将日志中记录的更改应用于磁盘上的表空间。
![innodb image][1] INNODB SYSTEM TABLESPACE
INNODB is system tablespace is divde into two parts
1>.frm
it can describe the table format or you can say it is a table *definition*
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.
If you delete your ibdata1 file, then all your InnoDB tables will be lost.
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.