Configuring MySQL/MariaDB to work with SSL/TLS

MySQL has supported TLS for TCP/IP connections for some time now, but there are a number of subtleties involved in getting it working. If you need to allow remote access to your databases, you really ought to be using TLS to protect your login details and any confidential data that might be transferred. Here are the various steps required to enable TLS in MySQL on Debian and Ubuntu. Note that MySQL still refers to everything as SSL, but it’s more correct to call it TLS as SSL is deprecated these days. The same instructions also ought to work for MariaDB.

Check TLS is compiled into MySQL

Never overlook the obvious! This is enabled by default in the packaged versions of MySQL in Debian and Ubuntu, but if you obtained MySQL elsewhere it’s worth checking.

Create/copy certificate into location

MySQL requires certificate keys in PKCS #1 format, whereas recent versions of OpenSSL default to PKCS #8 format. You’ll also need to ensure that MySQL has access to the certificate and key. If you have a certificate and key from Let’s Encrypt, here’s how to repurpose them for MySQL:

cp /etc/letsencrypt/live/example.com/fullchain.pem /etc/mysql/fullchain.pem
openssl rsa -in /etc/letsencrypt/live/example.com/privkey.pem -out /etc/mysql/privkey.pem
chmod 444 /etc/mysql/fullchain.pem
chmod 400 /etc/mysql/privkey.pem
chown mysql:mysql /etc/mysql/fullchain.pem /etc/mysql/privkey.pem

Note that you’ll probably want to use a hook to automatically do this every time that the certificate is renewed if you’re using Let’s Encrypt. If your MySQL instance runs as a different user, modify the chown command accordingly.

Update MySQL config

Edit /etc/mysql/my.cnf and add the following lines to the [mysqld] section:

bind-address = 1.2.3.4
ssl-cert=/etc/mysql/fullchain.pem
ssl-key=/etc/mysql/privkey.pem

Substitute 1.2.3.4 with the IP address of your server.

Restart MySQL

systemctl restart mysql

Add firewall exception

ufw allow from 4.3.2.1 to 1.2.3.4 port 3306 proto tcp

Replace 4.3.2.1 and 1.2.3.4 as required. You can replace 4.3.2.1 with any to allow connections from anywhere, but I don’t recommend it.

Create user with REQUIRE SSL

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'%';

Make sure to replace user, password and database with the correct values for your setup.

Alternatively, if you’re using phpMyAdmin, you can create the user as usual. Then go into the mysql.users table, edit the user, and change ssl_type to ANY. You may need to execute FLUSH PRIVILEGES; afterwards.

Test

You’re now ready to test. Log in to the server from your remote IP address using a command like mysql -u user -p -h 1.2.3.4. Assuming that the login works, execute the command \s:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Connection id:		3666
Current database:
Current user:		user@4.3.2.1
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.55-0+deb8u1 (Debian)
Protocol version:	10
Connection:		1.2.3.4 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Uptime:			2 hours 15 min 16 sec

Threads: 11  Questions: 308701  Slow queries: 0  Opens: 1758  Flush tables: 1  Open tables: 600  Queries per second avg: 38.036
--------------

Note the SSL: Cipher in use is DHE-RSA-AES256-SHA line. Test further with SHOW VARIABLES LIKE '%ssl%';:

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        |                          |
| ssl_capath    |                          |
| ssl_cert      | /etc/mysql/fullchain.pem |
| ssl_cipher    |                          |
| ssl_key       | /etc/mysql/privkey.pem   |
+---------------+--------------------------+
7 rows in set (0.03 sec)

Note that have_openssl and have_ssl are both YES. If the value is NO, MySQL was compiled without TLS support. If it’s DISABLED, TLS isn’t correctly set up.

If there are problems, check /var/log/mysql/error.log. If you forgot to convert your private key to PKCS #1 format, you may well see lines like the following:

SSL error: Unable to get private key from '/etc/mysql/privkey.pem'
170707 12:39:23 [Warning] Failed to setup SSL
170707 12:39:23 [Warning] SSL error: Unable to get private key

Similarly, if you didn’t correctly set the permissions so that the mysql user can access the certificate, you may see something like this:

SSL error: Unable to get certificate from '/etc/mysql/fullchain.pem'
170707 12:14:56 [Warning] Failed to setup SSL
170707 12:14:56 [Warning] SSL error: Unable to get certificate