1

Unfortunately I have recently lost my VPS and the provider restore it after 1 week, then the only things that I had was the raw files on my server which were accessible in recovery mode.

so by moving those files from the server to my local machine and the other new server which I have bought, I need to recover those IBD files to the MySQL.

What I have to do with these *.IBD files in order to have my data on a live mysql service again?

2

In this case, I wanted to restore my data on another database rather than the live one.

First of all you should take all of your old data from the following location on your old server and move them to your new VPS.
 old destination would be:

/var/lib/mysql/your_db_name

so I created another database called new_db
 then start to creating the tables by the CREATE TABLE statement that I had from my live database, maybe you can find them in your codes.

by doing these, you will have all your tables but empty ones.

let’s assume that you placed the old data files in the:

/location/to/old_files/

and you created a new empty database called new_db.

this is time to restoring your old data, please follow next steps for each one of your tables:

  1. discarding new data file from its structure by executing following command on the mysql where has access to your new database new_db:
    ALTER TABLE my_table DISCARD TABLESPACE;
    
  2. coping the old data files to the same table structure but in the new database location:
CP /location/to/old_files/my_table.ibd /var/lib/mysql/new_db/my_table.ibd
  1. change the ownership of the ids file which recently copied in the new database should be changed to mysql:mysql:
sudo chown mysql:mysql /var/lib/mysql/new_db/my_table.ibd
  1. finally, you can execute this command where you executed the previous one:
ALTER TABLE my_table IMPORT TABLESPACE;


it works for me.

1
  • When I first tried to import the table I got this error: #1808 - Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format). So I added ROW_FORMAT=COMPACT DEFAULT to the CREATE TABLE script. Now I have another error: ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.) Have no idea how to fix this. – JConstantine May 19 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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