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?

about_member.frm                              site_stories.frm
about_member.ibd                              site_stories.ibd
db.opt                                        stories.frm
FTS_00000000000000bb_BEING_DELETED_CACHE.ibd  stories.ibd
FTS_00000000000000bb_BEING_DELETED.ibd        story_comments.frm
FTS_00000000000000bb_CONFIG.ibd               story_comments.ibd
FTS_00000000000000bb_DELETED_CACHE.ibd        story_likes.frm
FTS_00000000000000bb_DELETED.ibd              story_likes.ibd
FTS_00000000000000f5_BEING_DELETED_CACHE.ibd  story_tags.frm
FTS_00000000000000f5_BEING_DELETED.ibd        story_tags.ibd
FTS_00000000000000f5_CONFIG.ibd               story_views.frm
FTS_00000000000000f5_DELETED_CACHE.ibd        story_views.ibd
FTS_00000000000000f5_DELETED.ibd              story_view_totals.frm
member_favorites.frm                          story_view_totals.ibd
member_favorites.ibd                          tags.frm
members.frm                                   tags.ibd
members.ibd
  • Have you tried to restore those files? The log files can stay deleted. You really shouldn't have deleted ibdata1 – Ramhound Nov 13 '13 at 15:00
  • I have copied the file from from an old mysql version where the file was located but the tables don't show up. – Get Off My Lawn Nov 13 '13 at 15:13
up vote 31 down vote accepted

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:

InnoDB Architecture

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

Give it a Try !!!

I have discussed things like this before

CAVEAT

What if you do not know the table structure of the tags ?

There are tools to get the CREATE TABLE statement just using the .frm file. I wrote a post about this as well : How can extract the table schema from just the .frm file?. In that post, I copied a .frm file to a Windows machine from a Linux box, ran the Windows tool and got the CREATE TABLE statement.

  • Thanks for the awesome Answer! I am still in the process of getting one table imported, because I keep creating problems, but I will get there eventually, and I will let you know how it works out! Thanks! – Get Off My Lawn Nov 15 '13 at 3:49
  • 1
    When I run the create I get: ERROR 1813 (HY000): Tablespace for table 'weblyize.tags' exists. Please DISCARD the tablespace before IMPORT. So then I try to run the alter tablespace first, and get this error: ERROR 1146 (42S02): Table 'weblyize.tags' doesn't exist. What can I do? – Get Off My Lawn Nov 16 '13 at 14:36
  • Thanks! To fix my error I created a new database, ran CREATE TABLE ... then followed your steps! You saved me from having to rewrite them 100% from scratch! It didn't import foreign keys but that is okay I can do that myself! Again Thanks! – Get Off My Lawn Nov 16 '13 at 15:54
  • What if I have 100 tables which should be fixed this way. I won't do operations for each table by hands. How it could be automated ? – Oleg Abrazhaev Nov 29 '16 at 12:11
  • thanks these Discard/Import tablespace commands saved my data! – Xipan Xiao Jul 3 at 5:39

I have same situation, Can not drop or create specific tblname. My fix procedure is:

  1. Stop MySQL.

    service mysql stop
    
  2. Remove ib_logfile0 and ib_logfile1.

    cd /var/lib/mysql;
    rm ib_logfile0 ib_logfile1
    
  3. Remove tblname files. WARNING: THIS WILL PERMANTENTLY DELETE YOUR DATA

    cd /var/lib/mysql/dbname;
    rm tblname*
    
  4. Start MySQL.

    service mysql start
    
  • 1
    Thank got this fixed my problem, I didn't do step 3, I simply removed the logfiles and started mysql back up. – Jeff Wilbert Aug 10 '15 at 15:50
  • You are absolutely fantastic! Solved my issue. – Alex G.P. Jun 1 '17 at 12:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.