MariaDB with Secure Connections. – o6asan's soliloquy.

同一記事の日本語版

These days, I was working hard about using MariaDB with Secure Connections. First, I did SHOW VARIABLES LIKE 'have_ssl'; and got this:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+

DISABLED means that the server was compiled with TLS support, but was not started with TLS support. So I can use MariaDB with Secure Connections on my server.

If my Let’s Encrypt certs are RSA ones, I might be able to use them for MariaDB. But they are not RSA ones and MariaDB seems to accept RSA certs only. So I created Self-signed certs for this purpose. First, I created them by the next openssl commands. These certs worked well with MariaDB and WordPress, but not well with phpMyAdmin.

>openssl genrsa 2048 > cakey.pem
>openssl req -new -x509 -nodes -days 3650 -key cakey.pem -out my_ca.crt
>openssl req -newkey rsa:2048 -days 3650 -nodes -keyout MySQL.key -out MySQL.csr
>openssl rsa -in MySQL.key -out MySQL.key
>openssl x509 -req -in MySQL.csr -days 3650 -CA my_ca.crt -CAkey cakey.pem -set_serial 01 -out MySQL.crt

Therefore, I recreated the certs by the following commands. This time, I did Common Name is localhost because phpMyAdmin gave errors. Maybe this was caused by all MariaDB users at localhost.

>copy index.txt+
>echo 01 > serial
>openssl req -new -keyout privatecakey.pem -out careq.pem
>openssl ca -selfsign -in careq.pem -extensions v3_ca -out cacert.pem
>copy cacert.pem my_ca.crt
>openssl req -new -keyout MySQL.key -out MySQL.csr
>openssl ca -in MySQL.csr -out MySQL.crt
>copy MySQL.key cp_MySQL.key
>openssl rsa <cp_MySQL.key> MySQL.key
>openssl rsa -in MySQL.key -out MySQL.key

The line openssl rsa -in MySQL.key -out MySQL.key changes key’s PEM header because of this.

I added the following three lines to the [mysqld] area of the my.ini file. Reboot MariaDB.

ssl_ca = /path/to/my_ca.crt
ssl_cert = /path/to/MySQL.crt
ssl_key = /path/to/MySQL.key

I got the below result by SHOW VARIABLES LIKE '%ssl%';:

+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| have_openssl        | NO                 |
| have_ssl            | YES                |
| ssl_ca              | /path/to/my_ca.crt |
| ssl_capath          |                    |
| ssl_cert            | /path/to/MySQL.crt  |
| ssl_cipher          |                    |
| ssl_crl             |                    |
| ssl_crlpath         |                    |
| ssl_key             | /path/to/MySQL.key  |
| version_ssl_library | YaSSL 2.4.2        |
+---------------------+--------------------+

If your MariaDB is other than Windows version, you will have ‘yes’ as the value of have_openssl. For details, see SSL/TLS System Variables.

The next post I will write SQL Secure Connections about phpMyAdmin and WordPress.

   I forgot to write. Each version is:
   MariaDB 10.2.9 win 32-bit
   phpMyAdmin 4.7.4
   WordPress 4.8.2

Leave a Reply Cancel reply