3

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)

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

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

  3. 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.

  4. 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;.
  5. 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 ?

Improve this question
4
  • there seem to be version mismatch as well try to install the same MySQL version first Commented Mar 6, 2018 at 17:45
  • Is a higher version of MySQL is compatible with the older data, or it has to be exactly the same version ? Commented Mar 6, 2018 at 20:23
  • Installing the same MySQL version solved my problem Commented Mar 7, 2018 at 19:45
  • "Just copying the .frm and .ibd files from one location to another is asking for trouble." (A quote from: restore table from .frm and .ibd file? ) Commented Oct 5 at 15:13

1 Answer 1

Reset to default
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.