Just Another ITS Systems Guy

There are four basic things you need to do when attempting to set up encrypted MySQL connections.

1. Make sure your MySQL installation is configured with SSL.
2. Create a set of certificates for your master, your slave, and your client(s).
3. Configure your master and slave my.cnf with the correct ssl-* options.
4. Configure the replication with the SSL options to CHANGE MASTER.

First, let’s check to make sure our installation supports SSL.

master [localhost] {msandbox} ((none)) >  show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED | 
| have_ssl      | DISABLED | 
| ssl_ca        |          | 
| ssl_capath    |          | 
| ssl_cert      |          | 
| ssl_cipher    |          | 
| ssl_key       |          | 
+---------------+----------+
7 rows in set (0.00 sec)

So we see here that SSL is disabled. Bummer. This likely happened because you don’t have the ssl option in your my.cnf. Add it to your configuration and restart your mysqld instance. (There is a configuration file below that you can use as an example.)

master [localhost] {msandbox} ((none)) >  show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   | 
| have_ssl      | YES   | 
| ssl_ca        |       | 
| ssl_capath    |       | 
| ssl_cert      |       | 
| ssl_cipher    |       | 
| ssl_key       |       | 
+---------------+-------+
7 rows in set (0.00 sec)

Good, SSL is enabled and available. Now we need to set up our keys. We’ll need a certificate authority, a server key (multiple in the case of master-slave replicas) and some client keys.
I used the following to generate my own certificate authority and self-signed certificates.

#!/bin/sh
# Generate test SSL keys

mkdir ../certs

cd ../certs

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -md5 -days 1000 -key ca-key.pem \
        -subj "/C=US/ST=Texas/O=My Org/OU=Test/CN=CA" > ca-cert.pem

for target in client server
do
  openssl req -newkey rsa:1024 -md5 -days 1000 -nodes -keyout $target-key.pem \
          -subj "/C=US/ST=Texas/O=My Org/OU=Test/CN=$target" > $target-req.pem
          
  openssl x509 -req -in $target-req.pem -days 1000 -md5 -CA ca-cert.pem \
          -CAkey ca-key.pem -set_serial 01 > $target-cert.pem
done

cd ..
chgrp -R mysql certs/

So what this gets you is:

:;  ls -l certs/
total 32
-rw-r----- 1 travis staff 1598 May 14 16:31 ca-cert.pem
-rw-r----- 1 travis staff 1675 May 14 16:31 ca-key.pem
-rw-r----- 1 travis staff 1086 May 14 16:31 client-cert.pem
-rw-r----- 1 travis staff  891 May 14 16:31 client-key.pem
-rw-r----- 1 travis staff  692 May 14 16:31 client-req.pem
-rw-r----- 1 travis staff 1086 May 14 16:31 server-cert.pem
-rw-r----- 1 travis staff  887 May 14 16:31 server-key.pem
-rw-r----- 1 travis staff  692 May 14 16:31 server-req.pem

Next, configure the master’s my.cnf. I’m using MySQL Sandbox for this test, so the config should be appropriate for that. You might have to modify accordingly to run outside of a Sandbox.

client]
user            = msandbox
password        = msandbox
port            = 31281
socket          = /tmp/mysql_sandbox31281.sock
ssl-capath                      = /home/travis/sandboxes/cat1_test/certs
ssl-ca                          = /home/travis/sandboxes/cat1_test/certs/ca-cert.pem
ssl-cert                        = /home/travis/sandboxes/cat1_test/certs/client-cert.pem
ssl-key                         = /home/travis/sandboxes/cat1_test/certs/client-key.pem

[mysqld]
user                            = travis
port                            = 31281
socket                          = /tmp/mysql_sandbox31281.sock
basedir                         = /home/travis/opt/mysql/5.0.76
datadir                         = /home/travis/sandboxes/cat1_test/master/data
pid-file                        = /home/travis/sandboxes/cat1_test/master/data/mysql_sandbox31281.pid
#log-slow-queries               = /home/travis/sandboxes/cat1_test/master/data/msandbox-slow.log
#log                            = /home/travis/sandboxes/cat1_test/master/data/msandbox.log
#
# additional options passed through 'my_clause' 
#
log-bin=mysql-bin
server-id=1
log-error                       = /home/travis/sandboxes/cat1_test/master/data/msandbox.err
ssl
ssl-capath                      = /home/travis/sandboxes/cat1_test/certs
ssl-ca                          = /home/travis/sandboxes/cat1_test/certs/ca-cert.pem
ssl-cert                        = /home/travis/sandboxes/cat1_test/certs/server-cert.pem
ssl-key                         = /home/travis/sandboxes/cat1_test/certs/server-key.pem

And the corresponding slave configuration:

