Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

Question

For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist.

I've read restore table from .frm and .ibd file? that had similar problem to this, but I don't know how to check if innodb_file_per_table is enabled, and I'm overall just really confused. I also converted a copy of my mysql-bin.000002 file to a txt file so I see that the data from my database is not completely lost.

The database was created last year. I have 6 of those mysql-bin.00000 files, but for some reason the .000002 is the largest. Right now, I have the .ibd and .frm files for all my databases, but I'm at a loss as to how I can restore it back into MySQL, or at least into something I can read.

I'm using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. Also, am I supposed to download a plugin in InnoDB?

Answer 1

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.

1. Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
2. Go into your command/terminal to open the MySQL utility, mysqlfrm, and use it to find the structure of your table that you need to restore. How I did that, was I cd'ed into the file location of mysqlfrm, then entered "mysqlfrm --server=user:pwd@localhost --port=3307 "path_to_.frm_file" > table_name.txt". The .txt file should be saved in the same folder as where your utilities are saved if you didn't specify where you wanted it to go.
3. In the text file, you'll see the CREATE TABLE statements, that include all the columns and info (basically, the original structure) of your table. Copy that CREATE statement with all that info.
4. In your MySQL Command, create a new database (CREATE DATABASE database_name). Make it the same name as your original database name.
5. Create a new table inside the new database--it doesn't have to be the same name as the folder. You can create the new table inside the command prompt, but I created my table in PhpMyAdmin, a free software tool that handles the administration of MySQL over the web. I simply clicked on the database on PhpMyAdmin, then the SQL table, and pasted the table structure from #3. (As a side note, I always received errors if I named my tables "table" in my command prompt, so try to avoid that name).
6. On your MySQL Command, go into your database, and enter "ALTER TABLE table_name DISCARD TABLESPACE", which will essentially remove this table's .ibd file.
7. Copy your original table (the table you want to restore)'s .ibd file into the newly-created table to replace the .ibd file that you just removed. Change your initial .ibd file to the newly created table's name. This will mimic the old .ibd file that you just deleted. You can find this folder in the MySQL data folder, under the newly-created database folder on your computer.
8. Go back to your MySQL Command, go into your database, and enter "ALTER TABLE table_Name IMPORT TABLESPACE." You'll receive a "warning" (1) error-type statement, but just ignore that.
9. And done! if you try to access your new table, it should contain all the data from your old table.

I hope this helped, and let me know if you have any questions or comments! Also, check out http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file for other details.

Answer 2

The main InnoDB data file - usually named ibdata - is essential for you MySQL to be able to understand your .ibd files.

If you need to move data between servers using the binary files, you should stop MySQL cleanly and then move all data files, including the ibdata file(s), between directories.

A more reliable mechanism of moving data between servers on Windows would be to use (mysqldump) or a database export from PHPMyAdmin (or a similar tool).

If binary logging has been enabled the whole time that your server's been running (based on the comments, this may not be the case), you could also use mysqlbinlog to recover every SQL statement you've run on the server from the mysql-bin files, and recreate the database that way. There should be unix timestamps in the mysql-bin files that help you determine how far back they go.

If you've lost your original database files and all you have left are the individual .ibd files, you may have to resort to recovering the data as per akuzminsky's suggestions in the comments.

MySQL 5.6 has some new features for moving InnoDB .ibd data files (transportable tablespaces), but these require some effort and, for a sufficiently small database, it's going to be much easier to transfer data using mysqldump.

Answer 3

_{Wiki answer generated from question comments by akuzminsky}

---

If you see *.ibd files then innodb_file_per_table is ON, otherwise all tables would be in ibdata1.

If it tells a table doesn't exists then the table is missing in InnoDB dictionary. Try to dump all tables into separate sql dumps (one table - one file). Those tables you can't dump you can restore with TwinDB recovery toolkit.

There are no binary packages yet. You have to get source code from GitHub and compile it. See instructions at Recover InnoDB dictionary. It's pretty straightforward:

git clone [email protected]:twindb/undrop-for-innodb.git

and then

make all