Backing up and restoring databases using mariadb-backup

Why backup?

As most people will know it is very important to make sure to keep regular backups as if your server, site or other service goes down you could lose it for good unless you have a working backup.

This means that you shouldn't just be creating backups but you need to test that they actually work and they can be restored when you need them.

How to backup using mariadb-backup

Before you create any backups you are obviously going to need to install the mariadb-backup tool using sudo apt install mariadb-backup.

When I create a backup I like to create a new directory and backup my databases into that. The following command will backup all databases whilst using the root account. If this is not what you want then change the command accordiingly:

sudo mkdir ~/mariadb_backup
sudo mariabackup --backup --target-dir ~/mariadb_backup -u root

If you would like to compress the backup run tar -czvf mariadb_backup.tar.gz ~/mariadb_backup.

Whilst you can perform this manually it is much better to set up a cron job to do it for you. You will need to create a small script to perform the above actions then set up a cron job that runs your script every day (or whenever you wish to create a backup).

How to restore your backup

If you have compressed your backup file then the first job is to uncompress it using tar zxvf mariadb_backup.tar.gz.

Now make sure to stop the MariaDB service if it is currently running. Type sudo systemctl stop mariadb.service to do so.

The following commands will remove the old database, initialise and copy the backup, set the necessary permissions and then restart the MariaDB service.

sudo rm -rf /var/lib/mysql/*
sudo mariabackup --prepare --target-dir ~/mariadb_backup
sudo mariabackup --copy-back --target-dir ~/mariadb_backup
sudo chown -R mysql. /var/lib/mysql
sudo systemctl start mariadb.service

Final thoughts

You should now have learnt how to backup and restore your databases using mariadb-backup. It is a simple tool but it will become vitally important to anyone who needs to keep systems running when the worst happens.

This post only explains how to backup and restore databases. It will be much safer if the backup is stored on another server. It is not good practice to store the backup on the same server that you are backing up. Future articles will explain how to save your backups elsewhere.