🔗mysqldump + xz + openssl

First add to .my.cnf the proper username/password to avoid getting a warning:

[client]
host = localhost
user = dbadmin
password = secret

To take the dump and keep it only for 31 days:

#!/bin/sh
DAY=$(date +%d)
mysqldump --events --routines --triggers --add-drop-database --compress --hex-blob --opt --skip-comments --single-transaction dbname | \
xz -c | \
openssl smime -encrypt -aes256 -binary -out /safe/path/${DAY}.sql.xz.enc -outform DER /path/to/cert.pem

🔗mysqldump

The options used:

mysqldump --events --routines --triggers --add-drop-database --compress --hex-blob --opt --skip-comments --single-transaction --databases dbname

The --databases option will add the CREATE DATABASE

Example multiple databases:

mysqldump --events --routines --triggers \
--add-drop-database --compress --hex-blob \
--opt --skip-comments --single-transaction \
--databases dbname dbname2 dbname3

🔗xz

Compress the output:

xz -c

-c is to write to stdout

🔗openssl (encrypt)

Encrypt the backup using asymmetric encryption (key pairs):

openssl smime -encrypt -aes256 -stream -binary -out /safe/path/${DAY}.sql.xz.enc -outform DER /path/to/cert.pem

Notice the -stream option, it helps to encrypt files bigger than 1.5 GB, and ouput is piped: xz -c |

How to create a pair of keys using openssl:

$ openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout example.key -out example.pem -subj "/CN=example.com" -days 3650

To add password to the key remove the -nodes (No DES encryption) options. https://security.stackexchange.com/a/106530/54143

🔗Decrypt

openssl smime -decrypt -in database.sql.xz.enc -binary -inform DEM -inkey example.key -out database.sql.xz

🔗Un-compress

xz -d database.sql.xz