Site icon GetPageSpeed

Setup SSL connections in Percona MySQL 5.6

Danila Vershinin
MySQL

MySQL

Note about MySQL versions

This post maybe applicable to other MySQL services. I just thought I’d state it clearly that this tutorial was tested with Percona MySQL 5.6.
Note that MySQL 5.7 series will generate self signed SSL certificates for you automatically at startup which may be sufficient for most users. But do check my notes about paid SSL certificates option below.

Times are, you’d need remote connectivity to your MySQL server instance. As per client of mine the requirement was due to:

… most of the analytic services I’ve worked with like Tableau/Google/Microstrategy work this way since it allows their services to scan and cache data effectively vs. hitting your DB directly on every query

While this sounded like a bummer to me (I mostly preferred to have MySQL listen on localhost and work with it using SSH tunnel, if I needed to use MySQL GUI tools), I realised that there are third party services that won’t do SSH tunnelling for connecting to your MySQL instance.

Allowing remote access to MySQL without encryption is a bad idea. So let’s setup some MySQL SSL, shall we?

SSL certificates for MySQL

As with any SSL (TLS, really), you need some SSL certificates in the first place. You have several options on where to get those.

1. Paid SSL Certificates

You may want to use regularly purchased SSL certificates.

The benefit for using those as compared to self signed ones, is clear certificate trust chain.
If you use certificates generated on the server, your Certification Authority would be self signed.
MySQL 5.7 would issue a warning during its startup:

[Warning] CA certificate ca-cert.pem is self signed

To avoid this, you may want to purchase SSL certificates.

2. LetsEncrypt

You may be itchy to use LetsEncrypt certificates for MySQL SSL setup. However, LetsEncrypt certs are valid for 90 days and have to be renewed.

MySQL cannot reload SSL certificates dynamically without restart, so you’re risking some short downtime periods every 90 days just for pushing LetsEncrypt use in every area 🙂

3. Your own SSL

Using SSL certificates generated elsewhere is typically not required for MySQL SSL setup. Generating SSL certificates of your own is the way to go if strict security isn’t required.

Without wasting your time, I give you the script to quickly generate the necessary SSL certificates.

mysql-setup-ssl.sh

#!/bin/bash

cd /var/lib/mysql
# 1. generate your own CA
openssl genrsa 2048 > ca-key.pem
# 2. generate server key / cert
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem -subj "/CN=$(hostname) CA" > ca-cert.pem
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem -subj "/CN=$(hostname)" > server-req.pem
openssl rsa -in server-key.pem -out server-key.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
# 3. generate client key / cert
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem -subj "/CN=$(hostname)-client" > client-req.pem
openssl rsa -in client-key.pem -out client-key.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
chown mysql *.pem
chmod 0600 *.pem

The script above generates:

Save it as mysql-setup-ssl.sh. You can run with e.g. ./mysql-setup-ssl.sh and it won’t require any input from you. All the certificates will be saved in MySQL data directory, /var/lib/mysql.

Now you should specify the names of your generated certificates in MySQL configuration and specify secure TLS versions. In /etc/my.cnf:

[mysqld]
...
tls_version=TLSv1.1,TLSv1.2
ssl-ca=ca-cert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

You would also likely need to remove existing bind-address directive from your configuration in order for MySQL to listen on all network interfaces.

Restart your service, or better, reload your MySQL configuration in a safe way.

Now you can connect to MySQL with mysql and check SSL configuration status with:

SHOW VARIABLES LIKE '%ssl%';

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

Configuring third party services to use SSL straightforward is easy (if they do support SSL). Simply download the .pem files from /var/lib/mysql and upload/use them in your remote MySQL client/service:

Next, you can create a separate MySQL user with remote connectivity:

mysql -e "CREATE USER 'username'@'%' IDENTIFIED BY 'secret';"

Now you can test SSL connectivity from your GUI client and if things work correctly, allow the connection only using the generated client certificate:

mysql -e "GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SUBJECT '/CN=$(hostname)-client' AND ISSUER '/CN=$(hostname) CA';"

Confirm the change with:

mysql -e "SHOW GRANTS FOR 'username'@'%';"

At this point, the MySQL user can connect from any host. If the remote service in question has a known IP address, it is best to create a user entry with specific host definition:

mysql -e "CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'secret';"

Needless to say, you also need to GRANT actual permissions to specific database that you want this user to work with:

mysql -e "GRANT ALL PRIVILEGES ON example. * TO 'username'@'1.2.3.4';"

Now the user is limited to connect from specific IP, and only over SSL, only with generated client side SSL certificate.

What else can you wish for? 🙂

Exit mobile version