This post was updated on 9th September 2022

Backups: one of those things nobody thinks about until it’s too late. Even when we are keeping local backups, natural disasters may happen. So, if we really want to keep our data safe, off-site back ups are a must. Moreover, this will free resources in our production server.

Fortunately for those of us who rent dedicated servers at the largest provider worldwide, which happens to be the french OVH, there is a complimentary backup storage included with them which will help us accomplish our goal. Be aware, however, that this will not help you in the event of your server being hacked; as the hacker, upon gaining root access, would be able to access this backup storage the same way your server does. If you want to secure your server against such incidents, you are invited to contract my security services here.

But, enough of shameless plugs! Today we will learn how to perform a regular backup of our data to the complimentary NAS storage that is included with OVH’s Bare Metal plans (a fancy name for dedicated servers) and get better sleep at night. As of writing this article, this storage provides 500 GB of space and we can find it in our server’s configuration panel.

Our first step will be creating a NFS access as shown above, by clicking the … button on the right. Once this is done, there are two pieces of data to take note of — the Name and the ID of the backup storage.

Thereafter, we will create a user named backup, with a home directory of /home/backup and sh as shell.

adduser backup

Let’s now mount our new backup drive, replacing the text between <brackets> as appropiate:

cd /home/backup
mkdir ovh_storage
mount -t nfs <Name>:/export/ftpbackup/<ID> /home/backup/ovh_storage

Test the storage is working and then have it mount on boot by adding this line to the /etc/fstab file:

<Name>:/export/ftpbackup/<ID>	/backups		nfs	rw	0	0

Be careful here as formatting this file wrong may cause your system to fail to boot.

And now to the important stuff. This Bourne Shell script will backup the databases of your choice from a MySQL, MariaDB or Percona server, compress them and store them in datetime coded directories in the backup storage. It will also delete those older than 7 days. If you followed this tutorial to the letter, you only need to replace the DATABASES variables with your own.

#!/bin/sh

MYSQLDUMP=/usr/local/bin/mysqldump
GZIP=/usr/bin/gzip

MAX_AGE=7
DAY=$(date +"%Y-%m-%d")
FILETIME=$(date +"%Y-%m-%d.%T")
BACKUP_DIR="/home/backup"
BACKUP_NAME="<Name>"
BACKUP_ID="<ID>"
MYSQL_USER="backup"

DATABASES="cats rabbits dogs"

#Check if the backup is mounted
if [ ! -d "$BACKUP_DIR" ]; then
  echo -e "\033[32mMounting backup storage\033[0m"
  mkdir $BACKUP_DIR
  mount -t nfs $BACKUP_NAME:/export/ftpbackup/$BACKUP_ID $BACKUP_DIR
fi

#Delete old backups
echo -e "\033[32mDeleting backups older than $MAX_AGE  days...\033[0m"
find $BACKUP_DIR/* -type d -ctime +$MAX_AGE | xargs rm -r

echo -e "\033[32mCreating directory $BACKUP_DIR/$FILETIME...\033[0m"
mkdir $BACKUP_DIR/$FILETIME

for db in $DATABASES
do
        FILE=$db.gz
        echo -e "\033[32mDumping $db...\033[0m"
        $MYSQLDUMP -u $MYSQL_USER -e --single-transaction --quick $db | $GZIP -9 > $BACKUP_DIR/$FILETIME/$FILE
done

echo -e "\033[32mDone!\033[0m"

Let’s copy and paste the text above as /home/backup/backup.sh and give some permissions to it:

chown backup:backup /home/backup/backup.sh
chmod 755 /home/backup/backup.sh

This second command allows the script to be executed without specifying a program, so we can type ./backup.sh instead of sh backup.sh and the shell will figure out how to run it by reading the first line of it (also known as shebang!). Okay, not a big deal but it’s good to mark executables as such to difference them from data – especially if we have colored our shell.

Now let’s head to the mysql console to create a special user for our backup. In my case, I’m using MariaDB 10.5, which provides the unix_socket plugin to identify local users without need of a password.**

CREATE USER 'backup'@'localhost' IDENTIFIED VIA unix_socket;
GRANT USAGE ON *.* TO 'backup'@'localhost';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

And now before going further, switch to the backup user and test the script

su backup

And finally we will create a cron job*** for the backup user to run our script at regular intervals:

crontab -e

Insert this line to backup our databases every day — daily at 3 am sounds like a good time. Mind the tabs!

3	*	*	*	*	sh /home/backup/backup.sh

And that’s all folks! Now you should get better sleep at night knowing your data is safe.

---

Footnotes

* Running this script on Linux is a matter of changing the MYSQLDUMP and GZIP paths. You can find the path of any executable with whereis <program>.

** Pro tip: if you’re not using MariaDB (or sockets) you can still accomplish passwordless login for a specific system user by creating a file named .my.cnf under his home directory containing his login details:

[client]
user = backup
password = something

In this case, the user would be created the traditional way:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';

*** the command “crontab -e” edits a user’s own cron file and it’s the proper, orthodox way of doing this, as opposed to just editing the /etc/crontab file as most people do. Now I’m not the dogmatic type but I hate being asked to review changes when I update FreeBSD to a new version, which is what happens when you edit a system file.