Adventures in Recovering InnoDB Tables from a MySQL/MariaDB Database

A crashed server, a broken database, and a client in need. Sounds like a recipe for an interesting day.

Plan A: Use a Tool

Searching revealed tools such as:

That were several years old. I didn't have much luck using them, and felt like I was being nudged towards their commerical offerings for the up-to-date solution.

I figured I could spin something up to copy things over, and just dump the data. How hard could it be? The 'ol developer's credo definitely kicked in here:

We do these things not because they are easy, but because we thought they were going to be easy.

Plan B: Spin up a Docker Container Using the Same Database Version

I was hoping to just copy down the database files, fire up a docker container using the same version of database that the server is using, and then dump the database. The first step was to locate the database files on the server.

In this case they lived at /var/lib/mysql so I packaged up that whole directory and downloaded it.

tar -czf /var/lib/mysql database.tar.gz

After downloading unpack the directory wherever you'd like:

tar -xz database.tar.gz

Next is figuring out what version of MySQL was running on the server.

mysql --version

Which showed: mysql Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64)

So the server was actually running MariaDB 10.3.39.

Alright, now we can get the docker container up and running locally using that specific version:

docker run -p 3306:3306 \
  --name dbrescue \
  -v /path/to/local/downloaded/database:/var/lib/mysql:Z \
  -d mariadb:10.3.39

You can confirm the container is running as expected using docker ps and you should see your container dbrescue running.

If you don't see it, check the logs for any errors using docker logs -f dbrescue

Now you should be able to hop into your container, and access the database. You'll need the the database username/password used on the server that the database came from.

docker exec -it dbrescue bash

Then in the container open up a database terminal:

mysql -u your_username -p

Enter the password when prompted and you should be in the database terminal. Make sure you can see your database, and tables, that you need to recover:

SHOW DATABASES;
USE DATABASE your_database;
SHOW TABLES;

If things are looking good you can attempt to dump the database.

mysqldump -u your_username -p your_database > database_dump.sql

If that process completes you can switch back to your local terminal (keep the docker container running) and copy the dump:

docker cp dbrescue:/file/path/to/database_dump.sql /local/path/destination/

Hopefully that worked and you've now got your database back up and running. Unfortunately, I was seeing errors when trying to dump the database.

Plan C: Re-Create the Tables and Copy the Data

If Plan B fails you can also manually re-create the database and tables on your new server, then copy over the .ibd files that contain each tables data.

Create a new database
Create the tables from the old database, using the same name and structure
Copy over the .ibd files from the old server to your new one, overwriting the ones that were automatically created when you created the tables

You may be noticing that your old server has .frm and .ibd files for each database, but you're only overwriting the .ibd files. Since both files are automatically created when you create the table in your new database the only missing piece would be the data from the .ibd files.

However, if you can't access the old server database to check what the old table structure was the .frm files contain the table schema.

You can use a script like dbsake to generate the CREATE TABLE statement necessary to re-create the table. Once downloaded you can use the frmdump command on each file to display the SQL:

dbsake frmdump /path/to/foo.frm

Which will output something like:

CREATE TABLE `foo` (
  `name` varchar(64) NOT NULL DEFAULT '',
  `dl` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Note: dbsake is built on Python 2, so I had to set up pyenv to switch my system from version 3 to version 2 to run it.

Plan D: Errors

Again, hopefully that did the trick and you're able to access your data once again. At this point I was seeing errors when trying to start up the database, and seemed like I needed to force InnoDB recovery.

Before modifying this make sure to have a backup though, as it can affect your data.

You can force recovery by adding this line to your option file:

innodb_force_recovery = 1

By default this value is 0 (normal startup without forced recovery). It goes up to 6, and anything above 4 is considered risky as data files can be permanently corrupted (again, make backups before proceeding).

I ended up needing to crank my innodb_force_recovery to 6, do a mysqldump, and then copy everything over to an entirely new database to restore things.

Fingers crossed that one of these restored the data you needed, and let you rescue it.