MySQL InnoDB lost tables but files exist
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
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 around
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.
I have same situation, Can not drop or create specific tblname. My fix procedure is:
Stop MySQL.
service mysql stop
Remove ib_logfile0 and ib_logfile1.
cd /var/lib/mysql; rm ib_logfile0 ib_logfile1
Remove tblname files. WARNING: THIS WILL PERMANTENTLY DELETE YOUR DATA
cd /var/lib/mysql/dbname; rm tblname*
Start MySQL.
service mysql start
I had this problem too. I deleted ibdata1
accidentally and all my data was lost.
After 1-2 day search in the google and SO, finally I found a solution that it saved my life (I had so many databases and tables with huge records).
take a backup from
/var/lib/mysql
recover table schema from
.frm
file with dbsake (there was another option! mysqlfrm. but it didn't work for me)
dbsake frmdump --type-codes /var/lib/mysql/database-name/tbl.frm
create new table (with new name) with exported schema.
discard new table data with this command:
ALTER TABLE `tbl-new` DISCARD TABLESPACE;
- copy old table's data and paste it instead of new one and set right permission for it.
cp tbl.ibd [email protected] && chown mysql:mysql [email protected]
- import data to new table.
ALTER TABLE `tbl-new` IMPORT TABLESPACE;
- alright! we have data in new table and we can drop old one.
DROP TABLE `tbl`;
- check
/var/lib/mysql/database-name
and if there is data (.ibd
file) for old table, delete it.
rm tbl.ibd
- and finally rename new table to original name
ALTER TABLE `tbl-new` RENAME `tbl`;