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)
Then I run :
ALTER TABLE USDT@002dNXT_hour DISCARD TABLESPACE;
Copied the old
usdt@002dnxt_hour.ibd
file to my database directory.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 tousdt@0040002dnxt_hour.ibd
.- 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;
. - 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 ?