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:
so I created another database called
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:
you created a new empty database called
this is time to restoring your old data, please follow next steps for each one of your tables:
- discarding new data file from its structure by executing
following command on the mysql where has access to your new
ALTER TABLE my_table DISCARD TABLESPACE;
- 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
- change the ownership of the ids file which recently copied in the
new database should be changed to
sudo chown mysql:mysql /var/lib/mysql/new_db/my_table.ibd
- finally, you can execute this command where you executed the previous one:
ALTER TABLE my_table IMPORT TABLESPACE;
it works for me.