encrypted backups with secure mysqldump

It’s convenient and often suggested to cron something like:

/usr/bin/mysqldump -u root -p PASSWORD --all-databases > /path/to/flatfile.sql

Thats for several reasons not a good idea.
Each user on the machine can view with ps your password. You need to write your plain root  password into your shell script or crontab.

ps axw | grep mysql

MySQL documentation has its guidelines for password security .
Store your password in an option file in your home directory instead of pasting it into shell scripts and commands.

Replace root with a single purpose Linux User

So let’s create a different linux user for mysqldump only.

root@mysql:~# useradd mysqldump -d /home/mysqldump/ -s /bin/bash
root@mysql:~# mkdir /home/mysqldump
root@mysql:~# chown mysqldump:mysqldump /home/mysqldump
root@mysql:~# passwd mysqldump
root@mysql:~# su mysqldump
mysqldump@mysql:/root$ cd
mysqldump@mysql:/home/mysqldump$ mkdir .ssh
mysqldump@mysql:/home/mysqldump$ chmod 0700 .ssh
mysqldump@mysql:/home/mysqldump$ vi .ssh/authorized_keys

Place your SSH-Key id_rsa.pub into ~./ssh/authorized_keys, for ssh|rsync|scp with password-less public key authentication. This way you can run on your foreign host rsync anytime without entering your password.

Read-Only MySQL User for mysqldump

The next improvement is to create a read-only MySQL User for mysqldump. It’s never a good idea to use root for dumping databases.

mysql> GRANT SELECT, LOCK TABLES ON *.* TO dump@localhost IDENTIFIED BY ‘trustno1’;

As always, limit the user to localhost and choose a strong password.

Option File for Password Storage

Now place the option file .my.cnf in your mysqldump home directory.


Set strict permissions to the file

mysqldump@mysql:~$ chmod 0400 .my.cnf 
mysqldump@mysql:~$ ls -la | grep .my.cnf
-r-------- 1 mysqldump mysqldump   39 Feb 27 20:21 .my.cnf

And test your mysqldump user

mysqldump@mysql:~$ mysql -u dump
Welcome to the MySQL monitor.

Now your mysqldump User is setup in a good way to prevent some OOOooops!

encrypt all the thingsEncrypted MySQL Dumps

We do not want to have the unencrypted SQL Dumps available on the harddisk, as they maybe contain passwords. And It’s always a good idea to have the files already encrypted, for the possible fact the Backup Storage does not have Full Disk Encryption or is a single USB Stick or external SATA Drive as cold Storage.
For this reason we are going to use asymmetric encryption also known as public-private key encryption. So we do not have to keep care of the symmetric key and key transport between MySQL Server and Backup System.

Create the Key Pair on your Backup System. Do not transport your private Key to any other systems. And of course, keep a good copy of your key available. If you loose the private key, all your Backups are worthless in less than second.

cave@backup:~/mysqlkey$ openssl req -x509 -sha256 -nodes -newkey rsa:4096 -keyout mysqldump.priv.pem -out mysqldump.pub.pem

And copy the public key to your mysql server.

cave@backup:~/mysqlkey$ scp mysqldump.pub.pem mysqldump@mysql.domain.tld:/home/mysqldump/key/

Verify the keypair if it works as expected. Encrypt a plaintext file on your mysql host, scp it to your backup host, and decrypt it.

mysqldump@mysql:~$ echo "encrypt all the things" > plain.txt
mysqldump@mysql:~$ openssl smime -encrypt -binary -text -aes256 -in plain.txt -out crypted.txt.enc -outform DER /home/mysqldump/key/mysqldump.pub.pem
mysqldump@mysql:~$ ls *.txt*
crypted.txt.enc  plain.txt

Now copy it to your Backup Host and try to decrypt it.

cave@backup:~/mysqlkey$ scp mysqldump@mysql.domain.tld:/home/mysqldump/crypted.txt.enc .
crypted.txt.enc                                                                                                       100%  730     0.7KB/s   00:00    
cave@backup:~/mysqlkey$ ls
crypted.txt.enc  mysqldump.priv.pem
cave@backup:~/mysqlkey$ openssl smime -decrypt -in crypted.txt.enc -binary -inform DEM -inkey mysqldump.priv.pem -out decrypted.txt
cave@backup:~/mysqlkey$ ls
crypted.txt.enc  decrypted.txt  mysqldump.priv.pem
cave@backup:~/mysqlkey$ cat decrypted.txt 
encrypt all the things

If your plain.txt from your mysql host contains the same message as the decrypted.txt on your backup host, all went fine.

This is also the way to decrypt your backups if you ever need them. Keep your private key safe, and do not lose it!

Why smime was choosen over rsautlOpenSSL S/Mime can handle large file encryption, where rsautl is limited to smaller files. And some well explained basics about usage of “openssl smime” http://stackoverflow.com/a/12233688.

Automate encrypted dumps with bash/crontab

This little script checks all available databases, excludes some and runs mysqldump for each db. All db’s are encrypted and tar’ed into a single file containing the date in the filename in LE order.

DAY=$(date +"%Y%m%d")
databases=`$MYSQL --user=$MYSQL_USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER --databases $db | \
  openssl smime -encrypt -binary -text -aes256 -out $BACKUP_DIR/$db$DAY.sql.enc -outform DER /home/mysqldump/key/mysqldump.pub.pem
tar czf mysqldb$DAY.sql.enc.tgz * --remove-files

Comments are closed.