Extracting MySQL databases from a disk backup, the easy way

A lot of guides online tell you that, if you want to restore a MySQL instance using only a backup of the /var/lib/mysql directory, you should recover/rebuild the entire operating system that MySQL ran on.

It’s true that you should mimic the original setup, but only with regards to the MySQL version and sometimes whether MariaDB or MySQL were used. Once you’ve got that, you can very easily spin the database up again using Docker. Assuming you know the database user+password, you can just run mysqldump as you normally would.

Below are the steps that worked for me.

1. Determine MySQL version

Find the location of the mysql or mariadb binary:

find /thebackup -type f -name mysql -or -name mariadb

I had a rough idea of what the version would be so I ran strings and searched for 8.0:

$ strings /thebackup/usr/bin/mysql | grep '8\.0'
8.0.25
/build/mysql-8.0-eXpnQw/mysql-8.0-8.0.25/sql-common/client.cc
/build/mysql-8.0-eXpnQw/mysql-8.0-8.0.25/sql-common/client_plugin.cc
...

An alternative which may work generically:

strings /thebackup/usr/bin/mysql | grep '^/build'

2. Start the database in Docker

Now that we have the exact version number (8.0.25), we can spin up a Docker container for it:

docker run --rm --name mysql-recovery -v /thebackup/var/lib/mysql:/var/lib/mysql mysql:8.0.25 --skip-grant-tables --user=mysql

Replace mysql:<version> with mariadb:<version> if it’s MariaDB.

Note that we’re mounting the mysql lib directory into the container using -v. If you want to play it safe, make an extra backup of /var/lib/mysql first.

3. Extract data

You can now browse or dump the data using command-line tools:

docker exec -it mysql-recovery mysql
docker exec mysql-recovery mysqldump mydatabase > mydatabase-backup.sql

Tags: , , , | August 30th, 2022 | Posted in Uncategorized

Comments are closed.