June 25, 2014 · automation bash debian dump gpg gzip hacks linux mysql rsync security ssh

Simple Automated MySQL Backup with gpg

I've been bad. Until recently I haven't been backing up my MySQL database. Bad things could have happened. About a month ago I hacked up a simple script to run from cron that will automatically dump, gzip, encrypt using my key gpg (I wrote an extensive post on gpg which you can read here: GNU Privacy Guard - The Basics) and finally rsync to an external server.

Here's the script:

#!/bin/bash 
# Database credentials 
user='' 
password='' 
gpgrcp='gpg_key_ID' 

# Other options 
backup_path='/where/to/store/mysql/dumps' 
prefix='eg. your server name' 
# file suffix will have 
# format: _DAY_MONTH_YEAR__HOUR.sql.gz
suffix='_'`date +%d'_'%m'_'%Y'__'%H`.sql.gz 
 
# show all databases and omitting information_schema and performance_schema 
dbs=`/usr/bin/mysql -u$user -p$password -Bse'show databases' \ | egrep -vi 'information_schema|performance_schema'` 

# Set default file permissions 
umask 177 

# Dump database into SQL file 
for DATABASE in $dbs 
  do 
    if [ $DATABASE != 'Database' ]; then 
      FILENAME=$prefix$DATABASE$suffix 
# dump and gzip databases 
      /usr/bin/mysqldump -u$user -p$password $DATABASE \ 
      --events --ignore-table=mysql.event | \ 
      /bin/gzip > $backup_path/$FILENAME 
# encrypt files 
      /usr/bin/gpg -r $gpgrcp -e $backup_path/$FILENAME 
# delete only gzipped files 
      /bin/rm $backup_path/$FILENAME 
    fi 
done 

# Recycle files older than 1 day 
find $backup_path/ -mtime +1 -exec rm {} \; 

# start ssh-agent for purposes of this script 
eval $(ssh-agent) 

# add identity 
/usr/bin/ssh-add /path/to/identity/file 

/usr/bin/rsync -azhe 'ssh -p [port number if you're not using default one]' \ 
  $backup_path/* user@remote_host:/path/on/remote/host/ 

# remove identities 
/usr/bin/ssh-add -D 

# kill ssh-agent 
pid=`/usr/bin/pgrep -u root 'ssh-agent'` 
if [ $pid -eq '0' ] 
  then 
    echo 'agent is not running' 
  else 
    kill $pid 
    echo 'agent stopped' 
fi

You will obviously need to modify the variables at the top to values suitable for your environment.

The last part of the script designed to back up your database files onto a remote server needs a ssh RSA key pair to run. This allows the script to run in background without the need of providing ssh password. Also, establishing SSH keys provides a more secure connection. To read more about SSH keys head here: Passwordless SSH.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket

Contact