I’m a software developer who runs Scribophile, an online writing group for serious writers, Writerfolio, an online writing portfolio service for freelancers, and Standard Ebooks, an open source project that produces liberated ebooks for the true book lover.

Setting up MySQL SSL in Ubuntu 14.04

If you’re running Ubuntu 14.04 servers, you may at some point have tried to set up MySQL replication with SSL enabled. There’s no shortage of guides out there on how to do that, but if you follow the directions on most of those guides, you’ll be surprised to find that MySQL just doesn’t enable SSL, no matter what you do.

The issue turns out to be an incompatibility between the versions of MySQL and OpenSSL packaged with 14.04. MySQL expects the private keys you generate to be in a certain format, and the version of OpenSSL on 14.04 doesn’t generate those kinds of keys by default.

The good news is that this handy answer from Ask Ubuntu provides a solution. When you start generating your CA and client/server keys before you configure your master and slave MySQL servers, use these commands instead:

openssl genrsa 2048 > ca-key.pem openssl req -sha1 -new -x509 -nodes -days 10000 -key ca-key.pem > ca-cert.pem openssl req -sha1 -newkey rsa:2048 -days 10000 -nodes -keyout server-key.pem > server-req.pem openssl x509 -sha1 -req -in server-req.pem -days 10000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem openssl rsa -in server-key.pem -out server-key.pem openssl req -sha1 -newkey rsa:2048 -days 10000 -nodes -keyout client-key.pem > client-req.pem openssl x509 -sha1 -req -in client-req.pem -days 10000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem openssl rsa -in client-key.pem -out client-key.pem

Note that modern SSL hygiene recommends you use SHA-256 in your certificates instead of the dated SHA-1; but the version of MySQL in Ubuntu 14.04 can’t understand SHA-256 certificates yet.

Once you’ve run the code above, you’ll have the same key files used in all the tutorials you’ve found, but in a format that MySQL can understand. Follow the rest of the usual steps for configuring SSL and you’ll be good to go!