What's the best practice for taking MySQL dump, encrypting it and then pushing to s3?

Solution 1:

first you can create a 'user' in mysql that has read-only permissions for the database in question, that would reduce potential destructive damage, were an attacker to gain access to your backup script.

then you could use gpg or pgp encryption on your backup before or after you compress it, and you can do that without needing to provide a password, using your public key.

and of course, you should chmod 700 backupscript.sh to prevent anyone from reading your password.

there may be other ways to do passwordless database snapshots, but i'm not aware of any off the top of my head.

gpg or pgp seems like a superior alternative to the openssl method you've mentioned, because it can be done without a password.

#!/bin/sh
touch db.backup.sql.gz
chmod 600 db.backup.sql.gz
mysqldump -u nonprivuser --password="pass" --all-databases --single-transaction | gzip > db.backup.sql.gz
gpg -e -r [email protected] db.backup.sql.gz && rm -f db.backup.sql.gz
s3put backup/db.backup.sql.gz.gpg db.backup.sql.gz.gpg