Restore MySQL InnoDb from *.frm files

Question

I was working on my Mac when it crashed, the HDD got corrupted and cant be repaired unless formating, so i recovered my DB data from OS X, i got all the folder , and now im trying to restore it in a windows computer while i repair the Mac. I have tried several post here that says tha i just have to copy all the files to the data folder in the new MySQL instalment on windows , i already tried copying the db folder with the frm files and the ind files, then i copied the ibdata1 file and restarted the service but even so it didn't work , the i read that i had to copy also the ib_logfile0 and ib_logfile1 and so i did, but then the service didnt want to run.When i restore the original log it works but i can only see the tables in mysqlworkbench but i can't make querys to them because the log says that the tables doesnt exist even that i can see them.

Im using mysql 5.6 on windows 7 and mysqlworkbench.

Currently i have the *.frm files , the *.ibd files, the ibdata1, the ib_logfiles0 and 1 , that i rescued from my os x hdd, i copied all of them to the windows mysql installment and so the mysql service wont start. Heres the current log when i try to start the service.

 FROM CARLOSDESKTOP.err:

    2014-02-11 13:27:10  7108  Note  Plugin 'FEDERATED' is disabled.

          2014-02-11 13:27:10 3a8 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

    2014-02-11 13:27:10  7108  Note  InnoDB: Using atomics to ref count buffer pool pages

    2014-02-11 13:27:10  7108  Note  InnoDB: The InnoDB memory heap is disabled

    2014-02-11 13:27:10  7108  Note  InnoDB: Mutexes and rw_locks use Windows interlocked functions

    2014-02-11 13:27:10  7108  Note  InnoDB: Compressed tables use zlib 1.2.3

    2014-02-11 13:27:10  7108  Note  InnoDB: Not using CPU crc32 instructions

    2014-02-11 13:27:10  7108  Note  InnoDB: Initializing buffer pool, size = 8.0M

    2014-02-11 13:27:10  7108  Note  InnoDB: Completed initialization of buffer pool

    2014-02-11 13:27:10  7108  Note  InnoDB: Highest supported file format is Barracuda.

2014-02-11 13:27:12 - Server start done.

2014-02-11 13:27:12 - Status check of service 'MySQL56' returned start pending

2014-02-11 13:47:27 - Status check of service 'MySQL56' returned stopped



FROM CARLOSDESKTOP.err:

    2014-02-11 13:27:12  7108  Note  InnoDB: The log sequence numbers 8610473 and 8610473 in ibdata files do not match the log sequence number 13883013 in the ib_logfiles!

    2014-02-11 13:27:12  7108  Note  InnoDB: Database was not shutdown normally!

    2014-02-11 13:27:12  7108  Note  InnoDB: Starting crash recovery.

    2014-02-11 13:27:12  7108  Note  InnoDB: Reading tablespace information from the .ibd files...

    2014-02-11 13:27:13  7108  ERROR  InnoDB: space header page consists of zero bytes in tablespace .\demo\record.ibd (table demo/record)

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size:1024 Pages to analyze:64

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size: 1024, Possible space_id count:0

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size:2048 Pages to analyze:64

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size: 2048, Possible space_id count:0

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size:4096 Pages to analyze:32

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size: 4096, Possible space_id count:0

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size:8192 Pages to analyze:16

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size: 8192, Possible space_id count:0

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size:16384 Pages to analyze:8

    2014-02-11 13:27:13  7108  Note  InnoDB: Page size: 16384, Possible space_id count:0

          InnoDB: Error: could not open single-table tablespace file .\demo\record.ibd

          InnoDB: We do not continue the crash recovery, because the table may become

          InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

          InnoDB: To fix the problem and start mysqld:

          InnoDB: 1) If there is a permission problem in the file and mysqld cannot

          InnoDB: open the file, you should modify the permissions.

          InnoDB: 2) If the table is not needed, or you can restore it from a backup,

          InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

          InnoDB: crash recovery and ignore that table.

          InnoDB: 3) If the file system or the disk is broken, and you cannot remove

          InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

          InnoDB: and force InnoDB to continue crash recovery here.

2014-02-11 13:47:28 - Status check of service 'MySQL56' returned stopped

2014-02-11 13:47:28 - Starting server...

2014-02-11 13:48:29 - Status check of service 'MySQL56' returned start pending

2014-02-11 13:48:29 - Server start done.

2014-02-11 13:48:30 - Status check of service 'MySQL56' returned start pending

Answer 1

Process 1

• Try to install mysql same version, if your crashed mysql version was 5.6 please install mysql 5.6. brew install mysql@5.6
• If following project fail try same things with mariadb. (In my case, by mariadb, I get back all database)
• Before star, must stop mysql or mariadb. Use this command brew services stop mysql or brew services stop mariadb

• ibdata1 file copy from backup mysql folder and paste and replace it inside new installed mysql folder's ibdata1 (usr/local/var/mysql) or mamp/xampp mysql folder location.

• ib_logfile0 and ib_logfile1 no need to replace first. If above process don't work, copy this file from backup mysql folder and replace. (In my case, i did not replace)

• Now copy 1 database for test (for example : testdb) from backup mysql folder and paste it inside new installed mysql folder. In testdb must have .frm and .ibd file.

• Restart mysql/mariadb (brew services start mysql) or (brew services start mariadb)

• Login MySQL -> mysql -u root -p
• If everything follow, mysql login successfully.
• Now check database, SHOW DATABASES;
• USE testdb;
• SHOW TABLES;
• SELECT * FROM testdbtable;
• If there don't show any massage, then its successfully restore;

Process 2

• If its not work, you have to install mysqlfrm (https://downloads.mysql.com/archives/utilities/)
• To find database table schema. Open terminal or your command line with your backup mysql folder mysqlfrm --diagnostic /BACKUP/mysql/testdb > /RESTORE/testdb.sql
• Open testdb.sql in notepad/sublime you will see all database table schema but not data just table :( anyway, follow following step carefully.
• Add ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; end of every CREATE TABLE....ENGINE=InnoDB otherwise table may not created.
• Create new database "newtestdb" and run the command >> mysqldump -uroot -p --databases newtestdb > testdb.sql
• It should create all database tables, Now if you check inside "newtestdb" you will see .frm and .ibd file.
• Now again login mysql >> mysql -u root -p
• SHOW DATABASES; USE newtestdb;
• Run this command one by one for all table, ALTER TABLE tablename DISCARD TABLESPACE; if you check newtestbd .ibd file will disapear.
• Now Copy only .ibd file from backup database testdb folder and paste it in newtestdb.
• Now run ALTER TABLE tablename IMPORT TABLESPACE;
• Hope now you will get back your data.

Answer 2

To restore the database from backup (I hope the backup was taken properly) you need at least these files

1. ibdata1 (and ibdata2, ibdataX if they were defined)
2. Database Directory with *.ibd and *.frm files

If you see tables in Workbench then you copied .ibd and .frm files only. ibdata1 looks empty because this is where InnoDB keeps tables dictionary.