Using WordPress with MySQL 8.0

If you’ve recently attempted to get WordPress and MySQL 8 to work together, you’ve likely experienced a connection error:

Error establishing a database connection.

Cause

The cause of the issue is that with MySQL 8.0, Oracle has changed the default encryption plugin. The new plugin is called caching_sha2_password. The original plugin, called mysql_native_password, which uses SHA1, is still there, and usable, it’s just not configured as the default.

When a client connects to MySQL 8.0, MySQL assumes the client to be connecting with caching_sha2_password. Many current clients are unable to perform this newer authentication mechanism.

Multiple solutions to the authentication issue

There are multiple ways to solve this problem in the short term.

Arguably, the “best” way to resolve this would be to migrate your WordPress servers to PHP 7.4 or higher. Unfortunately, PHP 7.4 isn’t yet available in many Linux distribution’s software repositories. That means that upgrading PHP, for the time being, is a painful way to get this working. Once PHP 7.4 becomes widely available in the software repositories, that will be the preferred method to solve this issue.

Another way to resolve this would be to change your MySQL config options to use the older method. This may or may not be available to you in a cloud environment, though.

The third way is to create the WordPress user and simply allow it to utilize the older, less secure mechanism. This third option is what we will step through in this article.

Configure WordPress to use mysql_native_password

Modify your WordPress user to use the older, less secure mechanism using the following steps.

Prerequisites

  • You’ll need a MySQL 8.0 database to connect to.
  • You’ll also need root enabled on your Database server.

Log into your Cloud Server

ssh root@your.ip.address

Ensure MySQL Client is installed

apt install mysql-client

Connect from your server to your Database

mysql -h your.database.ip -u root -p

Create your WordPress database and user

mysql> CREATE DATABASE wpdb;
mysql> CREATE USER 'wpuser'@'%' IDENTIFIED WITH mysql_native_password BY 'USE_A_SECURE_PASSWORD_HERE';
mysql> FLUSH PRIVILEGES;

Ensure your new user is created using mysql_native_password

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user WHERE user = 'wpuser';
+--------+-------------------------------------------+-----------------------+------+
| user   | authentication_string                     | plugin                | host |
+--------+-------------------------------------------+-----------------------+------+
| wpuser | *REDACTED                                 | mysql_native_password | %    |
+--------+-------------------------------------------+-----------------------+------+
1 row in set (0.00 sec)

Grant new user appropriate permissions

mysql> GRANT ALL ON wpdb.* TO wpuser;
Query OK, 0 rows affected (0.00 sec)

Verify grants on new user

mysql> SHOW GRANTS FOR wpuser;
+--------------------------------------------------+
| Grants for wpuser@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `wpuser`@`%`               |
| GRANT ALL PRIVILEGES ON `wpdb`.* TO `wpuser`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

Conclusion

You should now be able to install WordPress using this newly created database, with the user and password you’ve also created here. The auth method that MySQL will allow for the new user has been changed so that older versions of PHP can still connect to MySQL 8.0.

Note that if you create any further users, you’ll have to adjust the auth method for them as well, if they are connecting from software that doesn’t know how to use the new mechanism.

Leave a Reply

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