MySQL dump > compress > encrypt

mysql-backup.md

Generate openssl keys:

openssl req -x509 -nodes -newkey rsa:2048 -keyout mysqldump-key.priv.pem -out mysqldump-key.pub.pem

Create a mysql default file:

# ~/.mysqldump
[mysqldump]
host = host.here.com
user = user
password = "password"

Bash script:

#!/bin/bash

DATE=`date +%Y-%m-%d-%H-%M-%S`
ARCHIVE=${DATE}.sql.gz.enc
MYSQLINFO=~/.mysqldump
DATABASE=databasename
PUBLIC_KEY=~/.mysqldump-key.pub.pem

mysqldump --defaults-extra-file=${MYSQLINFO} ${DATABASE} --single-transaction --routines --events --triggers \
  | gzip -c \
  | openssl smime -encrypt -binary -text -aes256 -out ${ARCHIVE} -outform DER ${PUBLIC_KEY}

Decrypt & decompress

openssl smime -decrypt -in [filename].sql.gz.enc -binary -inform DEM -inkey mysqldump-secure.priv.pem -out [filename].sql.gz
gzip -d [filename].sql.gz

Clean up backups

cd [dir] && ls -tp | grep -v '/$' | tail -n +8 | xargs -I {} rm -- {}

Keep the latest 7 files in [dir].

References:
https://www.everythingcli.org/secure-mysqldump-script-with-encryption-and-compression/ http://stackoverflow.com/questions/25785/delete-all-but-the-most-recent-x-files-in-bash

Great !! congrats !!

It seems the -stream option is needed with openssl if you're encrypting a large database on a system that has a limited amount of memory. Otherwise, it will silently fail and you'll be left with an empty backup.
Still trying to understand myself, but I ran across the -stream option being mentioned here: cytopia/mysqldump-secure#21