How to fix WordPress database connection for MySQL 8
2018-10-27 | 15 commentsVery 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!
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.
@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
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.
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.
the file /etc/my.cnf doesn’t exist, I have looked in etc and there is no my. files in there.
@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.
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.
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?
I got mysql-8.0.13-macos10.14-x86_64.dmg community server from https://dev.mysql.com/downloads/mysql/
and it has a pkg installation file that runs a setup and handles everything. So I don’t think it was anything to do with how I installed it. I think they changed something or something else was happening. I ended up installing another dmg from here https://dev.mysql.com/downloads/mysql/5.7.html#downloads for version 5.7. So I downgraded.
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.
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.
What I did was to:
ALTER USER ‘root’@’localhost’
IDENTIFIED WITH mysql_native_password
BY ‘password’;
you have to be careful setting the user account you want to use.
from MySQL documentation here: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatible-connectors
Regards
That would also work. But it will only work for one account at the time. The problem with that is – you may have multiple sites and they shouldn’t share DB credentials and moreover shouldn’t use root account. And going forward all accounts created with that approach will be still using new password hashing algo, with the one in this article they will all use old one (and PHP would work therefore)
I believe that the safest way is to create a mysql user having access only to wordpress database.
I would not mess with root and default authentication plugin.
CREATE USER ‘wordpress_user’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
GRANT ALL PRIVILEGES ON database.* TO ‘wordpress_user’@’localhost’;
Generally you are correct that this would also work (as of now) and can be considered safer option. But the problem is that default plugin is would still be the one that currently doesn’t work with PHP at all (hoping next release will have a fix for this and then I’ll need to update this article). With the way that you suggest it will only fix it for single user. Once you forget to add mysql_native_password to it the user will no longer work from PHP (while still will work from CLI) and PHP so far doesn’t give detailed error message as of why authentication has failed.
This fix is more permanent and reliable than remembering that you need this addition for each new user. And once PHP has a fix it will be as simple as changing config back to default one and re-creating users.