You plan to make secure and automated mysql database backups on your server? There is a lot more to it than just using mysqldump from inside cron. Don’t step into the pitfalls many people do and risk to compromise all of your databases.
What makes a well designed and secure database backup script?
For that question to answer you will need to take the following considerations in mind before you start:
- Can the password be revealed somehow on a process or filesystem level?
- Should you use opt-in or opt-out database selections?
- If you use encryption, is there any point in time databases can be intercepted unencryptedly?
The complete source including a nagios/icinga plugin to monitor the state of your backups can be found at github:
1. Password revealing
I see a lot of people doing this:
mysqldump --user=root --password=foo --host localhost database > database.sql
Even if you start the above command as root, any user on your server can view the process list
ps axw | grep mysql 40802 ?? S 33:05.94 mysqld 42176 s005 S+ 0:03.86 mysqldump --user=root --password=foo database
Ok, but what if mysqldump is started from within a shell script?
#!/bin/sh mysqldump --user=root --password=foo --host localhost database > database.sql
The output stays the same, the script is not disguising the mysqldump call:
ps axw | grep mysql 40802 ?? S 33:05.94 mysqld 42130 s005 S+ 0:00.01 mysql_db_dump.sh 42176 s005 S+ 0:03.86 mysqldump --user=root --password=foo database
Here is what MySQL says to it:
MySQL End-User Guidelines for Password Security
Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.
1.1 Option file
The best solution to this problem is to create a mysql option file.
[mysqldump] host = localhost user = root password = "verysecurePassword!@#WithSpecialChars%&@"
With this you can do the following call:
mysqldump --defaults-extra-file=/etc/mysqldump.cnf database > database.sql
The process table can no longer reveal the password:
ps axw | grep mysql 40802 ?? S 33:05.94 mysqld 42176 s005 S+ 0:03.86 mysqldump --defaults-extra-file=/etc/mysqldump.cnf database
The next step is to secure the
mysqldump.cnf file. MySQL recommends 600, I would even recommend 400 to also prevent it from accidentally modifying.
1.2 Disguise Process List
Alternatively and/or additionally you can change the mount options for
/proc in order to hide processes that do not belong to a user. There is a mount feature called
hidepid=0– The old behavior – anybody may read all world-readable
hidepid=1– It means users may not access any
/proc/directories, but their own. Sensitive files like cmdline, sched*, status are now protected against other users
hidepid=2– It means
/proc/PID/will be invisible to other users. It compicates intruder’s task of gathering info about running processes, whether some daemon runs with elevated privileges, whether another user runs some sensitive program, whether other users run any program at all, etc
/proc filesystem with
mount -o remount,rw,hidepid=2 /proc
And add it to
proc /proc proc defaults,hidepid=2 0 0
2. Opt-in vs Opt-out
- Opt-in means: Dump every database I explicitly specify.
- Opt-out means: Dump all databases except those I explicitly specify.
If you are paranoid about backups and you should be, you should always choose opt-out. Why?
Imagine you have set up automated backups. Databases are dumped to filesystem and rsynched to a remote server. It all runs automatically and you never need to care about it again. Two months later you add another project with its own database to your server and you completely forget to include this database in the backup script… The worst that can happen is that in case you need a backup it is simply not there.
If you use opt-out, the worst that can happen is that you dump databases that are not needed.
Again: Always use opt-out!
3.1 Symmetric vs Asymmetric
Before you are going to encrypt your database dumps you have to choose a method which best suits security.
3.1.1 Symmetric Encryption
You have one key or password which is used for encryption and also for decryption. If someone gets hands on this key/password on the server which does the backup your database dumps are considered compromised. So you have to protect it very well on a filesystem and process level.
3.1.2 Asymmetric Encryption
Also known as public-private key encryption. It overcomes some of the above security problems. You have one key which can be publicly available that is used to encrypt your data. If this key is compromised, nothing bad can happen. The private is held on a different location which is only used to decrypt your backups. You should also never loose this key, as you won’t be able to restore your backups anymore.
With this method each of your servers can hold a copy of the same public key and decrypt the backup.
Go with asymmetric encryption!
You can use OpenSSL S/Mime which can handle large file encryption as well.
3.2 Race conditions
If you encrypt your backups, make sure not simply write the unencrypted files to disk, encrypt them and delete the original files. This leaves a door open for an attacker to view the unencrypted data in the moment it is written to disk.
You better pipe the dumps directly to the encryption process and then write it to disk.
3.3.1 Generate public-private keypair
Generate a 2048bit strong rsa keypair:
openssl req -x509 -nodes -newkey rsa:2048 -keyout mysqldump-secure.priv.pem -out mysqldump-secure.pub.pem
Use the public key to encrypt:
openssl smime -encrypt -binary -text -aes256 -in database.sql -out database.sql.enc -outform DER mysqldump-secure.pub.pem
Encrypt without race condition. Note the echo command is just a representation. Later on the mysqldump will be parsed to openssl.
echo "output" | openssl smime -encrypt -binary -text -aes256 -out database.sql.enc -outform DER mysqldump-secure.pub.pem
Use the private key to decrypt
openssl smime -decrypt -in database.sql.enc -binary -inform DEM -inkey mysqldump-secure.priv.pem -out database.sql
4. Mysqldump Options
4.1 Triggers, Routines and Events
If you use mysqldump without any parameters, only triggers are dumped, stored procedures and events will not be backed up. This is just the default behaviour.
So in order to also dump stored procedures and events you need to specify the following:
mysqldump --routines --events database > database.sql
Mysqldump automatically adds
--triggers, but just to be on the safe side in case future versions will remove it, append it as well.
mysqldump --routines --events --triggers database > database.sql
4.2 Transactional Table Options
If you have at least one database with transactional tables (for example innoDB tables). You should add the single-transaction parameter, which makes sure that you dump the database in a single transaction and that it stays consistent.
This is actually best explained with a negative example:
- Table A and table B have some kind relations (If something is deleted from B, it needs to be deleted from A as well)
- You start dumping table A without single transactions
- Another process deletes rows from table B which has relations to table A
- You start dumping table B without single transactions
Now you are left with records in table A that do not have any associations in your table B.
Single transaction basically creates a checkpoint and dumps the data as it was exactly at this very moment.
So the dump command would now look like this
mysqldump --routines --events --triggers --single-transaction database > database.sql
5. Working example
The final version to dump a single database securely looks like this:
mysqldump --defaults-extra-file=/etc/mysqldump.cnf \ --routines --events --triggers --single-transaction database | \ openssl smime -encrypt -binary -text -aes256 -out database.sql.enc -outform DER mysqldump-secure.pub.pem