#!/bin/bash
# This script dumps a database to SQL, compresses, encrypts and timestamps it, then saves it to Dropbox. Ideal for a cronjob.
# It uses symmetric encryption, so guard your password carefully.
#
# NOT RECOMMENDED FOR LARGE DATABASES!

# Dump a MySQL database
# Read this for how to get your password into mysqldump:
# https://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
mysqldump -u username > database.sql

# Or dump a PostgreSQL database. For this one, you'll need to put your credentials
# in ~/.pgpass in this format:
# 
# hostname:port:database:username:password
#
pg_dump --host=localhost --username=username database > database.sql

# Compress: -9 means best, slowest encryption.
gzip -N -9 database.sql

# Encrypt
openssl enc -aes-256-cbc -in database.sql.gz -out database.sql.gz.aes256cbc -pass file:db_encryption.pass

# Store
mv database.sql.gz.aes256cbc /home/user/Dropbox/db-backups/`date +%Y%m%d%H%M%S`_database.sql.gz.aes256cbc

# Clean up
rm database.sql.gz