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$ 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.
[client] password=trustno1
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!
Encrypted 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 rsautl? OpenSSL 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.
#!/bin/bash BACKUP_DIR="/home/mysqldump/backup/" MYSQL_USER="dump" MYSQL=/usr/bin/mysql MYSQLDUMP=/usr/bin/mysqldump 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 done cd $BACKUP_DIR tar czf mysqldb$DAY.sql.enc.tgz * --remove-files
https://thepiratebay.se/torrent/8118457/TPB.AFK.2013.1080p.h264-SimonKlose
