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:

# Database credentials 

# Other options 
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 
    if [ $DATABASE != 'Database' ]; then 
# 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 

# 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' ] 
    echo 'agent is not running' 
    kill $pid 
    echo 'agent stopped' 

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