MySQL 8 and WordPress – Unable to connect to database

MySQL 8 (actually, even back to MySQL 5.7) changed the default authentication from mysql_native_password to caching_sha2_password. This creates a problem in my environment of PHP 7.3 and MySQL 8.

First, I’d like to say is that this is a new setup for WordPress so I did not have existing users or databases.

Here’s a typical setup I’d run through on the database server to get the database and user created.

mysql> create database wordpress;
mysql> create user 'wpuser'@'localhost' identified by 'password';
mysql> grant all privileges on wordpress.* to 'wpuser'@'localhost';

Next, I’d head over to my website and start the WordPress installation through the web interface. Problem is, I’m getting an error when establishing connection to the database.

I confirmed access from the command line already, so I know it isn’t an actual user/permission error. Seems to be authentication since it can’t even connect to my database server.

mysql> select host,user,plugin from mysql.user where user='wpuser' \G
*************************** 1. row ***************************
  host: localhost
  user: wpuser
plugin: caching_sha2_password
1 row in set (0.00 sec)

Since MySQL changed the default authentication plugin to caching_sha2_password, my login is failing.

Changingdefault_authentication_plugin in my.ini configuration in the [mysqld] section, I restarted MySQL and then recreated my user thinking it would automatically use the mysql_native_password in lieu of caching_sha2_password.

Changing the default authentication plugin does not change users already created in MySQL. I had to update my already created MySQL user by changing the plugin. Moving forward, if you create a new user, it will automatically use the mysql_native_password plugin.

mysql> alter user 'wpuser'@'localhost' identified with mysql_native_password by 'password';

Now, a quick look at my user again and I see the plugin is updated to the mysql_native_password.

mysql> select host,user,plugin from mysql.user where user='wpuser' \G
*************************** 1. row ***************************
  host: localhost
  user: wpuser
plugin: mysql_native_password
1 row in set (0.00 sec)

Heading back to my web installer, I try again and it succeeds.

In conclusion, if you have upgraded to MySQL 8 (or 5.7+), you can simply update the users for your WordPress database by using the ALTER command above for those specific users and you’ll also need to remember to use the following when creating a new user.

mysql> create user 'wpuser'@'localhost' identified with mysql_native_password by 'password';

If you decide you do not want to use the default authentication plugin at all and change the my.ini configuration (in the [mysqld] section) to reflect mysql_native_password as your default, then any new users created in the future will be set with the mysql_native_password plugin type.

Leave a Reply

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