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

Comments

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 cert.pem and 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;
FLUSH PRIVILEGES;

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:


spring.datasource.url=jdbc:mariadb://IP:3306/myDB?useSSL=true&trustServerCertificate=true&serverSslCert=/certificates/peer.pem&disableSslHostnameVerification=true
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

But I am getting an error:


Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

Versions:

mariadb: mysql Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1
mariadb-java-client: 2.7.2
spring boot: 2.3.4

Hello Pete, I am not able to start mariadb. Always get the "Unable to get certificate from..." Error for the ssl-cert value. I created a directory /mysql_certs netxt to /etc/. I gtanted ownership to mysql:mysql for directory and all files. Also granted permissions, but even with 777...always get this error.
I used severel tutorials to create the certs.
Onece I got it to run but had an error because the CNs of all certs were the same, which leads to a typical error. I created new ones and since I always get this error.

This doesn't sound like an easy one to troubleshoot. Some suggestions: verify which user account runs the MySQL server process, with something like `ps aux | grep mysql` and/or `ps aux | grep maria`, and then run `chown -R mysql:mysql /etc/mysql_certs`. Depending on your config, if MySQL is running chrooted then you might need to put the certificate and key inside the MySQL folders rather than next to them. Finally, this article was written a few years back. It's possible that newer versions of MySQL or MariaDB expect the files in a different format. Maybe check the documentation to see what format is expected, and use the openssl command line tools to check the format of the files and convert them if necessary.

I tied all your suggestions, but no luck. I located de certs under /etc/mysql/mariadb.conf.d/mysql_certs/*.pem, double checked the mysql user and had a look to the docu on mariadb.com....no luck. All the creation commands looked very similar.
Than I decided to delet the certs again. Recreated it with the exact commands from docu (see link). And also locaded it like shown on other docu page for server config: /etc/mysql/certs/*.pem. I set ownership to dir and files for mysql:mysql and restarted whole engine. Then...magic...executed 'systemctl restart mariadb' and no error occured. I am not sure if it was the recreation of certs or the relocation one step closer to the root.
Thanks a lot for your answer...
https://mariadb.com/kb/en/certificate-creation-with-openssl/

Add new comment

CAPTCHA