There are four basic things you need to do when attempting to set up encrypted MySQL connections.
- Make sure your MySQL installation is configured with SSL.
- Create a set of certificates for your master, your slave, and your client(s).
- Configure your master and slave my.cnf with the correct ssl-* options.
- 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)
Does encrypted SQL connections stop sql injection when it happens or is that not possibly related – the two issues?
@Faisal: this does not prevent sql injection. This is strictly for the encryption of the transport layer between the MySQL server and the MySQL client to prevent sniffing of the data over the wire.