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.pemNote 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.pemSubstitute
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 tcpReplace
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 likemysql -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 keySimilarly, 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