1. Encrypting MySQL Backups

Encrypting MySQL Backups

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.

linux_ecb (1)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.

There is encryption algorithm that is mathematically proven to be unbreakable. That means you cannot decrypt a cipher text even with brute force attack and having unlimited computing power and time. It is One-Time Pad. It’s very simple and fast – it’s a XOR of a plain-text message and a key. The only problem with the algorithm is – it requires a key to be as large as the plain text message. But if you can securely transfer the key why would you need the encryption at all? That’s a reason why One-time Pad is not so popular. But it’s still usable. For example, you can generate a really large key, much larger than a typical plain text message. Then use some secure channel to transfer the key and then use parts of the key to send encrypted messages. Basically, this is what Soviets did to spy in USA. They generated a bunch of keys and used them to encrypt messages to a Moscow recipient. Because Soviets were broken by design they either ran of keys or some lazy-bone didn’t destroy used keys and at some point the keys were reused to encrypt more than one message. That broke their encryption and the USA managed to decipher many messages.

Thus there is a fundamental rule. Never use or design home backed encryption algorithms.

At TwinDB, we use GnuPG to encrypt MySQL backups and for communication between an agent and the dispatcher (See TwinDB Architecture for details).

The public key of the dispatcher is hard-coded in the agent (I’ll refer to it as api@twindb.com). The agent uses the public key api@twindb.com to send encrypted messages to the dispatcher. When a user registers new MySQL server in TwinDB the agent generates its own RSA keys pair, it’s referred as <UUID>@twindb.com, for example e456b18e-11eb-49ff-93f9-5341656799fe@twindb.com .

Here’s how you can generate a pair of GPG keys in Python non-interactively:

The agent sends its public key to the dispatcher and the dispatcher since then can communicate securely with the agent. It’s nice that gpg can sign messages, so the dispatcher can authenticate the agents, too.

Encrypting MySQL Backups

When the agent takes a backup it streams the backup copy to gpg process and it encrypts the stream with its own public key.

Hence one of the biggest features of TwinDB I’m proud of. The agent encrypts the backup copy with its own key. So, we, at TwinDB, can not decrypt user backups! We did it because of two reasons. First, we want our users to trust TwinDB. “You can get much farther with a kind word and encryption than you can with a kind word alone” (c) almost Al Capone. Second, it’s a kind of protection for us. If TwinDB is broken (I have no illusions, there are smarter hackers out there than we are) the burglars won’t gain access to user data.

The private key is generated locally and never leaves the server where it was generated.

But how can we restore the database if the server is destroyed with the private key?

We ask a user to generate their own keys pair and give us the public key. The user should keep the private key in a secure place, print it out and put in a bank deposit box.

Screenshot 2015-05-13 17.22.04

If the public key is available the dispatcher schedules a job for the agent and ask it to encrypt its private key with the user’s public key and send it back to the dispatcher. In case if the server is completely destroyed the user can get the encrypted server’s private key, decrypt it and decrypt the backup copy to restore the database.

Have a question? Ask the experts!

Previous Post Next Post
  • cytopia

    Hi Aleksandr,
    you might also want to consider openssl smime asynchronous encryption (aes + pub/priv-key). Doing public/private-key encryption alone on large databases might be a huge performance impact.

    Have a look at http://mysqldump-secure.org

    • Hi cytopia,

      mysqldump-secure is a nice project – thank you for sharing it!

      I don’t quite understand what’s the difference is? You’re suggesting asynchronous encryption and same time stating it might be a huge performance impact.

      Also, did you take into account other bottlenecks like network transfer and even xtrabackup itself?

      • cytopia

        Hi ALexsandr,

        sorry for the misleading description. I meant that public/private key encryption only has some performance problems on large files. The – in terms of performance – better way would to go with asynchronous encryption.

        • What is “asynchronous encryption”? Do you mean asymmetric encryption?

          • cytopia

            Yes, that’s the word I meant: asymmetric encryption

          • Here are numbers I got on a fairly powerful machine.
            Xtrabackup stream to /dev/null – 200MB/sec.
            Xtrabackup stream piped to gpg and then to /dev/null – 30-40MB/sec.
            Xtrabackup stream piped to openssl with symmetric encryption (aes-256-cbc) with openssl gave 140MB/sec.

          • cytopia

            Hi Aleksandr,

            here is my bench:

            $ time gpg –yes –batch –no-permission-warning –quiet –recipient cytopia –output sample.txt.gpg –encrypt sample.txt

            real 0m56.060s
            user 0m54.247s
            sys 0m1.392s

            $ time openssl smime -encrypt -binary -text -outform DER -aes256 -in sample.txt -out sample.txt.aes mysqldump-secure.pub.pem

            real 0m18.025s
            user 0m13.862s
            sys 0m3.498s

            For better view I put it up to pastebin: