Back up, compress and encrypt a small MySQL Database

db_backup.sh

#!/bin/bash
# Author: Ravi Bhure <[email protected]>
# Date: 13/10/2014
# This script dumps a "orders" database, compresses, encrypts and timestamps it, then saves it to netapp storage local mount. Ideal for a cronjob.
# It uses symmetric encryption, so guard your password carefully.
#
# NOT RECOMMENDED FOR LARGE DATABASES!

# Set your variable..
my_server=db.example.com
storage_server=backups.example.com”

# Exit me if encryption password file not found
if [ ! -f ~/.db_encr_pass.txt ] ; then
	echo "db encryption password file not found"
	exit 1
fi

# Dump a MySQL database "orders"
mysqldump -h $my_server -u root orders > /tmp/orders.sql

# Compress: -9 means best, slowest encryption.
gzip -N -9 /tmp/orders.sql

# Encrypt
openssl enc -aes-256-cbc -in /tmp/orders.sql.gz -out /tmp/orders.sql.gz.aes256cbc -pass file:~/.db_encr_pass.txt
if [ $? = 0 ] ; then
	# Store to storage disk.
	rsync -azp /tmp/orders.sql.gz.aes256cbc $storage_server:/home/prod/netapp/db-backups/$(date +%Y%m%d%H%M%S)_orders.sql.gz.aes256cbc
	echo "Backup encryption successful" | mail -s "Backup success" [email protected]
else
	echo "Backup encryption not went good...Exiting"
	echo "Backup encryption not went good...Exiting" | mail -s "Backup failed" [email protected]
	exit 1
fi

# In order to Decrypt the backup
# openssl enc -aes-256-cbc -in 20141013080616_orders.sql.gz.aes256cbc -d -out orders.sql.gz -k <mypassword>

# Clean up
rm /tmp/orders.sql.gz