Backup and restore MariaDB/MySQL databases

Every database running a site should have backup system. We will explore how to backup and restore MariaDB/MySQL databases in this article.

Taking backup of data is very important as many server experts would stress on it. Data could be website files, stored files of different kinds for office and personal use and databases. Databases are the backbone of most of the websites for data manipulation. Taking a regular backup is the key to avoid damages in case something goes south.

Backup database

This article is not about control panels or shared hosting, I assume you have a VPS or dedicated server to run the commands which I will be explaining below.

Points to take note of:

• Database user, I used root. Change accordingly.
• Backup location, I used /home/user/backups/. Change accordingly.
• Everything in bold should be changed.

By running the following command you can quickly backup your database.

mysqldump -u root -p DATABASE > /home/user/backups/db-bak.sql

That’s fine but what about running a daily backup job instead? Good question, we can use cron job to do just that. This job will take backup of the database on daily basis at 6:30AM to the same location overwriting the old backup.

30 06 * * * mysqldump -u root -p'PASSWORD' DATABASE > /home/user/backups/db-bak.sql

But I want backups by date? Sure you can add date function to the command to take daily backups separately without overwrite.

30 06 * * * mysqldump -u root -p'PASSWORD' DATABASE > /home/user/backups/db-bak-`date +%Y-%m-%d_%H-%M`.sql

Backup script

These are all fine, but why not let’s go a little further and make a bash script run by cron?

Create a file backup-db.sh in let’s say /opt/, make it executable by chmod +x /opt/backup-db.sh. Paste the following in it:

#!/bin/sh
/usr/bin/mysqldump -u root -p'PASSWORD' DATABASE > /home/user/backups/db-bak-`date +%Y-%m-%d_%H-%M`.sql

If you want to backup all the databases owned by root user, change DATABASE to --all-databases.

Run above script by cron:

30 06 * * * /opt/backup-db.sh

Delete older files

If you go with the approach of backups by date, you will soon find out that you are running out of space if you have little volume.

We can improve above bash script by letting it remove files older than 7 days, you can change to any number you would like.

Note: when it comes to rm command always use it with care. Test it on your local to make sure it do what it should.

find /home/user/backups -type f -mtime +7 -exec rm {} +

Final script would be:

#!/bin/sh
find /home/user/backups -type f -mtime +7 -exec rm {} +
/usr/bin/mysqldump -u root -p'PASSWORD' DATABASE > /home/user/backups/db-bak-`date +%Y-%m-%d_%H-%M`.sql

Download backup files

You can use scp or rsync to download files from your server to your local machine. Use rsync if you are worried about the bandwidth consumption.This can be applied to remote backups too.

scp

1- Download using private key

scp -i /backups/servers/keys/my-server -rp USER@IP-ADDRESS:/home/user/backups/*.sql /backups/servers/backups/

2- Download using password

scp -rp USER@IP-ADDRESS:/home/user/backups/*.sql /backups/servers/backups/ 

rsync

1- Download using private key

rsync -r -avu -e "ssh -i /backups/servers/keys/my-server" --progress USER@IP-ADDRESS:/home/user/backups/* /backups/servers/backups/

2- Download using password

rsync -r -avu --progress USER@IP-ADDRESS:/home/user/backups/* /backups/servers/backups/

Restore database

Restore is the easiest part in all these, if your backup file is not on the same server, you can upload it using scp/rsync.

Upload example with rsync

rsync -r -avu -e "ssh -i /backups/servers/keys/my-server" --progress /backups/servers/backups/db-bak.sql USER@IP-ADDRESS:/home/user/backups

Login to your server via ssh and then login to the database server. We will create database 1st in case it’s not there yet.

create database DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Exit from database server and run this in the terminal:

mysql -u root -p DATABASE < /home/user/backups/db-bak.sql