The Problem:

Databases like MySQL and MariaDB can get corrupted pretty easily, due to the fact that much of the database is stored in RAM, so a hard crash can leave entire tables completely corrupted. This is something I know first hand from experience. A disk backup or snapshot is not sufficient to backup your database!

The Solution:

A included program called MySQLDump! (Man Page) MySQLDump allows you export the entire contents of your database into a text file that is standard SQL commands. This means you can rebuild your database by just executing the text file in any SQL language. This script can be automated with a cron job to make sure that it happens daily!

This script does a few things:

• Dumps the entire contents of the database to a text file
• Compresses the file into a zip file
• Logs what is going on
• (optional) You can expand this to copy the zip file to a NAS using RSYNC

What you need to get started:

Getting this setup does not really require too much! I am running this on Ubuntu 20.04, with MariaDB, but this should work with any OS running MySQL or MariaDB. MySQLDump comes stock with these! You will also need to have both zip and crontab installed on your machine.

How to setup the automatic backup

Step 1: Create a new user for the backup to run on

We want to use a new SQL user on your database that has read only access to the database tables. This is for both security, and simplicity. (You do not want a root password just stored on your system in plain text)

To do this login to your database with a root account by running

sudo mysql

You then will create a new backup user by executing the following SQL commands to create the backup user

GRANT LOCK TABLES, SELECT ON DATABASE_NAME.* TO 'BACKUP_USER'@'localhost' IDENTIFIED BY 'PASSWORD'; #Change the password to a random string
FLUSH PRIVILEGES;

Step 2: Create the backup script

We are going to be using a backup script that I found and modified

First go to your user folder and create a new backup file and close the permissions.

cd ~
mkdir Database_Backup
touch db_backup.sh
sudo chmod 700 db_backup.sh
nano db_backup.sh

Now you are going to paste in this code: Updating the username password, and folder locations

#!/bin/bash
# Backup storage directory
backupfolder=/home/will/Database_Backup #update this to your home folder
logfile=/home/will/db_backup.log #update this to your home folder
# MySQL user
user=BACKUP_USER
# MySQL password
password=password #update this to the backup password you created
# Number of days to store the backup
keep_day=15
sqlfile=$backupfolder/all-database-$(date +%Y-%m-%d_%H-%M-%S).sql
zipfile=$backupfolder/all-database-$(date +%Y-%m-%d_%H-%M-%S).zip
echo Starting Backup [$(date +%Y-%m-%d_%H-%M-%S)] >> $logfile
 
# Create a backup
/usr/bin/mysqldump -u$user -p$password --all-databases >> $sqlfile
if [ $? == 0 ]; then
  echo 'Sql dump created' >> $logfile
else
  echo [error] mysqldump return non-zero code $? >> $logfile
  exit
fi
# Compress backup
zip -j $zipfile $sqlfile
if [ $? == 0 ]; then
  echo 'The backup was successfully compressed' >> $logfile
else
  echo '[error] Error compressing backup' >> $logfile
  exit
fi
rm $sqlfile
echo $zipfile >> $logfile
echo Backup complete [$(date +%Y-%m-%d_%H-%M-%S)] >> $logfile
# Delete old backups
find $backupfolder -mtime +$keep_day -delete

You should now be able to test the script by running

./db_backup.sh 

which should create a zipped output in the ~/Database_Backup directory

Step 3: Execute it in a cron job

We now want the cronjob to execute the backup at least once a day. To do this simply type

crontab -e

To edit the crontab schedule. We are going to want to add a line to the end of this file which runs the backup script

30 22 * * * /home/will/db_backup.sh #edit this to be where your script is

in this case the backup will be run every day at 10:30.

And now you are done! the backups should automatically be running!

Step 4: Checking the backup

Check back in in a few days and your ~/Database_Backup folder should be full of backups!

You can unzip one of these files to see the data within it!