Alex blog All about IT!

How to fix WordPress database connection for MySQL 8

Very recently I have been upgrading software on my servers to latest one and took the risk of upgrading MySQL to latest 8.0.x version. You can read here how to upgrade WordPress to MySQL 8. In this article I will show how to fix WordPress database connection for MySQL 8.

As many recently have experienced this resulted in my WordPress sites showing this error message:

Error establishing a database connection.

The cause of that is very simple though. MySQL 8 have changed from using native encryption for passwords to a more standardized approaches. As a matter of fact, this change happened even earlier, in release 5.7. However, in MySQL 8 the team changed default value of default_authentication_plugin configuration variable from previous mysql_native_password to caching_sha2_password.

What this means is that when a client connects to MySQL without specifying which password encryption method it uses MySQL assumes it to be using caching_sha2_password. But most of clients currently in use are assuming to use mysql_native_password.

Here is how you can fix this issue:

Firstly you need make sure you have a backup of any files you will be changing. In this guide we will be changing MySQL configuration file, so let’s back it up first. I always recommend using current date and backup sequence number in backup names to easier identify them and being able to keep multiple backups from before:

$ sudo cp /etc/my.cnf /etc/my.cnf.backup.20181027.001

Now let’s go and edit MySQL configuration file:

$ sudo nano /etc/my.cnf

In this file locate [mysqld] section there and add the configuration lines as follows:

[mysqld]
default_authentication_plugin=mysql_native_password

There may be other configuration lines between those, but there should be no other section header between them.

After this you need to restart MySQL server process:

$ sudo systemctl restart mysqld

All done. Now your WordPress should be able to connect to MySQL 8 without any problems. In the future when the community will update mysqlnd client library to use new encryption methods this may be no longer needed, but as of now it still is.

Please let me know in the comments if this solution helped you to fix wordpress database connection for MySQL 8!

11 comments

  • Tim Sheppard on 14:41 #

    We are upgrading to MySQL 8.0.13 on our live website, which uses WordPress for the front end, and found the above very helpful.

    I don’t know is it was becasue we are running MySQL and WordPress on a Windows OS, but we had to drop and re-create our WordPress user in MySQL becasue the authentication method is stored as part of the user setup and I had created the user on the new server before I found your fix.

    Re-creating the user after changing the default_authentication_plugin setting in my.ini meant that the user was then created with mysql_native_password as the user default.

  • Alexey Kamenskiy on 05:15 #

    @Tim You are absolutely correct, changing default_authentication_plugin to mysql_native_password means that new users created in MySQL will be using native password hashing. That is the problem with PHP because at the time of me writing this article PHP still did not implement new password hashing options that MySQL was telling everyone about for a good few years. I mean it is not like they did this silently, they actually were saying that this will be a new default in MySQL8, but seems PHP developers are unprepared for that. Upgrading to PHP 7.2+ should work, but only after they finished their implementation: https://bugs.php.net/bug.php?id=76243

  • John on 07:06 #

    Thanks Al, I’m a new wordpress/php developer and couldn’t figure this for literally 3-4 hours. I loosely recall the prompts asking about password encryption during MySQL 8 but just blasted through it foolishly. Moving forward I will read everything extremely carefully on updates.

    • Alexey Kamenskiy on 07:14 #

      I think the biggest issue is that even though MySQL were saying that they will do this change long in advance, PHP team did not add new password options to PHP until very recently and that change hasn’t made it to any release yet. After they do release this change this issue should disappear mysqlnd extension will now know about new password encryption option.

  • Emmanuel Mahuni on 13:26 #

    the file /etc/my.cnf doesn’t exist, I have looked in etc and there is no my. files in there.

    • Alexey Kamenskiy on 02:57 #

      @Emmanuel, well that would be very weird. What version of MySQL you have installed? Is it stock MySQL? Or is it MariaDB? You need to note that on newer systems when you try to install MySQL from default repositories, it will instead install MariaDB. And MariaDB while started off as a fork of MySQL has diverted away already enough to NOT make them equal.

  • Emmanuel Mahuni on 14:06 #

    It’s the latest at the time of writing, v8. I removed it and put 5.4 instead and worked right away as usual. I didn’t have the time to try and troubleshoot this further. I created that file and still it didn’t work.

    • Alexey Kamenskiy on 14:11 #

      Wondering how did you install it from where? Did you use official MySQL repos? (the ones located at http://repo.mysql.com) or some 3rd party repositories?

    • Alexey Kamenskiy on 14:42 #

      If that’s on Mac then the configuration file location is different, instead of editing file from console you’d need to go to settings panel and there will be MySQL option from which you should be able to edit (or at least find location of) config file. But if you install it on Mac for the dev environment then I would suggest looking towards running it from docker. The guide above is for Linux and not for macos/bsd.

  • Emmanuel Mahuni on 16:18 #

    Oh i did that. There were all sorts of paths there and the one for configuration file was empty, I even pointed to the config file I created that had just that entry and nothing happened. I tried looking for the file in all those locations before I went system-wide. It wasn’t found. I don’t know why but the installation wasn’t using that file at all and it didn’t even exists at all. Very strange indeed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: