Wordpress cannot connect to mysql server

问题:

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：

回答1:

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!

回答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.

回答3:

Use 127.0.0.1 rather than localhost

I came across this when installing WordPress locally for plugin development/unit testing and deciding to use the latest of everything! i.e. MySQL 8.0.11, PHP 7.1.16, WordPress 4.9.7.

I was able to connect to the database with Sequel Pro desktop client but WordPress would not connect.

Migrating to MySQL 8.0 for Wordpress – episode 1 came across a similar problem where I was reminded to add define('WP_DEBUG', true); to my wp-config.php:

Then, the output above was prepended with:

Warning: mysqli_real_connect(): (HY000/2002): No such file or directory in /Users/BrianHenryIE/Sites/wptest/wp-includes/wp-db.php on line 1531

Line 1531 doesn't really tell much, just the error is sometimes surpressed:

if ( WP_DEBUG ) {
    mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
} else {
    @mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
}

Another SO answer, mysqli_real_connect(): (HY000/2002): No such file or directory, told me to use 127.0.0.1 instead of localhost, which I changed in my wp-config.php, define( 'DB_HOST', '127.0.0.1' );, and everything worked!

回答4:

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

回答5:

Try this:

setsebool -P httpd_can_network_connect=1

回答6:

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.

回答7:

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.

回答8:

For docker mysql instance

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