MySQL InnoDB lost tables but files exist

PROBLEM

=====================================================================

I have a MySQL InnoDB which has all the database table files, but MySQL doesn't see them, and isn't loading them.

The problem happened because I deleted these three files: ibdata1, ib_logfile0 and ib_logfile1

because I was having issues with mysql starting up, and what I read was to remove them because MySQL will just regenerate them (I know I should have backed them up but didn't).

What can I do to get MySQL to see the tables again?

FIX:

======================================================================

Here is why MySQL cannot see those files: The system tablespace (ibdata1) has a Storage-Engine specific data dictionary that lets InnoDB map out potential table usage:

Moving InnoDB tables from one place to another requires commands like

ALTER TABLE tblname DISCARD TABLESPACE;
ALTER TABLE tblname IMPORT TABLESPACE;

Here is a part of the MySQL 5.5 Documentation explaining what needs to be considered

Portability Considerations for .ibd Files

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name; If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.

Issue this ALTER TABLE statement to delete the current .ibd file:

ALTER TABLE tbl_name DISCARD TABLESPACE; Copy the backup .ibd file to the proper database directory.

Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

ALTER TABLE tbl_name IMPORT TABLESPACE; In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied:

There are no uncommitted modifications by transactions in the .ibd file.

There are no unmerged insert buffer entries in the .ibd file.

Purge has removed all delete-marked index records from the .ibd file.

mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

Given these caveats and protocols, here is a suggested course of action

For this example, let's try to restore the tags table to the mydb database

STEP #1

Make sure you have backups of those .frm and .ibd files in /tmp/innodb_data

STEP #2

Get the CREATE TABLE tags statement and execute it as CREATE TABLE mydb.tags .... Make sure it is the exact same structure as the original tags.frm

STEP #3

Delete the empty tags.ibd using MySQL

ALTER TABLE mydb.tags DISCARD TABLESPACE;

STEP #4

Bring in the backup copy of tags.ibd

cd /var/lib/mysql/mydb
cp /tmp/innodb_data.tags.ibd .
chown mysql:mysql tags.ibd

STEP #5

Add tags table to the InnoDB Data Dictionary

ALTER TABLE mydb.tags IMPORT TABLESPACE;

STEP 6

Test the table's accessibility

SHOW CREATE TABLE mydb.tags\G
SELECT * FROM mydb.tags LIMIT 10;

If you get normal results, congratulations you import an InnoDB table.

STEP 7

In the future, please don't delete ibdata1 and its logs



ALT.

down vote

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.

MySQL Performance: MyISAM vs InnoDB

A major factor in database performance is the storage engine used by the database, and more specifically, its tables. Different storage engines provide better performance in one situation over another. For general use, there are two contenders to be considered. These are MyISAM, which is the default MySQL storage engine, or InnoDB, which is an alternative engine built-in to MySQL intended for high-performance databases. Before we can understand the difference between the two storage engines, we need to understand the term “locking.”

What is locking in MySQL?

To protect the integrity of the data stored within databases, MySQL employs locking. Locking, simply put, means protecting data from being accessed. When a lock is applied, the data cannot be modified except by the query that initiated the lock. Locking is a necessary component to ensure the accuracy of the stored information.  Each storage engine has a different method of locking used. Depending on your data and query practices, one engine can outperform another. In this series, we will look at the two most common types of locking employed by our two storage engines.

Table locking:  The technique of locking an entire table when one or more cells within the table need to be updated or deleted. Table locking is the default method employed by the default storage engine, MyISAM.

Example: MyISAM Table Locking		Column A	Column B	Column C
Query 1 UPDATE	Row 1	Writing	data	data
Query 2 SELECT (Wait)	Row 2	data	data	data
Query 3 UPDATE (Wait)	Row 3	data	data	data
Query 4 SELECT (Wait)	Row 4	data	data	data
Query 5 SELECT (Wait)	Row 5	data	data	data
The example illustrates how a single write operation locks the entire table causing other queries to wait for the UPDATE query finish.

Row-level locking: The act of locking an effective range of rows in a table while one or more cells within the range are modified or deleted. Row-level locking is the method used by the InnoDB storage engine and is intended for high-performance databases.

Example: InnoDB Row-Level Locking		Column A	Column A	Column A
Query 1 UPDATE	Row 1	Writing	data	data
Query 2 SELECT	Row 2	Reading	data	data
Query 3 UPDATE	Row 3	data	Writing	data
Query 4 SELECT	Row 4	Reading	Reading	Reading
Query 5 SELECT	Row 5	Reading	data	Reading
The example shows how using row-level locking allows for multiple queries to run on individual rows by locking only the rows being updated instead of the entire table.

MyISAM vs. InnoDB

By comparing the two storage engines, we get to the crux of the argument between using InnoDB over MyISAM. An application or website that has a frequently used table works exceptionally well using the InnoDB storage engine by resolving table-locking bottlenecks. However, the question of using one over the other is a subjective as neither of them is perfect in all situations. There are strengths and limitations to both storage engines. Intimate knowledge of the database structure and query practices is critical for selecting the best storage engine for your tables.

MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM’s readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table. The more information in the table, the more time it takes InnoDB to figure out which ones are not accessible. If your application relies on huge tables that do not change data frequently, then MyISAM will out-perform InnoDB.  Conversely, InnoDB outperforms MyISAM when data within the table changes frequently. Table changes write data more than reading data per second. In these situations, InnoDB can keep up with large amounts of requests easier than locking the entire table for each one.

MyISAM:

The MyISAM storage engine in MySQL.

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true.
• Full-text indexing. -- InnoDB has it now
• Especially good for read-intensive (select) tables. -- Mostly no longer true.
• Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.

InnoDB:

The InnoDB storage engine in MySQL.

• Support for transactions (giving you support for the ACID property).
• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.

MyISAM Limitations:

• No foreign keys and cascading deletes/updates
• No transactional integrity (ACID compliance)
• No rollback abilities
• 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes.
• Maximum of 64 indexes per table

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED)
• You cannot repair an InnoDB table