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
/etc/mysql/my.cnf and add the following lines to the
bind-address = 126.96.36.199 ssl-cert=/etc/mysql/fullchain.pem ssl-key=/etc/mysql/privkey.pem
188.8.131.52 with the IP address of your server.
systemctl restart mysql
Add firewall exception
ufw allow from 184.108.40.206 to 220.127.116.11 port 3306 proto tcp
18.104.22.168 as required. You can replace
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
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
ANY. You may need to execute
FLUSH PRIVILEGES; afterwards.
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 22.214.171.124. Assuming that the login works, execute the command
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 3666 Current database: Current user: firstname.lastname@example.org 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: 126.96.36.199 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 --------------
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)
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
Thank you! This worked for me where other guides didn't. One thing I don't understand: for your
ssl_cert setting you have 'fullchain.pem' and no value for
ssl_ca. I thought the 'fullchain.pem' was supposed to be used for
ssl_ca and that a cert -- let's be original and call it 'cert.pem' -- was supposed to be what
ssl_cert pointed to.
Hi David, glad this post helped you out. In the example above, I used a Let’s Encrypt certificate. Let’s Encrypt automatically generates a combined certificate file, with both the site-specific certificate and the chained authority certificates all included together, in
fullchain.pem. It’s generally easier to use this than to specify the certificate authority file separately. However, you’re quite correct, if you’re using a certificate that also comes with intermediate certificate authority files, you should also specify those in your MySQL configuration. If you're using Let’s Encrypt and want to explicitly configure the local certificate and the CA separately, I believe that the file names are
chain.pem respectively. In this case, you’ll probably need to convert both of them from PKCS #8 format into PKCS #1 format.
I have MariaDB deployed as a pod into the k8s cluster.
DB is enabled with SSL mode which will not allow users/clients to login to DB without certs. So we can not use a username and password to login to DB. We must use only the certificates to do so.
User creation statement:
CREATE USER 'test'@'%' REQUIRE X509;
GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;
Note: So we are not using any password while creating users.
Login to DB:
mysql -uroot --ssl-key=/keys/peer-key.pem --ssl-cert=/certificates/peer.pem --ssl-ca=/ca/cacerts.pem
So I tried enabling SSL mode on mariadb to the spring boot app side as shown below:
But I am getting an error:
Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'
mariadb: mysql Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1
spring boot: 2.3.4
Hi Vishal, sorry to hear that you're having trouble with this. I've got no experience with Kubernetes or with Spring, so there's really not much I can do to help you, sorry! If it's working on the command line (as your example implies), that's about the limit of my knowledge...