Mysql – restore thesql from ibdata/frm files

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.

Your question seems identical to this one on Stack Overflow.
I have reproduced part of the accepted answer here.

The simple solution is to find your saved copy of the CREATE TABLE SQL, run it on a development instance of MySQL, then copy the generated FRM file to the restored instance.

If you don't have access to the CREATE TABLE statements you can try the following:

1. In your restored database, run create table innodb_table_monitor (a int) ENGINE=InnoDB
2. Watch the MySQL server error file until the table monitor data has been dumped (usually about a minute)
3. Run drop table innodb_table_monitor

4. Stop the restored database

5. Write SQL to match the table monitor output, e.g.:

   TABLE: name db/mylosttable, id 0 7872, flags 1, columns 5, indexes 1, appr.rows 1828
   COLUMNS: id: DATA_MYSQL DATA_NOT_NULL len 12; name: type 12 DATA_NOT_NULL len 45;     
   DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; 
   DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
   INDEX: name GEN_CLUST_INDEX, id 0 17508, fields 0/5, uniq 1, type 1
   root page 3, appr.key vals 1828, leaf pages 9, size pages 10
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
   

   can be expressed as:

   drop table if exists mylosttable;
   create table mylosttable (
       id char(12) NOT NULL,
       name varchar(45) NOT NULL
   );
   

   If you are confused about the table monitor output, look at the output for tables with a known schema.

6. Run the above SQL on a development instance of MySQL

7. Copy the FRM files created in the development server to the restored database. You will find them in the MySQL data directory within the subdirectory for the corresponding database.

8. Restart the restored database

   Note you can copy the FRM files from your development system into a live database instance. The reason for stopping the server above is that if you crash the database after making the innodb_table_monitor table it will leave the ibdata file in an inconsistent state, and you'll have to start over from a backup.

9. Test that the tables work using select * statements. If you are wrong you will see:

   ERROR 2013 (HY000): Lost connection to MySQL server during query
   

which means the database has crashed. If this occurs, do create table innodb_table_monitor... on the dev instance and compare the output to the original output from the restored instance. You will likely see you missed a NOT NULL or something small like that.