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
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.ibdfile 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 to
- 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 ?