Secure mysqldump script with encryption and compression

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?

TL;DR

The complete source including a nagios/icinga plugin to monitor the state of your backups can be found at github:

http://mysqldump-secure.org

cytopia/mysqldump-secure cytopia

Latest Stable Version

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.

  1. hidepid=0 – The old behavior – anybody may read all world-readable /proc/PID/* files (default)
  2. 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
  3. hidepid=2 – It means hidepid=1 plus all /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

Mount your /proc filesystem with hidepid

mount -o remount,rw,hidepid=2 /proc

And add it to /etc/fstab

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. Encryption

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.

3.1.3 Conclusion

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 Example

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

3.3.2 Encrypt

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

3.3.3 Decrypt

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

13 comments on “Secure mysqldump script with encryption and compression”

  1. Ayub Reply

    Hi,

    I store data in mysql while it is encrypted, I automatically back up my data daily

    I have the pem key in a notepad file to the encrypted data.

    I have a cron job and I want to decrypt the data as I back it up, so that I make edits then store it back as an enctpted dumb.

    Any help will be much appreciated as I am stuck.

    Thankx

    • cytopia Reply

      Hi Ayub,

      I’m not quite sure what you mean.

      Do you store your live data encrypted via built-in mysql encryption or do you have certain fields (vchar, blobs) encrypted manually via your application?

      Its hard to answer if there is so little information. You could provide an example extract of how it looks and how it should be to make it clearer to understand the situation.

      Best

      • Ayub Reply

        Hi Cytopia,

        Thanx for the prompt reply

        The application am using is ODK to collect data, it encrypts the data before transmission to the server in a mysql database.

        I created an RSA key pair using openssl, the public key is what the application I use to collect data (ODK) uses to encrypt the data before being submitted and stored in mysql.

        while in mysql a few fields are not encrypted while all other fields are ecnrypted and all I can see are fields like BASE64_ENCRYPTED_KEY , BASE64_ENCRYPTED_ELEMENT_SIGNATURE, and the data in here are just characters like adasda45asddas123dasd

        I have the private key (PEM key) that I want to use to decrypt the above fields to get back the original filed names such as age, name, date of birth.

        I want to decrypt this data beasue I want to do some celaning for the data in mysql

        Thanx

        Any help will much be appreciated as am confused/stuck at this point

        • cytopia Reply

          Hi Ayub,

          so as I understand:

          You might have a dump that looks similar to this:

          ...
          INSERT INTO table(id, blob) VALUES
          (1, 'c2RmIGFzZGYgYXNkZiBhc2Rm'),
          (2. 'asdasd9BKfisafdasGF23rasfas'),
          ...
          

          The id is unencrypted and the second field blob stores encrypted values.

          This is not trivial. I guess you will need to apply some regex skills in order to extract all encrypted values from the file, then decrypt them and store them again at the same position or create a new file.

          Another approach could be to dump in csv-format and only the encrypted fields. Then you could decrypt field by field.

          • Ayub Reply

            hi cytopia,

            Looks like now we are on same page,

            could u be having an example how I can write the regex expression?

            thanx

            • cytopia Reply

              Hi Ayub,

              this totally depends on how your data looks like.

              I suggest you export your encrypted fields line by line (only the value of the fields from all tables) in a text file and then you can decrypt your data line by line using a simple for loop.

              while read -r line; do
              echo "${line}" | <openssl decrypt command here>
              done < enc-db-dump.txt
              
  2. donnie Reply

    How can I gzip encrypted database? I’m using script from “5. Working example” but don’t know where to palce gzip command, before openssl?

  3. Manoj Reply

    I am not being able to use/test the code mentioned above:

    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

    Would you please explain more with complete syntax.

Leave a Reply to cytopia Cancel reply

Your email address will not be published.