skip to content
Ruban Selvarajah

Restoring a MySQL DB from /var/lib/mysql with Docker

/ 3 min read

database-management, docker

In a dramatic turn of events, one of our servers was recently decommissioned without warning. It all happened too fast. Before we’ve even said our goodbyes, we’d been assigned a new server, with the old server’s disk temporarily attached to it as a saving grace.

Now this was a development server. So despite my dramatisation of the situation, it wasn’t that critical. It didn’t have any client data, nor did it have any production code. But it did have a bunch of sites with unique configurations and dummy data that allowed us to quickly test new ideas for our product.

So I wasn’t entirely happy with abandoning ship and starting fresh. There had to be a way to restore the valuable sites from the old server!

Let’s 🚀

The Challenge

Restoring the files was a breeze. We had used CWP so creating a new CWP user with the same domain and moving the files into the user’s public_html directory was all it took.

Restoring the DB was tricky though. We didn’t have the luxury of using mysqldump before the old server was nuked. So all we had was a copy of /var/lib/mysql which we grabbed during the grace period.

Operation Phoenix

I knew I could boot up a MariaDB container with my old /var/lib/mysql as a volume map. But I couldn’t fully imagine how I should proceed from there. So out came the checklist:

  1. Identify the DB version
Terminal window
sudo strings /home/ruban/OldData/var/lib/mysql/ib_logfile0 | grep "MariaDB"
# Output: MariaDB 10.6.7
  1. Create a container of the same version

    without mariadb running on startup

Terminal window
docker pull mariadb:10.6.7
docker run --name mariadb-container -v /home/ruban/OldData/var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=TheDragonAdvances -d mariadb:10.6.7 tail -f /dev/null
  1. Boot up mariadb in safe mode

    I forgot the root password (sudoers woe)

Terminal window
docker exec -it mariadb-container bash
mysqld_safe --skip-grant-tables &
  1. Reset the root password
Terminal window
docker exec -it mariadb-container bash
mysql -u root
FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('TheDragonAdvances');
FLUSH PRIVILEGES;
exit
  1. Dump them all

I got ChatGPT to churn out a bash script to dump all my DBs in one go as timestamped SQL files.

So still in the container, I did a vi dump-all.sh && chmod u+x dump-all.sh with the following going into the file:

#!/bin/bash
USER="root"
PASSWORD="TheDragonAdvances"
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
timestamp=$(date +%Y%m%d-%H%M%S)
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > "/var/lib/mysql/dumps/${db}_$timestamp.sql"
    fi
done

Then I just had to run the script like so:

mkdir /var/lib/mysql/dumps && ./dump-all.sh

  1. Secure the salvage
Terminal window
docker cp mariadb-container:/var/lib/mysql/dumps /home/ruban/.
  1. Verify that the operation was a success
CREATE DATABASE ghostwater;
CREATE USER 'northstrider'@'localhost' IDENTIFIED BY 'DragonMonarch';
GRANT ALL ON ghostwater.* TO 'northstrider'@'localhost';
FLUSH PRIVILEGES;
Terminal window
mysql -u root -p ghostwater < /home/ruban/dumps/ghostwater_20240405-133431.sql

And my site works!

Now I can go back to ignoring backup best practices /s