4

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 ?

3
  • there seem to be version mismatch as well try to install the same MySQL version first 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 ?
    – πter
    Mar 6, 2018 at 20:23
  • Installing the same MySQL version solved my problem
    – πter
    Mar 7, 2018 at 19:45

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.