Mysql – Huge #sql-xxxx_xxxx.ibd files in thesql-data folder

Well I tried a bunch of stuff and researched a bunch of commands, switches, and structures (not surprisingly, the MySQL docs were most helpful, if extensive—needle/haystack). Eventually some of my tricks worked (it turns out that others had thought of the same tricks, though I did not see the two major parts—recovering the table structure, and recovering the data—in one place, so I’m posting them together here).

What I had to do was to recreate the IBDATA1 file. Unfortunately while running the daemon detects the databases (the directories), it does not pick up the tables Innodb inside (the IBD/FRM files). So what I did was to:

1. Empty out the data directory (or move the original and create an empty one)
2. Run the daemon, let it create a new, empty IBDATA1 file
3. Import the system tables using the SQL scripts in …\MySQL\share
4. Create a dummy database and table of the same name
5. Copy over the original FRM file
6. Use either DESCRIBE or better, SHOW TABLE CREATE to extract the table structure
7. Next, I used DISCARD TABLESPACE on the table
8. Copied over the original IBD file
9. Then I used IMPORT TABLESPACE
10. Finally, I re-ran the daemon with innodb-force-recovery=6
11. And I ran mysqldump to extract the structures and data

Of course it was not always smooth. Some tables were fine, but some required dropping the table and database after the SHOW TABLE CREATE, and using that to re-create the table before trying to import the data. Others did not even work that far, and I had to manually get the comments and names of the columns from the FRM file using a hex-editor (though figuring out what the data types and attributes, keys, etc. were was a crap-shoot). Also, there were plenty of—too many—daemon and client restarts.

(I’m still looking for a tool that will directly parse FRM/IBD files (or at least display table structure from an FRM file), but it looks like nobody has bothered to “reverse-engineer” them even though it’s open-source and the file formats are publicly available. It seems that everyone is complacent using the official MySQL tools—thus creating a great opportunity for data-recovery firms and proprietary/commercial tools.)

The key was to always work with the absolute minimum (eg only the MYSQL directory—ie system tables). Unfortunately while it meant that things would be simplified and easier to work with, it also meant recovering one table at a time—which wasn’t a big deal for me, but for some people it could be.

Anyway, out of the many MySQL recovery pages on the Internet that I saw during the past couple of days, a small handful were pretty useful, and I’ll add them once I scour my history to dig them up.


Hope this can help others in a similar situation.

You should look at your my.conf ( /etc/mysql/my.conf by default), note that .sql are text files, which could be larger than the binary form of the database sometimes - depends on the indexes and structure of the SQL dump (numbers are much more efficient in binary form than dumped for example). If you have phpmyadmin, you can see the configuration variables along with the file setup there for example. Otherwise "lsof" should give you the list of open files and you may check where the MySQL writes (could be better seen through "iotop" also).

.idb files are not mandatory - they are created by table optimization/setup, for example by "mysqloptimize" command in ubuntu ...