Ending 2020 by losing all your MySQL data seems about right doesn’t it? I spent a full day recovering my data after updating to the latest Docker version on a NAS. It nuked my MySQL container in the process. Luckily, I had some database files but they were in .frm and .ibd format which are not easy to import, especially once the server is gone. In this article I’ll show you how to restore your MySQL data on MacOS if you only have .frm and .ibd files available. Hopefully, this will save you some time if you are in the same position I found myself in.
A brief overview of the files we have and what we’ll be doing to salvage the data:
Restore Process High-Level:
Let’s Get Started
Install MySQL shell client:
Add it to your path:
Reload your shell
Let’s begin by starting up a fresh docker instance that we’ll use to restore our database.
Create config and data volumes. The config volume will hold additional MySQL configuration and the data volume will be where our data goes on disk.
Add the following to a ~/docker/mysql/conf.d/config.cnf
Run a MySQL docker instance
Step 1: Create Files to Reconstruct Table Structure
There are two ways to reconstruct the tables. The first option is to use MySQL command line utilities and the mysqlfrm tool. The other option is to create our tables with a dummy structure using some command line magic and then replace the dummy .frm’s in our data directory with the actual .frms. I’ve used both but prefer the former, I’ll show you how to do both.
Step 1.1: Use MySQL’s command line utilities
The Python MySQL connector is required. Download it from here. Download MySQL Utilities source code by selecting “Source Code” from “Select Platform”. I downloaded the Windows distribution and unzipped it (running on a mac) From a terminal:
Use mysqlfrm to generate table creates
Set the ROW_FORMAT to compact
Step 2 - Generate dummy tables creates using .frm files
Even though we’re going to use the output from Step 1, we will use the files generated in this step to reset the tablespace and import so go ahead and follow along. This is useful when you have many tables to restore.
First we’ll get the table names from our .frm files
Prepend create table in front of each table name
Replace .frm and add a dummy int column with engine innodb
At this point, you should have three files: recoverme-table-structures.sql, tables.sql, rtables.sql.
Step 3 - Create Our Tables
First let’s create our database
Next, run sql
If you were to run rtables.sql instead of recoverme-table-structures.sql, you would have your tables added with a dummy id column. At that point, you would stop mysql and then copy over your .frm files into the mysql database directory, replacing the existing .frm files. After that you would run
Step 4 - Replace Data
Now the spot we’re in is that our internal id of our tables is different than the one in our .ibd files. We’ll manually sync our tablespaces.
Create remove-tablespace.sql to remove the existing internal ids.
Create sync-tablespace.sql to import tablespace from existing data.
Remove any existing .ibd files in our MySQL directory
Copy .ibd files over to MySQL directory
If you get error - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.). Restart the process and sed ROW_FORMAT=DYNAMIC instead of ROW_FORMAT=COMPACT
Dump our database to create a backup
Now we have a legitimate MySQL dump of our data. We can import that in with the correct database name
That’s it! There are certainly more issues that can come up if the ibd files are corrupted. I encourage you to check out the Twindb github for some excellent tools on repairing ibd files and general data recovery practices.
How do I load a file into MySQL?
Apart from the LOAD DATA statement to load data from file to table in MySQL, you can use the INSERT statement to insert new rows into an existing table. You can use the INSERT statement to load data from file to table in MySQL in the following forms: INSERT… VALUES. INSERT…VALUES ROWS()
How we can open IBD file?
You can open an IBD file in MySQL (cross-platform) if you are using the InnoDB database engine. For example, you can use a backup IBD file to restore the table it contains if your current copy of the table has become corrupted or been deleted.
How to import tablespace in MySQL?
mysql> USE test; mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4; In the / datadir /test directory, there is a tablespace . ibd file for each of the four partitions. On the destination instance, discard the partitions that you intend to import from the source instance.
What is .IBD file in MySQL?
An IBD file is a MySQL table created by the InnoDB database engine. It contains a table-specific tablespace and index data. IBD files are created when MySQL's innodb_file_per_table option is enabled, which it is by default.