Home

Very Simple but Secure Database Backup

I am using the very fine DBMail as a MySQL email backup, more on this in other articles. Please note that this is my first article, so please be gentle. But for now, I wanted to share how I backup this email store and hence a database. I have written a script that :

  • Dumps the relevant database.
  • Compresses the dump file.
  • Encrypts the database using Gnu Privacy Guard (but any method will do).
  • Uploads the database to a remote location using SFTP.

The prerequisites for using the script "as is" are :

  • MySQL dump user with sufficient privileges to dump the required database.
  • ~/.my.cnf configured for user having sufficient privileges to backup the DB. For example:

[client]
user = dbuser
password = dbpass
database = dbmail

  • Working GPG installation.
  • Remote backup host running an sftp server.
  • Local copy of sftp OpenSSH client.
  • Keychain for password-less login to remote server. More on keychain in a future article.

Note that this script stems from a quick and dirty script that I wrote for myself so use what is in this article more as a guideline :-) They script itself will require some modification in your environment. And, without further ado, here is the script :

#!/bin/sh
# Author : Fred Clausen <ftclausen@gmail.com>

database="dbmail"
gpg_user="Friedrich"
local_dir="/data/tmp"
remote_host="remote-host.example.com"
remote_user="fred"
remote_dir="/var/tmp"

source $HOME/.keychain/$HOSTNAME-sh
cur_name="$local_dir/$database-`date +%a`.sql.gz"
batch="`mktemp`"

if [ -f ${cur_name}.gpg ]; then
        rm -f ${cur_name}.gpg
fi

mysqldump --databases $database | gzip > $cur_name
if [ $? != 0 ]; then
        echo "Error dumping data"
        exit 1
fi

gpg -r $gpg_user -e $cur_name

cat > $batch <<END
cd $remote_dir
put ${cur_name}.gpg
END

sftp -b $batch $remote_user@$remote_host

rm $batch

You will obviously need to modify the variables at the top to values suitable for your environment. Also, see how this script recycles file names to keep a week's worth of backups.

Good luck!

About Consultancy Articles Contact
References Red Hat Certified Architect By Robert de Bock Robert de Bock
Curriculum Vitae By Fred Clausen +31 6 14 39 58 72
By Nelson Manning robert@meinit.nl