Monolune

How to connect from WordPress to MySQL using SSL

Comments

If you have a WordPress installation on one system, and a MySQL or MariaDB database server on another system, you may want to ensure that the data transfers between them are secure. SSL can be used to prevent eavesdropping and tampering on data. This guide will show the steps needed to configure WordPress to connect to a remote MySQL database using SSL.

Wordpress to MySQL using SSL

Assumptions

This guide makes these assumptions:

  • You already have a self signed SSL CA certificate.
  • You are on Linux.
  • The PHP that WordPress runs on uses Mysqlnd (MySQL native driver) as the driver for MySQLi. Note that this is the default for default PHP installations starting from PHP 5.4.0. (If you need to check whether or not you are using Mysqlnd, refer to this Stack Overflow question for guidance: How to know if MySQLnd is the active driver?)

The Plan

If SSL is improperly configured, WordPress may give enigmatic error messages. For this reason, we recommend that you first attempt SSL connections without WordPress to make sure that the SSL connections actually work. Here's the plan:

  1. Attempt a remote connection to the MySQL server using the MySQL client program.
  2. Attempt a remote connection to the MYSQL server using PHP.
  3. If everything succeeds so far, finally configure WordPress to use SSL.

Attempt remote connection using the MySQL client

First, attempt a connection to the remote MySQL or MariaDB server to make sure that remote connections using the SSL certificate works:

# On the command line, run:
mysql --user john --host db.example.com --ssl-ca my-ca.crt --password

If you were not able to connect to the remote database, you must first fix the issue that prevents the connection.

Attempt remote connection using PHP

Next, verify that PHP is able to connect to the remote database using SSL. Start the interactive PHP shell by running php -a. Then:

// In the interactive PHP shell:
$db = mysqli_init();
mysqli_real_connect($db, 'db.example.com', 'john', '123456', NULL, NULL, NULL, MYSQLI_CLIENT_SSL);
var_dump(mysqli_query($db, 'STATUS;'));  # Should show results if connection succeeded, not false.

If there are errors in the interactive shell, you must resolve them.

Configure WordPress to use SSL

If all goes well so far, you can be confident that WordPress database connections will be unlikely to fail once configured. Now add your self signed SSL certificate to OpenSSL's trusted store:

# On the command line, run:
sudo mv my-ca.crt /usr/local/share/ca-certificates/
sudo update-ca-certificates

The system's trust store is where WordPress will look for SSL certificates. Important: The file name of the certificate must only end with a crt extension. my-ca.crt is fine, but my-ca.pem.crt and my-ca.pem are invalid!

You must now instruct WordPress to use the system's trust store to connect to MySQL using SSL. Set the appropriate settings in the wp-config.php file of your WordPress installation:

// In wp-config.php, add:
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

At this point, WordPress will connect to the MySQL server through an SSL connection.

Discussion

As far as I am aware, the method outlined above is the simplest way to get secure connections between WordPress and a remote MySQL database. An alternative to using SSL is to use a persistent (permanent) SSH tunnel between the system running WordPress and the system running the database server. While using SSH works for securing the connection, both anecdotal evidence and (admittedly unscientific) tests have shown that the SSH option is slower than the SSL option.