Mysql – Restoring thesql data from *.IBD file

MySQLrestore

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?

Best Answer

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.

Related Question