1

Yesterday I dumped my MySQL databases to an SQL file and renamed the ibdata1 file. I then recreated it and imported the SQL file and moved the new ibdata1 file to my MySQL data directory, deleting the old one.

I’ve done it before without issue, however this time something is not right. When I examine the (personal, not MySQL config) databases, they are all there, but they are empty… sort of. The data directory still has the .ibd files with the correct content in them and I can view the table list in the databases, but not the tables themselves. (I have file-per-table enabled, and am using InnoDB as default for everything.)

For example with the urls database and its urls table, I can successfully open mysql.exe or phpMyAdmin and use urls;. I can even show tables; to see the expected table, but then when I try to describe urls; or select * from urls;, it complains that the table does not exist (even though it just listed it). (The MySQL Administrator lists the databases, but does not even list the tables, it indicates that the dbs are completely empty.)

The problem now is that I have already deleted the SQL file (and cannot recover it even after scouring my hard-drive). So I am trying to figure out a way to repair these databases/tables. I can’t use the table repair function since it complains that the table does not exist, and I can’t dump them because again, it complains that the tables don’t exist.

Like I’ve said, the data itself is still present in the .ibd files and the table names are present. I just need a way to get MySQL to recognize that the tables exist in the databases (I can find the column names of the tables in question in the ibdata1 file using a hex-editor).

Any idea how I can repair this type of corruption? I don’t mind rolling up my sleeves, digging in, and taking a bunch of steps to fix it.

Thanks a lot.

alt text

2 Answers 2

1

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.

0

Being able to list all tables & database usually means the *.frm files are there. They don't mean there is any data for them. Have you tried to start your mysqld process forcing innodb recovery? If not, try it, of yes, what does the mysql log say when starting?

After that's done: never, ever try to use backups like that again.

3
  • Like I said, both the FRM and IBD files for the databases and their tables are present and fine, and I’m using file-per-table for everything other than the system table. The only thing that’s got a problem is IBDATA1. Yes, I tried forcing it, but that didn’t help. I don’t recall what the log said because that was a long time ago (I’ve made several copies and tried a million things since then, so I can’t find the log entries from that point).
    – Synetech
    Jun 6, 2010 at 14:39
  • 1
    I have no idea what you mean by “using backups like that”. I dumped the data and structures as anyone would, and imported them as anyone would. Like I said, I have done the same thing before and it was fine. I deleted the SQL file because it seemed to be fine like last time, and it probably was for a while after. Something got corrupted later somehow (out of space? crash? ??); it happens.
    – Synetech
    Jun 6, 2010 at 14:43
  • Dumping: ok, Importing: OK. Expecting that a an InnodDB data file can just be transferred ("moved the new ibdata1 file to my MySQL data directory"): error, do not rely on that, ever. And when starting your MySQL eerver you'd probably still get error logs when something is wrong as long as it isn't fixed. Moral of the story: even if MySQL stores it's data in the filesystem, don't expect you can move around anything in there. Sure, a lot of the time it works, but the cases when it doesn't work aren't worth it.
    – Wrikken
    Jun 6, 2010 at 15:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .