Bash script for MySQL Dumps GPG Public Key encrypted, then offloaded to S3.

README.md

mysql-backup.sh will dump all of your MySQL databases, encrypt them using a GPG public key, transfer them to a S3 bucket, then delete them.

Getting Started:

1. Install s3cmd ($ apt-get install s3cmd)
2. Generate a GPG key and upload it to a key server. Remember the $KEY_ID and $KEY_NAME. (Google this. It's straightforward.)
3. Download the GPG key onto the MySQL host server. ($ gpg --keyserver subkeys.pgp.net --recv-key $KEY_ID)
4. Create a new keyring for this key. ($ gpg --export $KEY_NAME | sudo tee /etc/backups/keyring.gpg > /dev/null)
5. Put mysql-backup.sh somewhere, and CHMOD 755 it. (/etc/backups/mysql-backup.sh is cool.)
6. Test it. ($ ./etc/backups/mysql-backup.sh)
7. Check your S3 bucket for the backup file, download it and decrypt it using the private key you made in Step 2.
8. If everything worked, add this line to your crontab for backups at noon and midnight ($ crontab -e): "0 0,12 * * * /etc/backups/mysql-backup.sh". Otherwise, I'm @zcs on twitter.

That's it!

mysql-backup.sh

#!/bin/sh
#
# Config Vars
S3_BACKUPS_BUCKET = 'my_encrypted_mysql_backups'
MYSQL_ROOT_PASSWORD = 'my_sql_root_pw'
GPG_KEYRING_PATH = '/etc/backup/keyring.gpg' # Or, wherever your GPG keyring is
GPG_KEY_NAME = '[email protected]'
#
# Backup Script Below
NOW=`date +%Y-%b-%d@%H:%M:%S`
mysqldump -uroot -p$MYSQL_ROOT_PASSWORD --all-databases | gpg --no-default-keyring --keyring $GPG_KEYRING_PATH --trust-model always -r $GPG_KEY_NAME --encrypt > /tmp/$NOW.dump.gpg
SERVER_NAME = `hostname`
s3cmd put /tmp/$NOW.dump.gpg s3://$S3_BACKUPS_BUCKET/$SERVER_NAME/mysql/$NOW.dump.gpg
rm /tmp/$NOW.dump.gpg