MySQL / MariaDB Backup & Archive Script

I am using MariaDB – the open source version of MySQL – and wanted an easy way to backup my databases on a regular schedule without having to do anything when I created or dropped a database. MariaDB is a drop in replacement for MySQL, so this script should work as is. I put together this base script that will use the show databases command to iterate over each database so that it can be passed to mysqldump. The nice command is used to lower the impact to your regular server work.

Once exported the resulting SQL files are compressed into an archive using tar with bzip2 compression, which saves quite a bit of space over gzip from my tests with this kind of data. After the archive is created the source SQL files are deleted (again using nice) and finally anything in the directory older than the specified archive days value will be deleted.

You will need to create a file called /etc/sysconfig/dbbackup that stores the USERNAME and PASSWORD parameters for the script.

#/bin/bash
#
# MySQL/MariaDB backup script
# Justin Silver
# http://www.justinsilver.com
#
# Use cron to schedule this script to run as frequently as you want.
###################################################################################

# Set properties in this file
SYSCONFIG="/etc/sysconfig/dbbackup"

# User with SELECT, SHOW VIEW, EVENT, and TRIGGER, or... root
#USERNAME="USERNAME"
#PASSWORD="PASSWORD"

# Archive path
ARCHIVE_PATH="/var/backups"

# Archive filename
ARCHIVE_FILE="databases_`date +%F_%H-%M-%S`.tbz2"

# Archives older than this will be deleted
ARCHIVE_DAYS="15"

# Set or override config variables here
if [ -f $SYSCONFIG ]; then
    source $SYSCONFIG
fi

if [ -z "$USERNAME" ] || [ -z "$PASSWORD" ]; then
    echo "You must set USERNAME and PASSWORD in $SYSCONFIG";
    exit
fi

# Change working directory
cd $ARCHIVE_PATH

# Get all of the databases
for database in `mysql -u $USERNAME -p"$PASSWORD" -Bse 'show databases'`; do

        # Skip ones we don't want to back up
        if [ "performance_schema" == "$database" ]; then continue; fi
        if [ "information_schema" == "$database" ]; then continue; fi

        # Use Nice to dump the database
        nice mysqldump -u $USERNAME -p"$PASSWORD" --events $database > $database.sql

done

# Use Nice to create a tar compressed with bzip2
nice tar -cjf $ARCHIVE_FILE *.sql

# Remove the SQL files
nice rm -rf *.sql

# Remove old archive files
nice find . -mtime +$ARCHIVE_DAYS -exec rm {} \;