How to connect to MariaDB secure server from phpMyAdmin and WordPress.


   After “MariaDB with Secure Connections.”, my SQL server has Secure Connections. Now I have to add some options to phpMyAdmin and WordPress settings.
   Their versions are MariaDB 10.2.9 win 32-bit, phpMyAdmin 4.7.4 and WordPress 4.8.2 on Windows 7 32-bit HE SP1.

   Before this I confirmed SQL sever status when I connected to it by using CA information. I logged in the server by mysql -u root -p --ssl-ca=/path/to/my_ca.crt and did s. I had the result below:

mysql  Ver 15.1 Distrib 10.2.9-MariaDB, for Win32 (AMD64)

Connection id:          221
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.2.9-MariaDB-log binary distribution
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp932
Conn.  characterset:    cp932
TCP port:               3306
Uptime:                 25 min 57 sec

Threads: 7  Questions: 14327  Slow queries: 18  Opens: 215  Flush tables: 1  Open tables: 209  Queries per second avg: 9.201

   DHE-RSA-AES256-SHA is TLS v1.0. As I set ssl_cipher=TLSv1.2, the server gave have_ssl|DISABLED. I cannot use TLS v1.2 because of MariaDB Windows version built with YaSSL.
   Next, I change my WordPress SQL user account settings on the server which requires TLS whenever the user connects to it.
GRANT SELECT,INSERT,UPDATE,DELETE on *.* to wpuser@localhost IDENTIFIED by 'password' require ssl;
   I ended working on the command line.

[About phpMyAdmin]
   Add the following lines to the file. See cfg_Servers_ssl.
   In my case, the file already had $cfg['Servers'][$i]['ssl_verify'] = false; because of this. I change this ‘false’ to ‘true’.
   $cfg['Servers'][$i]['ssl'] = true;
   $cfg['Servers'][$i]['ssl_key'] = '/path/to/MySQL.key';
   $cfg['Servers'][$i]['ssl_cert'] = '/path/to/MySQL.crt';
   $cfg['Servers'][$i]['ssl_ca'] = '/path/to/my_ca.crt';
   $cfg['Servers'][$i]['ssl_verify'] = true;

   I logged in phpMyAdmin as wpuser@localhost. It accepted me but required two more options. So I added the next lines to the file.
   $cfg['Servers'][$i]['ssl_ca_path'] = '/path/to/';
   $cfg['Servers'][$i]['ssl_ciphers'] = 'DHE-RSA-AES256-SHA';
   Now I can connect to MariaDB secure server as wpuser@localhost from phpMyAdmin. Of course, you can set a user as ‘require ssl’ privilege by phpMyAdmin not by command line.

[About WordPress]
   See “Secure DB Connection”.
   Add define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL); to the wp-config.php file. This time my CA cert is self-signed one, so I also added define('MYSQL_SSL_CA', '/path/to/my_ca.crt');.

   That’s it!!

Leave a Reply

Your email address will not be published. Required fields are marked *