I’ve spent some time putting together a solution for automated database backups (from MariaDB in this case) to a remote data storage. Since my web host only does backups for my site itself, I was left to find a solution on my own. My goal was to find something that was simple, efficient, and most importantly of all, automated.

Here’s a brief overview of what my backup solution consists of:

• A bash script is run weekly through cron
• This script first creates a gzipped database dump using mysqldump
• The dump is then uploaded to Dropbox¹ using Dropbox Uploader
• Once it’s uploaded to Dropbox, the local copy is deleted

The bash Script

#!/bin/bash

_file="backup-$(date +"%Y-%m-%d").sql.gz"

cd ~
mysqldump --user=username --password=password --host=hostname --databases database1 database2 | gzip >$_file
./dropbox_uploader.sh -q upload $_file "/DB Backups/$_file"
rm $_file

You can download a copy of this script here. Most of it should be self-explanatory. Here’s where you might want to change something:

• You can change the date format
• I have it set to upload to the DB Backups folder in the root of my Dropbox; you can set this to whatever you want
• Put your own database info in to the mysqldump command

Setting Dropbox Uploader Up

dropbox_uploader.sh is a bash script that provides an interface to Dropbox. You can download it from the Github page, and follow the instructions to set it up. Place this file in the same directory as the script above unless you change the reference in the script.

I ended up giving the Uploader script full access to my Dropbox, as I wasn’t sure how to give it application-specific storage and yet still access a folder in the root of my Dropbox.

Once all of this is set up, the script will automatically upload a backup of your database to your Dropbox at whatever interval you set.