Restoring a MySQL (WordPress) database from .ibd Files

I have Apache and MySQL (XAMPP) running on my Windows PC, took backup of the \xampp\mysql\data\ folder and excluding any other files.

Bummer!!!, it simply not matter of pasting files (.frm and .ibd) to \xampp\mysql\data\mydatabase to get the database back.

Since this a WordPress site, I did the installation of WordPress and it created the database tables and now it’s just matter of restoring the data, data is in .ibd files.

First to prevent write operations for the table to be restored.

LOCK TABLES tbl_name WRITE;

Issue this ALTER TABLE statement:

ALTER TABLE tbl_name DISCARD TABLESPACE;

so you have both commands for all the tables like below

LOCK TABLES wp_commentmeta WRITE;
ALTER TABLE wp_commentmeta DISCARD TABLESPACE;
LOCK TABLES wp_comments WRITE;
ALTER TABLE wp_comments DISCARD TABLESPACE;
LOCK TABLES wp_links WRITE;
ALTER TABLE wp_links DISCARD TABLESPACE;
LOCK TABLES wp_options WRITE;
ALTER TABLE wp_options DISCARD TABLESPACE;
LOCK TABLES wp_postmeta WRITE;
ALTER TABLE wp_postmeta DISCARD TABLESPACE;
LOCK TABLES wp_posts WRITE;
ALTER TABLE wp_posts DISCARD TABLESPACE;
LOCK TABLES wp_term_relationships WRITE;
ALTER TABLE wp_term_relationships DISCARD TABLESPACE;
LOCK TABLES wp_term_taxonomy WRITE;
ALTER TABLE wp_term_taxonomy DISCARD TABLESPACE;
LOCK TABLES wp_termmeta WRITE;
ALTER TABLE wp_termmeta DISCARD TABLESPACE;
LOCK TABLES wp_terms WRITE;
ALTER TABLE wp_terms DISCARD TABLESPACE;
LOCK TABLES wp_usermeta WRITE;
ALTER TABLE wp_usermeta DISCARD TABLESPACE;
LOCK TABLES wp_users WRITE;
ALTER TABLE wp_users DISCARD TABLESPACE;

Now copy all the .ibd backup files to newly created database folder, replacing existing files.

Issue this ALTER TABLE statement:

ALTER TABLE tbl_name IMPORT TABLESPACE;

Release the write lock to complete the restore procedure:

UNLOCK TABLES;

so you have both commands for all the tables like below

ALTER TABLE wp_commentmeta IMPORT TABLESPACE;
ALTER TABLE wp_comments IMPORT TABLESPACE;
ALTER TABLE wp_links IMPORT TABLESPACE;
ALTER TABLE wp_options IMPORT TABLESPACE;
ALTER TABLE wp_postmeta IMPORT TABLESPACE;
ALTER TABLE wp_posts IMPORT TABLESPACE;
ALTER TABLE wp_term_relationships IMPORT TABLESPACE;
ALTER TABLE wp_term_taxonomy IMPORT TABLESPACE;
ALTER TABLE wp_termmeta IMPORT TABLESPACE;
ALTER TABLE wp_terms IMPORT TABLESPACE;
ALTER TABLE wp_usermeta IMPORT TABLESPACE;
ALTER TABLE wp_users IMPORT TABLESPACE;
UNLOCK TABLES;

That is it.