Ubuntu

Published on May. 31, 2018 by Gabriel Bordeaux

Introduction

This article is a simple how-to on configuring MySQL to accept remote connections with SSL/TLS encryption.

Install packages on the MySQL server

apt-get update
apt-get install --yes mysql-server openssl

Create an SSL certificate

Let's create the CA key and certificate:

mkdir ~/cert && cd ~/cert

openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -key ca-key.pem -subj "/CN=certificate-authority" > ca-cert.pem

You can now create the private key for the server:

openssl req -sha1 -newkey rsa:2048 -nodes -keyout server-key.pem -subj "/CN=mysql-server" > server-req.pem
openssl x509 -sha1 -req -in server-req.pem  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl rsa -in server-key.pem -out server-key.pem

Now, let's create the client key and certificate:

openssl req -sha1 -newkey rsa:2048 -nodes -keyout client-key.pem -subj "/CN=mysql-client" > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem

At this point, you should have the following files in the directory:

$ ls
ca-cert.pem  ca-key.pem  client-cert.pem  client-key.pem  client-req.pem  server-cert.pem  server-key.pem  server-req.pem

Let's now move the files to their final destination:

mkdir -p /etc/mysql/ssl
cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql/ssl
chown -R mysql.mysql /etc/mysql/ssl
chmod -R 700 /etc/mysql/ssl

Edit MySQL configuration

You need to edit my.cnf to document the certificate:

nano /etc/mysql/my.cnf

and add:

[mysqld]
bind-address = *
ssl-ca =       /etc/mysql/ssl/ca-cert.pem
ssl-cert =     /etc/mysql/ssl/server-cert.pem
ssl-key =      /etc/mysql/ssl/server-key.pem

Then restart MySQL:

/etc/init.d/mysql restart

You can take a quick look at the logs to make sure it started properly:

tail /var/log/mysql/error.log
# 2018-05-22 13:57:46 139653708935744 [Note] InnoDB: Completed initialization of buffer pool
# 2018-05-22 13:57:46 139653708935744 [Note] InnoDB: Highest supported file format is Barracuda.
# 2018-05-22 13:57:46 139653708935744 [Note] InnoDB: 128 rollback segment(s) are active.
# 2018-05-22 13:57:46 139653708935744 [Note] InnoDB: Waiting for purge to start
# 2018-05-22 13:57:46 139653708935744 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.1 started; log sequence number 1616945
# 2018-05-22 13:57:46 139653067826944 [Note] InnoDB: Dumping buffer pool(s) not yet started
# 2018-05-22 13:57:46 139653708935744 [Note] Plugin 'FEEDBACK' is disabled.
# 2018-05-22 13:57:46 139653708935744 [Note] Server socket created on IP: '::'.
# 2018-05-22 13:57:46 139653708935744 [Note] /usr/sbin/mysqld: ready for connections.
# Version: '10.1.26-MariaDB-0+deb9u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian 9.1

You cna also check SSL status from MySQL:

mysql -u root -e "SHOW GLOBAL VARIABLES LIKE 'have_%ssl';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | NO    |
| have_ssl      | YES   |
+---------------+-------+

Create a new user

mysql -u root

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON *.* TO remote_user@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

Setup the client

On the server used to connect to MySQL remotely, install MySQL's client:

apt-get update
apt-get install --yes mysql-client

Copy the certificates:

mkdir /etc/mysql/client-ssl && cd /etc/mysql/client-ssl

# Copy the following files: client-cert.pem, client-key.pem and ca-cert.pem
scp root@REMOTE_SERVER_IP:~/cert/{client-cert.pem,client-key.pem,ca-cert.pem} ./

chmod -R 700 /etc/mysql/client-ssl

You need to edit my.cnf to document the certificate:

nano /etc/mysql/my.cnf

and add:

[client]
ssl-ca =   /etc/mysql/client-ssl/ca-cert.pem
ssl-cert = /etc/mysql/client-ssl/client-cert.pem
ssl-key =  /etc/mysql/client-ssl/client-key.pem

You can now connect to MySQL using the SSL certificate:

mysql -h REMOTE_SERVER_IP -u remote_user -p"my_password"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Bye