Setting up SSL on Mysql Server

  • by John
  • Comments Off on Setting up SSL on Mysql Server
  • MySQL

First we need to create the SSL Certificates. I’m using Ubuntu 16.04 and MySQL 5.6 which do not like the certificates to be created as anyting other than sha1 and the original RSA.

Use the following commands to create the certificates in the correct format.


# Generate a CA key and certificate with SHA1 digest
openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem


# Create server key and certficate with SHA1 digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -sha1 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl rsa -in server-key.pem -out server-key.pem


# Create client key and certificate with SHA digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem

You then need to move the server keys over to /var/lib/mysql and change the owner to mysql.

chmod mysql:mysql /var/lib/mysql/*.pem

Now create the MySQL user to use for ssl connections.

CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ssl_user'@'%'
REQUIRE X509;

Now We need to update the /etc/mysql/my.cnf file to include the certificates.

under the [mysqld] section add the following.


ssl-ca=/var/lib/mysql/ca-cert.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

Then restart the mysql server.

service mysql restart

To test the connection use the following command to connect using the new client certificates.

mysql -ussl_user -p --ssl-ca=/var/lib/mysql/ca-cert.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem

once logged in you can issue the following command to check SSL is enabled.

status

Which should produce the following indicating you have an SSL connection


--------------
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper

Connection id: 26
Current database:
Current user: ssl_user@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 7 min 39 sec

Threads: 1 Questions: 1001 Slow queries: 0 Opens: 530 Flush tables: 1 Open tables: 50 Queries per second avg: 2.180
--------------

If you cannot connect using SSL then log in as root and issue the following command.

show variables like '%ssl%';

That should output the following table.


+---------------+--------------------------------+
| Variable_name | Value |
+---------------+--------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /var/lib/mysql/server-key.pem |
+---------------+--------------------------------+

If the have_openssl or have_ssl says DISABLED then it could be something wrong with the certificates so make sure they are all owned by the mysql user (see the chown command above).