create encrypted database backups

backup.sh

#!/bin/bash

# Do the following to create the pub.pem
# openssl rsa -in id_rsa -outform pem > id_rsa.pem
# openssl rsa -in id_rsa -pubout -outform pem > id_rsa.pub.pem

# gen rand key
openssl rand -base64 32 > key.bin

# timestamp for dump 
timestamp=$(date -u +%Y%m%d_%H%M%SZ)

# make local tmp dir with timestamp
mkdir ./$timestamp

# gen rand key
openssl rand -base64 32 > ./$timestamp/key.bin

# encrypt key
openssl rsautl -encrypt -inkey ~/.ssh/id_rsa.pub.pem -pubin -in ./$timestamp/key.bin -out ./$timestamp/$timestamp.bin.enc

# mysql dump and zip
mysqldump --all-databases > ./$timestamp/dump.sql
zip ./$timestamp/dump.sql.zip ./$timestamp/dump.sql

# Encrypt mysql_dump
openssl enc -aes-256-cbc -salt -in ./$timestamp/dump.sql.zip -out ./$timestamp/$timestamp.sql.zip.enc -pass file:./$timestamp/key.bin

# cp files to s3
aws s3 cp ./$timestamp.sql.zip.enc $DATABASE_BACKUP_BUCKET
aws s3 cp ./$timestamp.bin.enc $DATABASE_BACKUP_BUCKET

# Delete ALL the files! - maybe run with trap on exit
rm -rf ./$timestamp

restore.sh

#!/bin/bash

# download file from s3
aws s3 cp $DATABASE_BACKUP_BUCKET/$timestamp.bin.enc ./key.bin.enc
aws s3 cp $DATABASE_BACKUP_BUCKET/$timestamp.sql.zip.enc ./dump.sql.zip.enc

# decrypt key
openssl rsautl -decrypt -inkey id_rsa.pem -in key.bin.enc -out key.bin
openssl enc -d -aes-256-cbc -in dump.sql.zip.enc -out dump.sql.zip -pass file:./key.bin

# unzip sql dump
unzip dump.sql.zip

# load from dump
mysql -u root -p < dump.sql