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
Best Answer
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
Here is a part of the MySQL 5.5 Documentation explaining what needs to be considered
Given these caveats and protocols, here is a suggested course of action
For this example, let's try to restore the
tags
table to themydb
databaseSTEP #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 asCREATE TABLE mydb.tags ...
. Make sure it is the exact same structure as the originaltags.frm
STEP #3
Delete the empty
tags.ibd
using MySQLSTEP #4
Bring in the backup copy of
tags.ibd
STEP #5
Add
tags
table to the InnoDB Data DictionarySTEP 6
Test the table's accessibility
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
Apr 23, 2012
: MySQL: how to restore table stored in a .frm and a .ibd file?Sep 28, 2011
: How to Recover an InnoDB table whose files were moved aroundCAVEAT
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 theCREATE TABLE
statement.