MySQL InnoDB tables are missing and not counted in database structure

Question

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?

Answer 1

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

Answer 2

UPDATED

First of all, about the files:

• .frm - table structure;
• .myd - table data;
• .myi - indexes.

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:

1. 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);
2. Stop mysql service and replace the created .frm files with yours;
3. Start mysql service and check.

I expect correct tables (without data, of course). And sorry, for now I have no PC to check, before suggesting...