Home > Uncategorized > Running a Secure (Encrypted) MySQL Backup Using mysqldump on Linux

Running a Secure (Encrypted) MySQL Backup Using mysqldump on Linux

One of the challenges many face when running a secure encrypted MySQL backup on Linux is using managed or scheduled mysqldumps without exposing them.

  1. The data – within the mysqldump backup file
  2. The credentials – that are used connect into mysql

So, how might this be accomplished? I’ll show you one option. It starts with the installation of Gazzang’s ezNcrypt. Its not open source, but it is inexpensive and provides you a simple and secure means to protect and encrypt data transparently with the flexibly to map to your environment and applications.

This technique also applies to other backup tools such as xtrabackup.

This can also go along with transparently encrypting your mysql data.  I discussed this on oursql  podcast #55 a few months ago. 

Follow these steps to perform a secure mysqldump:

Step 1. Create a mysqldump cnf file. This provides the username, password and secure file destination.

> cat protected.cnf

[client]
user=”<your_user>
password=”<your_password>
result-file=/var/lib/mysqlbackup/<backupfilename>

Note: you can also encrypt this cnf file off the system if needed

Step 2. Encrypt this mysqldump cnf file. If its in plain text it’s not protected.

> sudo ezncrypt –e @mysqlbackup /home/mfrank/protected.cnf

ezncrypt | Checking system dependencies

| Verifying ezncrypt license

| getting information about location

|   > path: /var/lib/ezncrypt/ezncrypted/mysqlbackup

ezncrypt | Checking encryption status

| done!

keymgr | Retrieving key from KSS

|  > Encryption password retrieved from KSS

| generating keys

| done!

ezncrypt | encrypting files

|  > checking disk space

|  > encrypting /home/mfrank/protected.cnf

| done!

ezncrypt | congratulations. you have encrypted your Files!!

Step 3. Create a backup directory and set permissions appropriately. This is where the backups will be stored.

> sudo mkdir /var/lib/mysqlbackup

> sudo chown <linux_user> /var/lib/mysqlbackup

Step 4. Encrypt the backup directory with ezNcrypt. All files going to this directory /var/lib/mysqlbackup will be encrypted.

> sudo ezncrypt –encrypt @mysqlbackup /var/lib/mysqlbackup

Step 5. Create the access control rule for mysqldump. This allows access to the key plus permissions to the files in @mysqlbackup.

> sudo ezncrypt-access-control –add “ALLOW @mysqlbackup * /usr/bin/mysqldump”

> passphrase:

> salt:

Step. 6 Run the mysqldump. From either the commandline or cron.

> mysqldump –defaults-extra-file=/home/mfrank/protectedlogin.cnf –all-databases

Note: the backup file and cnf file are actually physically located (for my default installation) in /var/lib/ezncrypt/ezncrypted/mysqlbackup

You can see the links using 

> ls –l 

Mission accomplished.

Heres the link to the ezNcrypt download if your interested.

Review the benefits of running a mysqldump with ezNcrypt:

  1. The OS user can now run the backup without knowing the username and password to mysql.
  2. The OS user cannot read the files that were generated by mysqldump.
  3. If another process copies the backup file – i.e. a scheduled filesystem backup – the file would be AES-256 encrypted. Without access to the encryption keys the files are protected.
  4. For recovery, the file can be easily be decrypted (ezncrypt – decrypt) and restored using mysql.

Note: There are ways to setup a trusted auto restore executable or script such that you can restore with seeing the username/password or mysqldump. data as well. I’ll blog about some other time.

      5. The files can be sent to another system and decrypted (if you have the key) and then can be restored.

It’s hard to continually develop your own security solutions with encryption and key management. Transparent encryption solves many problems. For a more in-depth look, you might be interested in this EMA paper Unifying Data Encryption: Liberating Transparent Encryption for Any Purpose”

In conclusion, I think transparent encryption provides a somewhat novel way to accomplish this task that is both easy and secure. It has become increasingly important to improve security and enforce principles of “need to know” and “separation of duties” across business partners and 3rd parties, especially in Cloud and PaaS environments. This is one method towards providing that for MySQL backups using mysqldump.

About these ads
  1. November 25, 2011 at 10:41 pm

    Backup e Restauração de banco de dados MySQL via SSH: http://naninho.blog.br/web/mysql/backup-restauracao-banco-de-dados-mysql-ssh.html

    • Mike Frank
      December 20, 2011 at 6:50 pm

      but from what I can tell – this doesn’t protect the mysql user/password

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: