Wordpress cannot connect to mysql server

Question

I have run a mysql server in my macbook, where I can access via both mysql command mysql -u root and navicat application. However, when I open the install page of a brand new wordpress app in my macbook. During the installation, I had got：

Answer 1

For docker mysql instance

docker run --name eight-mysql -e MYSQL_ROOT_PASSWORD=password -d mysql:latest --default-authentication-plugin=mysql_native_password

Answer 2

It turned out to be I have MySQL version at 8.0.11 that might be unsupported by WordPress (I guess). When I switch the mysql version from 8.0.11 to 5.7.16. It works. Fairly wired though.

Answer 3

What I did that solved my issue was to update to PHP 7.2.x on my IIS Server, then assigned this version to the Wordpress Website in PHPManager (In IIS Manager).

This worked for me.

Answer 4

Do not connect with MySQL root user. Create another user in MySQL to connect in Wordpress.

Answer 5

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.

To fix this problem, edit your mysql config file. On linux it should be at:

/etc/my.cnf

Find and uncomment the following line:

default_authentication_plugin=mysql_native_password

if you can't find it, just add it under [mysqld].

You must restart mysql after this change. On CentOS 7 you can issue this command:

systemctl restart mysqld

This permanent solution will fix mysql connection error for all wordpress installations.

Answer 6

MySQL 8.x actually IS supported, but requires a slightly different command when creating the user and password, as version 8 expects passwords to be SHA256 encoded.

When creating the database user, with the MySQL prompt, use the following:

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

So WITH mysql_native_password being the main difference. Good luck!