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:
- .frm: Contains a single table definition, essentially create table statements
- .ibd: This file holds the data and indexes for a single table on the file system.
Restore Process High-Level:
- Generate create statements from .frm files to get our table structures back
- Reset the tablespace: when an InnoDB table is truncated, or dropped and recreated, it gets a new table ID. Any ID mismatch between the table in the database and the backed-up table can prevent it from being restored. Since we're restoring this on a different MySQL Server instance, we need to drop the tables ids.
- Put our .ibd files into the data directory
- Sync the tablespace: Import new tables ids
- Backup our data so we have full backup which can easily be restored
Let's Get Started
Install MySQL shell client:
brew install mysql-client
Add it to your path:
export PATH="/usr/local/opt/mysql-client/bin:$PATH"
Reload your shell
source ~/.zshrc
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.
mkdir ~/docker/mysql/conf.d
mkdir ~/docker/mysql/data
Add the following to a ~/docker/mysql/conf.d/config.cnf
[mysqld]
innodb_force_recovery = 0
Run a MySQL docker instance
docker run \
--detach \
--name=mysql2 \
--env="MYSQL_ROOT_PASSWORD=mypassword" \
--publish 32780:3306 \
--volume=/pathToMysqlConfig/conf.d:/etc/mysql/conf.d \
--volume=/pathToDataDirectory/mysql2:/var/lib/mysql:rw mysql:5.7
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:
$ cd path/to/unzipped/mysql-utilities-1.5.6
$ python ./setup.py build
$ sudo python ./setup.py install
Use mysqlfrm to generate table creates
mysqlfrm --diagnostic ~/folder/*.frm --quiet > ~/folder/recoverme-table-structures.sql
Set the ROW_FORMAT to compact
sed -i '' -e 's/ENGINE=InnoDB/ ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci/' recoverme-table-structures.sql
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
ls -1 *.frm > tables.sql
Prepend create table in front of each table name
awk '{print "create table " $0}' tables.sql > rtables.sql
Replace .frm and add a dummy int column with engine innodb
sed -i '' -e 's/.frm/ (id int) engine=innodb;/' rtables.sql
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
mysql -uroot -p --host=127.0.0.1 -P32780 -e "create database dbName"
Next, run sql
mysql -uroot -p --host=127.0.0.1 -P32780 dbName < recoverme-table-structures.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 flush tables
.
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.
cp rtables.sql remove_tablespace.sql
sed -i '' -e 's/create/alter/' remove_tablespace.sql
sed -i '' -e 's/ (id int) engine=innodb;/ discard tablespace;/' remove-tablespace.sql
Create sync-tablespace.sql to import tablespace from existing data.
cp rtables.sql sync_tablespace.sql
sed -i '' -e 's/create/alter/' sync_tablespace.sql
sed -i '' -e 's/ (id int) engine=innodb;/ import tablespace;/' sync-tablespace.sql
Run remove-tablespace.sql
mysql -uroot -p --host=127.0.0.1 -P32780 dbName < remove-tablespace.sql
Remove any existing .ibd files in our MySQL directory
cd ~/MySQL/recoverme
rm -f *.ibd
Copy .ibd files over to MySQL directory
cp ~/directoryMyRestoreFilesAre/*.ibd .
Sync tablespace
mysql -uroot -p --host=127.0.0.1 -P32780 dbName < sync-tablespace.sql
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
mysqldump recover > recoverme-structure-data.sql
Now we have a legitimate MySQL dump of our data. We can import that in with the correct database name
mysql -e "create database actualdatabase"
mysql actualdatabase < recoverme-structure-data.sql
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.
References
Dealing with corrupted InnoDB data
Recover Orphan InnoDB Database from .ibd file
SO Recover Table Structure from ibd files
SO Repair/Recover data from MySQL database
Top comments (0)