Restoring databases using only .frm, .ibd files

Question

Today I had the pleasure of losing all my databases.
Luckily I had the data folder still intact and with all .frm and .ibd files.
Googling for a few hours I came up with a solution that helped me recreate the .sql file, and table structure, and then import the .ibd file.

The biggest helper with all of this was mysqlfrm, which I found to be discontinued. Not sure I would have been able to do this without it.

This was my process:

Export tables to .sql file

mysqlfrm --server=root:root@127.0.0.1 ./*.frm  --port 3301 > ./dbtables.sql

Get all create table commands for easier discard/import tables. This was used with the dbtables.sql file to get all instances of create table so it would be easier to find and replace ALTER TABLE mytable DISCARD TABLESPACE/IMPORT TABLESPACE.

^[CREATETABLE].*+$

Discard .ibd files command:

ALTER TABLE mytable DISCARD TABLESPACE;

After discarding, copy the .ibd files from you database folder into the "live" version.

Import .ibd files command:

ALTER TABLE wp_actionscheduler_actions IMPORT TABLESPACE;

Do you know of a modern way to restore a DB using just .frm, .ibd files?

Google and Stack Overflow mostly point to old ways/tools that don't work anymore.

A solution for any OS would be great as I have access to all so can test them without issues, hopefully.

Does this answer your question? restore table from .frm and .ibd file? — mustaccio, Sep 01 '22 at 16:01
Caution: Any solution that worked before MySQL 8.0 may not work for 8.0. This is due to the new "data dictionary" and what it stores on disk in case of disaster. — Rick James, Sep 01 '22 at 18:36
Right, there are no .frm files in MySQL 8.0, so there's no tool to read them. The recommended solution is to keep current backups of your schema and database (that was a better solution pre-8.0 too). — Bill Karwin, Sep 01 '22 at 18:41

score 2 · Accepted Answer · answered Sep 02 '22 at 09:49

Whilst there might be a way to do this, I would not recommend you look to it as any kind of "Recovery" Strategy.

Do you know of a modern way to restore a DB using just .frm, .ibd files?

No.

Do you know of a modern way to restore a DB ... ?

Yes.
From the Backups that you take expressly for this purpose.

Trying to "fix" your database's files after an incident like this is like trying to start your car and drive it away after crashing it into a brick wall at high speed... sure; it might work, if you're very, very [un]lucky, but you have absolutely no idea what other dangers might be lurking under the hood.

Get your database back to a Known, Good State (your last backup) and move forward from there.

It's a Truth that is, all too often, learned the Hard Way, but...

Any Data that you don't have at least two copies of is Data that you don't care about losing ...

Some even suggest going further than that! Have a read about the Three Copies Rule ...

Restoring databases using only .frm, .ibd files

1 Answers1