Cannot restore MySQL database from .frm and .ibd files

Question

I have a bigger database with around 1000 tables. Unfortunately after a OS X update everything vanished, but I was able to recover all the file that now consists of only .frm and libd files. Following answers from here restore table from .frm and .ibd file? I did the following, test only on one table

1.Installed MAMP

2.Created a new database that has the same name as the old (sunflower)

3.Using mysqlfrm I was able to get a query that created me the table structure( First at once I am trying to recover only one table):

 CREATE TABLE `sunflower`.`USDT@002dNXT_hour` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `datetime` datetime NOT NULL, 
 `open` text, 
 `close` text, 
 `low` text, 
 `high` text, 
 `volume` text, 
 `btc_volume` text, 
PRIMARY KEY `PRIMARY` (`datetime`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB;

Here I encountered with one problem. Running this query it created me a file called : usdt@0040002dnxt_hour.ibd why my old file is usdt@002dnxt_hour.ibd. (Renaming 1 file will be fine, but renaming 1000 is quite complicated)

4. Then I run : ALTER TABLE USDT@002dNXT_hour DISCARD TABLESPACE;

5. Copied the old usdt@002dnxt_hour.ibd file to my database directory.

6. Now I tried to run ALTER TABLE USDT@002dNXT_hour IMPORT TABLESPACE; which complained that such a table does not exits, so I renamed my file to usdt@0040002dnxt_hour.ibd.

7. Running now the same script trows me this error : #1808 - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.) So I changed the row type be running : ALTER TABLE USDT@002dNXT_hour ROW_FORMAT=DYNAMIC;.
8. Now running the import query again throws me this error : Table sunflower.usdt@002dnxt_hour doesn't exist, however the table exits.

Is my approach is good ? If yes, where I make the mistake ?

• Nawaz Sohail: there seem to be version mismatch as well try to install the same MySQL version first
• πter: Is a higher version of MySQL is compatible with the older data, or it has to be exactly the same version ?
• πter: Installing the same MySQL version solved my problem

Answer 1

Overall your method/steps look good.

• Step3: Change you CREATE TABLE statement

  CREATE TABLE sunflower.#mysql50#USDT@002dNXT_hour

This should create the table the way you expect it to be i.e. no 0040 is added to table name.

• Step 6: This shouldn't be required after change to Step3 as mentioned above

Give this a try.