Today we are going to learn how we can recover a database for which we don’t have a .sql backup copy, but we do have access to its MySQL folder or directory. This can happen when the operating system doesn’t boot up, but we still have access to the hard drive.
The first thing we need to do is to access the MySQL directory usually located at:
/var/lib/mysql
In my case, since I use an external hard drive mounted on my unit, it’s named:
mnt/xvdb/var/lib/mysql
Once we have the address, we download the directory via FTP to have it on our system or copy it to the main drive.
Now it’s very important to determine the previous version of MySQL or MariaDB. If we don’t know it and we know it was installed on this drive, we do the following:
/mnt/xvdb/usr/bin/mysql
/mnt/xvdb/usr/bin/mysql --version
sudo apt-get install libncurses5
Now we have several ways to access the database again.
You have two options:
We can try to access it directly from the executable we now have access to:
/mnt/xvdb/usr/bin/mysql -h localhost -u root -ppass database_name --default-character-set=utf8mb4
In my case, I use this command. It’s very important to know the previous username and password if we want to recover the data.
This command didn’t work for me because being on another machine, it couldn’t find the correct path. We can copy the previous mysql folder to the path:
/usr/bin/mysql
And it should let us access.
Using this command, we can perform the backup:
/mnt/xvdb/usr/bin/mysql -u root -p database_name < database_backup.sql
Remember to use root or the user associated with the database, put the database name database_name correctly, and put the previous password correctly.
2. We can use a Docker image
In this case, we’ll create a Docker image with the version returned to us in the previous step:
In my case:
mariadb:10.1.48
Then I create my Docker Compose container
And we place our mysql folder in the root and create a folder called backup:
Now, with this configuration, we start our docker-compose:
docker compose up -d
Now we access the container console:
docker exec -it mariadb /bin/bash
Once inside, we go to the backup folder:
cd backup
And run:
mysql -u root -p database_name < database_backup.sql
In case of MariaDB:
mysql -u root -p database_name < database_backup.sql
Remember to use root or the user associated with the database, enter the database name database_name correctly, and enter the previous password correctly.
Now we will save our backup in the .sql file.
