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:

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 :

# Author : Fred Clausen <[email protected]>


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

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

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

gpg -r $gpg_user -e $cur_name

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

sftp -b $batch [email protected]$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!