Configure mySQL server to use SSL

10 Apr 2011 by Misha Dragojevic

In this post, I will cover rarely explored topic of enabling mySQL server to use SSL encryption. Why would you want to do that? The most common example is mySQL replication over the Internet and I intend to cover this topic in one of my next articles. Another use could be with one time, ad hoc, mySQL connectivity from unsecured location: by providing a special “SSL encrypted” user, one can safely connect to mySQL over unsecured line.

Procedure below has 3 steps that need to be performed on your mySQL server. Explanation that follows was tried on Ubuntu 10.04 LTS with mySQL 5.1.41.

Step 1: Generate SSL certificates

We need to create SSL keys and certificates first. You can do this on any server, although doing it on Master makes the most sense.

Customize SSL configuration for your organization by editing file “/etc/ssl/openssl.cnf”. By doing so, you will make your life easier and will type less :-)

Prepare a simple shell script that will generate certificates. Script below worked for me, make sure you enter different common name for server and client (if you enter the same, SSL connection will fail)

Again, the most important thing to remember above is to use different common name for server and client.

Step 2: Prepare mySQL server

In this step, you will configure mySQL server to be able to use SSL encryption by providing references to files created above. Read more here.

We will use “master.mybigstartup.com” in this document when we want to reference external interface of this server. Make sure you understand how Amazon EC2 assigns network interfaces, and what is external vs. internal interface.

Edit /etc/mysl/my.cnf and add the following lines in “mysqld” section:

 Restart mySQL with: "service mysql restart" To test:

If you don’t see “YES” next to “have_ssl”, go back and fix the error before continuing.

Step 3: Create test user

Let’s create test user to verify that client / server encryption works. Afterwards, if you decide to replicate using SSL, you can use this user for replication.

Since we are talking about replication users, I would suggest having two users for replication: let’s call them user “rep1” for “regular”, unencrypted replication and user “rep2” for encrypted replication.

  1. You would use user “rep1” for replication in the SAME Amazon EC2 region (possibly different availability zones).
  2. You would use user “rep2” for replication between DIFFERENT Amazon EC2 regions, over the Internet.

Example syntax on how to create “unencrypted replication” user is offered here as an example to illustrate the difference from “encrypted replication” user.

 User "rep2" will be used for communication between different EC2 regions. We need to create such user with the following syntax:

Test and make sure test user can login to localhost using SSL:

 If above works, make sure that the same works via network interface by using internal DNS name or internal IP address of the server:

Above tests must pass before you can continue. Fix the errors, if any.

Once you can login to mySQL as encrypted user “rep2”, try the following:

If you see a value next to “Ssl_cipher”, then your connection is encrypted. mySQL DB server is now capable of encrypting traffic using SSL with various clients. You can still use the same server for “regular” unencrypted traffic.

To use SSL encryption, client requests encrypted connection (via —ssl-ca= parameter on command line).
mysql users created with “REQUIRE SSL” in GRANT statement can ONLY connect via SSL. Unencrypted connections will be rejected.

blog comments powered by Disqus