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

Question

This current project requires that the DB be dumped, encrypted and pushed to s3. I'm wondering what might be some "best practices" for such a task. As of now I'm using a pretty straight ahead method but would like to have some better ideas where security is concerned. Here is the start of my script:

mysqldump -u root --password="lepass" --all-databases --single-transaction > db.backup.sql
tar -c db.backup.sql | openssl des3 -salt --passphrase foopass > db.backup.tarfile
s3put backup/db.backup.tarfile db.backup.tarfile
# Let's pull it down again and untar it for kicks
s3get surgeryflow-backup/db/db.backup.tarfile db.backup.tarfile
cat db.backup.tarfile | openssl des3 -d -salt --passphrase foopass |tar -xvj

Obviously the problem is that this script everything an attacker would need to raise hell.

Any thoughts, critiques and suggestions for this task will be appreciated.

Solution

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

Comments

[DevGambit]

added a short example script for you

[Shivam Bajpai]

Good to go I think.