Restoring database from .frm and .ibd files

[KSA]

One of our respected XF member here approached me for help to restore his database after an issue with his host that left him with nothing but messy server files and no backup at all.

after a long read here and there I finally managed to restore his database and here is the approach I took:

get hold of your MySQL database folder which contain .frm and .ibd files

Download MySQL utilities to extract Create Statement from .frm file

mysqlfrm command:

mysqlfrm --server=root pass@127.0.0.1 --port 3307 /path/to/database/my_table.frm --diagnostic

Copy and Query create statement:

I used MySQL Workbench

example create statement of xf_addon

CREATE TABLE `xf_addon` (
  `addon_id` varbinary(50) NOT NULL,
  `title` varchar(75) NOT NULL,
  `version_string` varchar(30) NOT NULL DEFAULT '',
  `version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `json_hash` varbinary(64) NOT NULL DEFAULT '',
  `active` tinyint(3) unsigned NOT NULL,
  `is_legacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_processing` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_pending_action` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`addon_id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2- Execute the following

ALTER TABLE 'MyTable' DISCARD TABLESPACE;

3- Import data

Upload MyTable.ibd to your database folder and run

ALTER TABLE 'MyTable' IMPORT TABLESPACE;

You may come across issue like Innodb Schema Mismatch

in this case you may need to run something like

ALTER TABLE 'MyTable' ROW_FORMAT=compact;

Sadly you have to do it one by one for each table which is tiring, I couldn't find any alternate way to do it.

for MyISAM and Memory tables just move them to the folder directly.

Optimize your database and preform sqldump

There are some fine-tuning steps which is not needed but I find them useful to reduce size for export/restore.

Hope that helps.

 

[gsd104]

Thank You!