[client]
user            = msandbox
password        = msandbox
port            = 31282
socket          = /tmp/mysql_sandbox31282.sock
ssl
ssl-capath                      = /home/travis/sandboxes/cat1_test/certs
ssl-ca                          = /home/travis/sandboxes/cat1_test/certs/ca-cert.pem
ssl-cert                        = /home/travis/sandboxes/cat1_test/certs/client-cert.pem
ssl-key                         = /home/travis/sandboxes/cat1_test/certs/client-key.pem

[mysqld]
user                            = travis
port                            = 31282
socket                          = /tmp/mysql_sandbox31282.sock
basedir                         = /home/travis/opt/mysql/5.0.76
datadir                         = /home/travis/sandboxes/cat1_test/node1/data
pid-file                        = /home/travis/sandboxes/cat1_test/node1/data/mysql_sandbox31282.pid
#log-slow-queries               = /home/travis/sandboxes/cat1_test/node1/data/msandbox-slow.log
#log                            = /home/travis/sandboxes/cat1_test/node1/data/msandbox.log
#
# additional options passed through 'my_clause' 
#
server-id=101
report-host=SBslave1
report-port=31281
log-bin=mysql-bin
log-error                       = /home/travis/sandboxes/cat1_test/node1/data/msandbox.err
ssl
ssl-capath                      = /home/travis/sandboxes/cat1_test/certs
ssl-ca                          = /home/travis/sandboxes/cat1_test/certs/ca-cert.pem
ssl-cert                        = /home/travis/sandboxes/cat1_test/certs/server-cert.pem
ssl-key                         = /home/travis/sandboxes/cat1_test/certs/server-key.pem

Since I’m being lazy for this test, note that the server key is used in both the master and slave [mysqld] sections. Ordinarily you would have a server key for each mysqld instance.

Now, when you start up your mysql client you either need to specify all the ssl-* options on the command line or be lazy and refer to one of the above my.cnf files. Because we defined a [client] section, it should just work.

Again, I’m using a Sandbox, so we start the client with the generated script. It looks like this:

export LD_LIBRARY_PATH=/home/travis/opt/mysql/5.0.76/lib:/home/travis/opt/mysql/5.0.76/lib/mysql:$LD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=/home/travis/opt/mysql/5.0.76/lib:/home/travis/opt/mysql/5.0.76/lib/mysql:$DYLD_LIBRARY_PATH
SBDIR="/home/travis/sandboxes/cat1_test/master"
BASEDIR=/home/travis/opt/mysql/5.0.76
MYSQL="$BASEDIR/bin/mysql"
PIDFILE="$SBDIR/data/mysql_sandbox31281.pid"
if [ -f $PIDFILE ]
then
    $MYSQL --defaults-file=$SBDIR/my.sandbox.cnf $MYCLIENT_OPTIONS "$@"
fi

When I run this I get logged into my Sandbox master and I can check that SSL is working on my connection.

:;  ./m
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 89228
Server version: 5.0.76-enterprise-gpl-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

master [localhost] {msandbox} ((none)) >  show variables like '%ssl%';
+---------------+--------------------------------------------------------+
| Variable_name | Value                                                  |
+---------------+--------------------------------------------------------+
| have_openssl  | YES                                                    | 
| have_ssl      | YES                                                    | 
| ssl_ca        | /home/travis/sandboxes/cat1_test/certs/ca-cert.pem     | 
| ssl_capath    | /home/travis/sandboxes/cat1_test/certs                 | 
| ssl_cert      | /home/travis/sandboxes/cat1_test/certs/server-cert.pem | 
| ssl_cipher    |                                                        | 
| ssl_key       | /home/travis/sandboxes/cat1_test/certs/server-key.pem  | 
+---------------+--------------------------------------------------------+
7 rows in set (0.00 sec)

Alternatively, you can start the command line client with the following options:

--ssl-capath=/usr/local/mysql/certs --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Finally, we need to configure the replication to use SSL. I will assume you understand how to set up replication properly and you have it already working on your master-slave pairs.

Ok, so by now you should have your master and slave my.cnf configured with all the SSL variables. On your master, configure the replication user to require SSL.

GRANT REPLICATION SLAVE 
ON *.* TO 'msandbox'@'%' 
IDENTIFIED BY 'msandbox' REQUIRE SSL;

On your slave, issue the CHANGE MASTER command.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'msandbox',
MASTER_PASSWORD = 'msandbox',
MASTER_PORT = 31281,
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 98,
MASTER_SSL = 1,
MASTER_SSL_CA = '/home/travis/sandboxes/cat1_test/certs/ca-cert.pem',
MASTER_SSL_CERT = '/home/travis/sandboxes/cat1_test/certs/client-cert.pem',
MASTER_SSL_KEY = '/home/travis/sandboxes/cat1_test/certs/client-key.pem';
START SLAVE;

