Recovering a MySQL or MariaDB database without a backup but accessing the mysql folder or directory

by
Tiempo de lectura: 2 minutos

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 directory, we download it via FTP to have it on our system or copy it to the main hard drive.

Now it’s very important to obtain the previous version of MySQL or MariaDB. If we don’t know it and we know it was installed on this hard drive, we do the following:

  1. We look for the previous MySQL installation directory (usually in usr/bin/mysql), in my case:
/mnt/xvdb/usr/bin/mysql
  • Now we execute the version command but using that executable:
/mnt/xvdb/usr/bin/mysql --version
  • It may ask us to install this library:
sudo apt-get install libncurses5
  • Now if it will let us run the version command:

Now we have several ways to access the database again.

We have two options:

1. We can try to access directly from the executable to which we now have access:

/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

version: "3.1"
services:
  mariadb:
    image: mariadb:10.1.48
    restart: unless-stopped
    container_name: mariadb
    volumes:
      - ./mysql:/var/lib/mysql
      - ./backup:/backup

And we place our mysql folder in the root and create a folder called backup:

Directories:
   -docker-compose.yml
   -mysql
   -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 we run:

mysql -u root -p database_name < database_backup.sql

In case of MariaDB:

mariadb -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.

And now we’ll have our backup saved in the .sql file.

Post Views: 6

Leave a Comment