Categories: Windows

MariaDB with Secure Connections.

   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

o6asan

Recent Posts

I completed my WordPress to Sub-domain.

About a week ago, I finally started to renew my sites, which I had been…

2 months ago

Happy New Year!

   Happy New Year! It is the beginning of a new year.    This is a year…

2 years ago

My Web server supports TLSv1.3 now.

   Apache 2.4.37 from Apache Lounge supported TLSv1.3, so I enabled TLSv1.3 on my Web server…

2 years ago

I made an external 1seg antenna for SC-02H.

I made it without understanding, but I think I got some good results, so I'll…

3 years ago

phpMyAdmin uses Twig by default from the version 4.8.0.

   I updated phpMyAdmin from 4.7.9 to 4.8.0 the day before yesterday. Although they wrote that…

3 years ago

The director of ‘Hotaru no haka’ Takahata Isao (高畑勲) passed away.

   On Apr. 5 in Japan time, the director of 'Hotaru no haka' Takahata Isao (高畑勲)…

3 years ago

This website uses cookies.