Encrypt your credentials using GPG

This blog post will look how to use encryption to secure your database credentials.

In the recent blog post Use MySQL Shell Securely from Bash, there are some good examples of how you might avoid using a ~/.my.cnf – but you still need to put that password down on disk in the script. MySQL 5.6.6 and later introduced the  –login-path option, which is a handy way to store per-connection entries and keep the credentials in an encrypted format. This is a great improvement, but as shown in Get MySQL Passwords in Plain Text from .mylogin.cnf, …

Encryption is important component of secure environments. While being intangible property security doesn’t get enough attention when it comes to describing various systems. “Encryption support” is often the most of details what you can get asking how secure the system is. Other important details are often omitted, but the devil in details as we know. In this post I will describe how we secure backup copies in TwinDB.

See the picture. This is what happens when encryption is used incorrectly. The encryption algorithm can be perfect, but poor choice of the mode results in a quite readable encrypted image. This mode is called “Electronic Code Book”, avoid it at all means.

Another bright example of improper encryption use was illustrated in Venona project.

More than once I have wished that mysqldump was never created. In many backup scenarios it is better to use other tools such as xtrabackup. However, there is still times when I end up using mysqldump. For one thing it is included with the mysql packages. It's "always there". Secondly, it sometimes is really a good fit for the tasks. And occassionally, with a bit of work, you can make it do neat tricks. 

I'm going to cover a few of the tricks I have used over the years. Hopefully you will find them helpful. In some cases one of these might even save your bacon.

Trick One:

One of the annoying issues with mysqldump is its lack of flexibility. It's very easy to dump all databases (--all-databases option), or one or more databases (--databases). Even dumping a single table isn't too difficult. But what about all the tables that start with "a"? You could specify each table on the command line, but I have ADD and I can't …

to generate a gpg key, run the following:

gpg --gen-key

when that completes, run

gpg --list-keys

for instance, to get the key information from my pub key, I run:

$ gpg --list-keys cjcollier@mysql.com
pub 1024D/6CC27EA8 2001-04-27
uid C.J. Collier
uid C.J. Collier
uid C.J. Collier
uid [jpeg image of size 9187]

After you generate it, you should publish your public key:

gpg --keyserver pgp.mit.edu --send-keys

mine looks like this:

$ gpg --keyserver pgp.mit.edu --send-keys 6CC27EA8
gpg: sending key 6CC27EA8 to hkp server pgp.mit.edu

You can get my GnuPG key with the following command:

$ gpg --keyserver pgp.mit.edu --recv-keys 6CC27EA8

Let me know what your ID is when you complete this. When I see you
next, I'll sign your …