And hopefully, if things went well, you’ll see happiness when you issue a SHOW SLAVE STATUS on the slave. Mine looks like:

slave1 [localhost] {msandbox} ((none)) > show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: msandbox
                Master_Port: 31281
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000016
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysql_sandbox31282-relay-bin.001746
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000016
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /home/travis/sandboxes/cat1_test/certs/ca-cert.pem
         Master_SSL_CA_Path: 
            Master_SSL_Cert: /home/travis/sandboxes/cat1_test/certs/client-cert.pem
          Master_SSL_Cipher: 
             Master_SSL_Key: /home/travis/sandboxes/cat1_test/certs/client-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Ha ha! Further success on the Linux -> Active Directory integration front. I got SSL working for the underlying ldap bind user. What’s this mean? Protection of the directory information over the wire as it travels from the domain controller to the client host where it will be used.

So what’s the necessary setup bits?  There are three options that need to be added to the ldap.conf that I originally came up with.  They are:

ssl yes
tls_cacertfile /etc/ssl/certs/ca_bundle.crt
tls_checkpeer no
uri ldaps://austin.utexas.edu/ (this is a modification from the previous config)

The tls_cacertfile defines the location of the file that contains the certificate authority information used to create the SSL certificate on the Active Directory domain controllers. You need this to verify the authenticity of the dc’s. The file should be .pem formatted and must be converted from the file you retrieve from the internal certificate authority at UT.

Once you’ve downloaded the file, you get it in the DER format which needs to be converted using something like the following.

openssl x509 -in downloadedcert.cer -inform DER -out rootca.pem -outform PEM

Next, you copy the contents of the rootca.pem to the tls_cacertfile file.

Once you’ve configured the ldap.conf with the updated options, you should now be accessing LDAP over SSL. You’ll have to verify this by running something like wireshark and watching the tcp traffic going across the wire. It’ll look something like the stream in the image above.

If you see errors in /var/log/messages that look like the following, then you’ve got something wrong in your configuration still.

Jun  3 14:09:49 fedex getent: nss_ldap: failed to bind to LDAP server ldaps://austin.utexas.edu/: Can't contact LDAP server
Jun  3 14:09:49 fedex getent: nss_ldap: reconnecting to LDAP server (sleeping 4 seconds)...
Jun  3 14:09:53 fedex getent: nss_ldap: failed to bind to LDAP server ldaps://austin.utexas.edu/: Can't contact LDAP server
Jun  3 14:09:53 fedex getent: nss_ldap: reconnecting to LDAP server (sleeping 8 seconds)...
Jun  3 14:10:01 fedex getent: nss_ldap: failed to bind to LDAP server ldaps://austin.utexas.edu/: Can't contact LDAP server
Jun  3 14:10:01 fedex getent: nss_ldap: reconnecting to LDAP server (sleeping 16 seconds)...

Now that we have SSL tackled, time to get group lookups working. I’ll leave that for another posting.

One of the things I’ve been tasked with implementing is support for serving Category 1 data from our MySQL servers.  Historically, this has been shied away from because of the complexity associated with managing the SSL certificates for all of the potential clients.  This came up again after I took over.  There are customers inside the university that want it, but don’t necessarily have the resources to implement MySQL themselves (in a safe and secure manner).  No biggie.  The support for it is certainly in the code and we can certainly do it given enough time to implement and test.

As part of the testing, I began using MySQL Sandbox to do the rapid deployment of my test areas.  This is a sweet piece of software and certainly makes deployment of test environments easy and rapid.  I ended up with a master->slave setup in a few minutes deployed out of my home directory.  Easy.  Next step was to get the certificates made.  Because this is only a test environment, I made some self-signed ones; no need to purchase any out of the pool that UT has access to if I’m just going to throw them away.

Using the info at https://kb.mysql.com/view.php?id=6566 (MySQL support contract required), I got the master and slave configured pretty quickly.  But, one problem.  As soon as I issued the change master command, my replication (that WAS working) began failing.  And here’s the frustrating part:  there’s no real way to see why the connection is failing.  There’s nothing in the logs.  There’s nothing in the warnings.  It just sits there continually trying to connect to the master.

After staring at it for a few hours trying different options, I ended up opening a ticket with MySQL about it and sent up all my config information.  I got the results back this morning.  A typo.  A simple typo in defining the path to one of my certificate files.  Gah.  I can’t believe I missed that, but that’s what I get for not taking a break and coming back with a fresh look.  Glad it was simple though.

Now we’re one step closer to getting MySQL to meet the minimum requirements for storing category 1 data.  Next step:  get the client (e.g. php on the webserver) working!

Once I get everything working, I’ll probably put up a full how-to.