Use SSL with MariaDB/MySQL

Posted on by Andy

Basically you need to create 3 different certificates:

mariadb-ca.crt (Certificate Authority CA)
mariadb-server.crt (Server certificates)
mariadb-client.crt (Client certificate)

Server and client certificate need to be signed by the same CA. Now let’s start with the CA certificate and key:

# cd /var/db/mysql
# openssl genrsa -out mariadb-CA.key 1024
# openssl req -new -x509 -extensions v3_ca -key mariadb-CA.key -days 10950 -out mariadb-CA.crt

Now that the CA part have been generated it is time to generate the server certificate and key:

# openssl genrsa -out mariadb-server.key 1024
# openssl req -new -key mariadb-server.key -out mariadb-server.csr​
# openssl x509 -req -in mariadb-server.csr -CA mariadb-CA.crt -CAkey mariadb-CA.key -CAcreateserial -out mariadb-server.crt -days 10950

Well then, let0s go to the client part, shall we:

# openssl genrsa -out mariadb-client.key 1024
# openssl req -new -key mariadb-client.key -out mariadb-client.csr​
# openssl x509 -req -in mariadb-client.csr -CA mariadb-CA.crt -CAkey mariadb-CA.key -CAcreateserial -out mariadb-client.crt -days 10950

Now that we have our certificates ready, we need to edit our my.cnf. put following lines under the [mysqld] section:

ssl-ca=/var/db/mysql/mariadb-CA.crt
ssl-cert=/var/db/mysql/mariadb-server.crt
ssl-key=/var/db/mysql/mariadb-server.key

and for the client put this when doing the conneciton:

ssl-ca=/path/to/mariadb-CA.pem
ssl-cert=/path/to/mariadb-client.crt
ssl-key=/path/to/mariadb-client.key

Last security related step (but not mandatory) is to update permissions so that only mysql has read permissions:

# chown mysql:mysql mariadb-CA.* mariadb-server.* mariadb-client.*
# chmod 640 mariadb-CA.* mariadb-server.* mariadb-client.*

Important notes:

  • Note that you need to copy the mariadb-CA.crt to the client machine
  • The CN field must be different on server and client
  • I chose a certificate period of 30 years, adopt the paths and period to your needs
  • Make sure that the certs and keys are readable by the server
  • I chose a key length of 1024-bit because the longer the key gets, the drastically slower the connections will be. (https://dzone.com/articles/ssl-performance-overhead-mysql)