Mysql – Restore MySQL Databases from File backups, not MySQL Export

You need to do a mysqldump of everything !!!

With regard to the error message, you have what I call a pidgeon hole. It is essentially a table's metadata that got corrupted in ibdata1. There is no way to erase it. You cannot drop the table the metadata is looking for because the corresponding data outside ibdata1 cannot be referenced via its inode. Sometimes, even mysqldumps won't work when it hits the table entry via the .frm.

From another perspective, the metadata contained in ibdata1 is Lunix-ish and inode centric, which are concepts foreign to FAT-based Windows. I would not trust InnoDB metadata built this way. Doing a mysqldump gives you logcial data representation via SQL that is both OS and hardware agnostic.

If the datadump is too big, you need to do parallel dumps of the databases or tables and load those mysqldumps into MySQL Windows.

If you are unsure or wary of scripting this, get MAATKIT and use mk-parallel-dump (deprecated tool but good for adhoc dumps) to spit out the data as CSV files. Then, use 'mysqldump --no-data --routines --triggers' and generate table structures file. Run the table structures file in MySQL Windows. Finally, load the CSV into MySQL Windows using LOAD DATA INFILE.

I can confirm it is indeed possible to recover data with only .frm and ibdata1 files. See here: http://www.nerdydork.com/restoring-mysql-innodb-files-on-windows.html. My solution was a little more difficult and my config settings etc were all in different places, but generally that solution worked for me... and saved my life too ;)