Recreate mysql database from frm files and idb

Recently, I made a huge mistake on my server and ended up with a failed install and no backups.
I basically had plesk with several wordpress sites and by chance, I still had access to my data, even though it was not useable anymore.

So here is how I did to recover my files and be able to get back a .sql file with structure and data.

First, retrieve data

So all my mysql data is lying on my server in /var/lib/mysql and it's organized in

• ibdata1 (one important file to keep)
• one folder per database name
• inside each of this folder, .frm and .idb files

So let's rappatriate everything on my computer using scp

mkdir mysql
scp -r root@<ip>:/var/lib/mysql/* .

Then, retrieve mysql version

In order to be able to recreate the databases, we need to use the same version of mysql/mariadb

For this, launch cat /var/lib/mysql/mysql_upgrade_info on the server.

I got something like 10.1.48-MariaDB.

Launch mysql/mariadb on local

As we know the exact version we need, we will now use docker to launch a mysql server of exactly this version.
So on your local, run:

docker pull mariadb:10.1.48
docker run -p 127.0.0.1:3307:3306 --name mariadbtest -e MYSQL_ROOT_PASSWORD=Password123! -d mariadb:10.1.48

For this, we launch docker on port 3307 just in case another mysql server is already running.

Now, we can login to the database by using our favorite mySQL explorer, for me, it's Sequel Pro

You can connect to the database using

• Host: 127.0.0.1
• username: root
• password: Password123!
• port: 3307

🎉 you got now the same version of mysql you used to have on your server.

Copy your old databases to the docker machine

So now, let's move each database inside docker to be able to access it:

docker cp dbname/ mariadbtest:/var/lib/mysql

dbname begin the folder holding the .frm and .idb files.

From there, if you refresh your databases in Sequel Pro, you should see your database but an error occurs when you try to open any table.

Table 'dbname.mod844_icl_string_status' doesn't exist for example

Fix errors

In order to fix this, you need to copy the previous file we backed up:

docker cp ibdata1 mariadbtest:/var/lib/mysql

After that, restart your docker machine and you should be able to access your data using Sequel Pro.

Export your data

Now you can safely export your data into a .sql file and think about always setting up a backup system from now on!

Cheers

My name is Martin Ratinaud and I am a full stack developer

I run a staking crypto comparator to help you find the best rates for any crypto.

And as a remote worker since 2016, I also want you to discover your sweet spot based on your hobbies, so that you can expatriate and live your best life.