MySQL InnoDB tables are missing and not considered in the database structure

Question

I have a serious problem in my MySQL tables, as soon as InnoDB tables appeared that were USED and now somehow hidden

look at this * Link removed - the number of tables in the header is 79, and the actual number calculated is 74.

these tables are those that were in USE

I do not have the latest backup of my database, so for me it is a game of life and death.

I checked my VPS, I found them in / etc / lib / mysql / db _name /.

EDIT:

I searched the internet and I found out that in each table there should be 3 files associated with it.

For example, the table table_users has:

  -- table_users.frm -- table_users.MYD -- table_users.MYI 

and for a hidden table there are only .frm files and two other table files.

I have to change my question to: How to restore innodb table from .frm file?

Answer 1

InnoDB does not have these 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 can take a look at these tools: https://launchpad.net/percona-innodb-recovery-tool

Answer 2

UPDATED

First of all, about files:

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

To restore tables, you can try (backup first):

1) run check table tablename - for all db tables.
2) run repair table tablename - for the necessary tables.

---

UPDATED ONE AGAIN

Another idea ... Try the following:

• Create a new database to restore and create tables with the same name as .frm files (with one field - only to create new .frm files);
• Stop the mysql service and replace the created .frm files with yours;
• Start the mysql service and check.

I expect the correct tables (no data, of course). And I'm sorry, at the moment I do not have a PC to check before offering ...

Answer 3

in fact, I also had the same problem with the missing two files. I later discovered that when the table type is innodb, then there will be only one linked file in the database folder.

but you can change the table type to myisam to get all three files for the table.

Now according to the backup you can export the database whenever and wherever you want :)

PHP is BIG :)

Answer 4

! [innodb image] [1] INNODB SYSTEM TABLES

The INNODB system tablespace is contained in the mysql data directory ---

INNODB is a system tablespace divided into two parts

1> .frm it can describe the table format, or you can say that this is a table definition *

2> .ibd it contains the entire file associated with the system, and also contains data and index and (the main space of the InnoDB table contains ibdata1 and redo logs ib_logfile *.) Ibdata1 contains your InnoDB database, and ib_logfile0 and ib_logfile1 contain log files for InnoDB.

If you delete the ibdata1 file, all your InnoDB tables will be lost.

By default, InnDB uses a common "table space", which is one or more files from the same logical storage area. All InnoDB tables are stored together in a time sheet (from all databases). By default, InnoDB creates two 5MB log files in the data directory: iblogfile0 and iblogfile1. Information is recorded in a circular manner, while old information is recorded when recording a journal when the journal is full. Consequently, a larger log allows InnoDB to work longer without forcing changes recorded in the logs that will be applied to the table space on disk.