Mysql – Recreating Database from IBD files (no FRM)

MySQL

I am running OS X with mySQL 8.0.22 running natively. A few weeks ago had to reinstall mySQL as I could not get it to start. I made a backup of all the folders under '/usr/local/mysql/data'.

When mySQL was installed again it had wiped all the databases.

The data inside these DB is in no way critical, but I remembered today I was working in a WordPress site with a page builder, hence all info is stored in the DB tables.

I would like to find a way to restore this database, but every article I have read mentions "ibd and frm" files.

There is not a single frm file from the backup I made.

Is there any way of recreating this DB at all or is it a lost cause?

Thanks!

Best Answer

As suggested by @AMtwo, .frm files contain the schema definition. Since these are WordPress tables, getting the schema was very easy as I had lots of installs to get this info from.

I then followed the guide at https://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/ and it worked just fine:

  1. Create table based on the schema of original table.
  2. Running ALTER TABLE product DISCARD TABLESPACE;
  3. Copying the original IBD file
  4. Running ALTER TABLE product IMPORT TABLESPACE;

The tutorial mentions further instructions but at this stage it simply worked for me. I did the same for all other tables, exported as SQL dump and imported successfully.

I would recommend using Docker + mySQL if you are going to be trying this out.

Thanks again @AMtwo, you pointed me in the right direction!

Related Question