Using SSL Connections with MySQL

To be able to use SSL for connecting to a MySQL server, the server will need to be reconfigured, and how clients access the server will need to be adjusted. First, we will modify the options file (e.g., my.cnf). We will add the following lines, substituting $MYSQL_HOME with the actual path to the MySQL basedir:

[client]

ssl-ca=$MYSQL_HOME/certs/ca-cert.pem
ssl-cert=$MYSQL_HOME/certs/client-cert.pem
ssl-key=$MYSQL_HOME/certs/client-key.pem

[mysqld]

ssl-capath=$MYSQL_HOME/certs/
ssl-ca=$MYSQL_HOME/certs/ca-cert.pem
ssl-cert=$MYSQL_HOME/certs/server-cert.pem
ssl-key=$MYSQL_HOME/certs/server-key.pem

Now we’ll start MySQL and log in and check that SSL has been enabled at the server level:

SHOW GLOBAL VARIABLES LIKE '%ssl%';

+---------------+-----------------------------------------------------+
| Variable_name | Value                                               |
+---------------+-----------------------------------------------------+
| have_openssl  | YES                                                 |
| ssl_ca        | /usr/local/mysql/certs/ca-cert.pem                  |
| ssl_capath    | /usr/local/mysql/certs/                             |
| ssl_cert      | /usr/local/mysql/certs/server-cert.pem              |
| ssl_cipher    |                                                     |
| ssl_key       | /usr/local/mysql/certs/server-key.pem               |
+---------------+-----------------------------------------------------+
6 rows in set (0.00 sec)

Now that you have the server configured to use the new SSL certificates, we need to ensure that the users are forced to use SSL to connect to the database. The GRANT statement provides the REQUIRE SSL keyword to force this:

GRANT ALL ON *.* TO 'test_user'@'localhost'
IDENTIFIED BY 'secret' REQUIRE SSL;

To test the changes, we will login with the mysql command-line client, using the –ssl-ca, –ssl-cert, and the –ssl-key options, pointing to the generated client certificate and key (see the related article in the margin for generating a certificate and key):

mysql -u test_user -p --ssl-ca=/usr/local/mysql/certs/ca-cert.pem \
--ssl-cert=/usr/local/mysql/certs/client-cert.pem \
--ssl-key=/usr/local/mysql/certs/client-key.pem

STATUS
--------------
bin/mysql Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.0

Connection id: 4
Current database:
Current user: test_user@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: less
Using outfile:
Using delimiter: ;
Server version: 5.0.24-pro-gpl-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: ../mysql-5.0.24.sock
Uptime: 21 min 43 sec

Threads: 1 Questions: 3 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.002

Here you can see that the connection is using SSL with SSL: Cipher in use is DHE-RSA-AES256-SHA.

Using SSL Connections with MySQL

Latest Posts