CODEWITHSUNDEEP
mysqlinnodb
Mac Taylor
Mac Taylor

Reputation: 5148

MySQL InnoDB tables are missing and not counted in database structure

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?

Upvotes: 6

Views: 30401

Answers (4)

vikas093
vikas093

Reputation: 21

![innodb image][1] INNODB SYSTEM TABLESPACE

INNODB system tablespace is contain in the mysql data directory---

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.

Upvotes: 1

Sid GM
Sid GM

Reputation: 11

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 :)

Upvotes: 1

kpower
kpower

Reputation: 3891

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

Upvotes: 1

Geoffrey
Geoffrey

Reputation: 11353

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

Upvotes: 6

Related Questions