Configuring MySQL / MariaDB for SSL Connections

Submitted by Tom Thorp on Saturday, August 13, 2016 - 15:48
Modified on Wednesday, August 1, 2018 - 02:50
SSL Key
The following is a step-by-step guide on how to set up MySQL / MariaDB for SSL connectivity. 
 

Creation of Certificates and Keys

Using OpenSSL, we will need to create three SSL certificates and keys : 1) Certificate Authority; 2) Server; and 3) Client. The certificates and keys will form the heart of our configuration. 
As outlined in (1), we create the certificates and keys using the following procedure : (Note: take care that the CN value in the Server and Client 'must be different from the CN value in the Certificate Authority.) 
# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA Certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
-key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 \
-out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 \
-out client-cert.pem
Finally, verify certificates so that they will authenticate when used.
shell> openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
You now have a set of files which can be used with MySQL / MariaDB in the following way : 
  • ca.pem - The 'Certificate Authority' certificate. Use this as the argument to --ssl-ca for both the Server and Client. (If CA is used, it must be the same for both sides.)
  • server-cert.pem, server-key.pem - The 'Server' certificate and key. Use these as the arguments to --ssl-cert and --ssl-key on the server side. 
  • client-cert.pem, client-key.pem - The 'Client' certificate and key. User these as the arguments to --ssl-cert and --ssl-key on the client side. 

Configuring a MySQL Account for SSL Access

Setting up a MySQL Account for SSL access uses the same Grant syntax, except for one key difference - the 'Require' clause. The Require clause tells MySQL that this account will only authenticate via an encryption algorithm. An example is below : 
grant select,insert,update,delete on db.* to ssluser@localhost identified by 'ssluser' require SSL;
Grants 'select', 'insert', 'update' & 'delete' access on database 'db' to database user 'ssluser' with password of 'ssluser', but only authenticate via SSL. 
 

Configuring MySQL Server and MySQL Client

MySQL Server uses a configuration file to determine key startup parameters on startup. On a Windows machine, this would generally be your 'mysql.ini' or 'my.ini' file. On a Linux machine, generally this would be your 'my.cnf' file. It is all dependent on whether you installed a compiled solution, or you customised the install process. 
Within the configuration file, there are two sections that are outlined : 
  • [MYSQLD] - This is the section related to the MySQL server
  • [MYSQL] - This is the section related to the MySQL client
To set up SSL access on MySQL server, add in the following lines underneath the [MYSQLD] section 
[MYSQLD]
ssl-ca = <path_to_certificates>/ca.pem
ssl-cert = <path_to_certificates>/server-cert.pem
ssl-key = <path_to_certificates>/server-key.pem
To set up MySQL Client for SSL access, add the following lines under the [MYSQL] section.
[MYSQL]
ssl-ca = <path_to_certificates>/ca.pem
ssl-cert = <path_to_certificates>/client-cert.pem
ssl-key = <path_to_certificates>/client-key.pem
To make the changes active, restart the MySQL service. 

 

(1) Creating SSL Files Using OpenSSL - http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html

 

 

About the author

Tom Thorp
Tom Thorp is an IT Consultant living in Miami on Queensland's Gold Coast. With over 30+ years working in the IT industry, Tom's experience is a broad canvas. The IT services Tom provides to his clients, includes :
 
Website development and hosting
Database Administration
Server Administration (Windows, Linux, Apple)
PABX Hosting and Administration
Helpdesk Support (end-user & technical).
 
 
Categories