[MySQL] Restore from another host by ibd file

Enable innodb_file_per_table = 1 in my.cnf
1.Backup original ibd file

root:/var/lib/mysql/event# cp event.ibd event.ibd.bak

2.Issue this ALTER TABLE statement to delete the current .ibd file:

MariaDB [event]> ALTER TABLE tbl_name DISCARD TABLESPACE;

3.Copy the backup .ibd file to the proper database directory.

root:/var/lib/mysql/event# cp event.ibd.new event.ibd 

4.Beware the permission

root:/var/lib/mysql/event# ll
total 3145228
drwx------  2 mysql mysql       4096 Jun  1 15:26 ./
drwxr-xr-x 22 mysql mysql       4096 May 28 12:00 ../
-rw-rw----  1 mysql mysql         61 Nov 25  2019 db.opt
-rw-rw----  1 mysql mysql       2643 May 31 03:00 event.frm
-rw-r-----  1 root  root  3196059648 Jun  1 12:44 event.ibd
-rw-r-----  1 root  root    24117248 Jun  1 15:23 event.ibd.bak
root:/var/lib/mysql/event# chown mysql:mysql event.ibd

5.Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

MariaDB [event]> ALTER TABLE tbl_name IMPORT TABLESPACE;
0
0 Comments
Inline Feedbacks
View all comments