Crap, I accidentaly deleted ibdata1..

0. Preamble

Administrating multiple wordpress/websites can be cumbersome at times. While wordpress is pretty complete and provides a lot of features via its plugin system, it can get messy pretty quickly.

One of the main concerns about website administration is the database. There are, at least, two important rules for any serious web admin:

• Always have multiple backups
• If possible, add some redundancy

My setup is pretty straight forward, a VPS running in cluster using docker swarm, and a few containers.
The topic of today is about recovering from an accidental removal of ibdata1 (InnoDB file) when working with MySQL (or other database) using InnoDB engine.

1. How did it happen ?

After a restart of my VPS, the MySQL container refused to startup due to a misalignement of the InnoDB file.
While trying to debug the issue, I acted too fast while I was searching for a potential solution, and, sadly, proceeded to remove the ibdata1 and ib_logfile0 files in MySQL directory WITHOUT A PRIOR BACKUP…
Thankfully for me, MySQL/MariaDB is smart and by default on newer versions it keeps single .frm and .ibd files containing, respectively, the DB tables and their data.

2. Recovering

First and foremost, we’ll use a nifty tool called dbsake which will parse the .frm files in order to recreate the tables structure:

curl -s http://get.dbsake.net > dbsake
chmod u+x dbsake
sudo cp dbsake /usr/local/bin/

Now that dbsake is installed we’ll create a temporary database, which we’ll call recovery in order to recover the old tables

mysql -p -e "CREATE DATABASE recovery;"

While dbsake has a recursive option for parsing a folder containing .frm files, it doesn’t follow the latest MySQL specification and fails parsing some of the tables. We’ll use a for loop to parse the .frm files instead, but you are free to create the missing tables yourself after this step.

for frm in *.frm; do dbsake frmdump ${frm} >> frm-create-tables.sql; done

This should produce most of the CREATE TABLES statements. Now let’s make the DISCARD and IMPORT statements, in order to sync the tables

awk '{if(/CREATE TABLE/) print $3}' frm-create-tables.sql | sed 's/`//g' 's/^/ALTER TABLE /g; s/$/ DISCARD TABLESPACE;/g' > discard-tables.sql

sed 's/DISCARD/IMPORT/g' discard-tables.sql > import-tables.sql

Before importing the tables into the newly created database, we want to fix timestamp statements generated by dbsake.

sed -i 's/timestamp DEFAULT NULL/timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP/g' frm-create-tables.sql

We can now import the tables into the newly created database

mysql -p recovery < frm-create-tables.sql

Once that is done, we’ll discard the tablespaces

mysql -p recovery < discard-tables.sql

We’ll copy the BACKED UP *.ibd files to the new database folder and fix their ownerships

sudo rm -rf /var/lib/mysql/recovery/*.ibd
sudo cp *.ibd /var/lib/mysql/recovery/
sudo chown -R mysql:mysql /var/lib/mysql/recovery/

Let’s sync the newly imported *.ibd files

mysql -p recovery < import-tables.sql

Finally, we can create a sql dump of the recovered tables using the following command

mysqldump -p recovery > recovered-tables.sql

Now everything is prepared and ready to be pulled into the damaged database and complete the recovery.

mysql -p <damaged_database_name> < recovered-tables.sql

The moral of the story is to always create multiple backups and make automated jobs to dump your database.