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

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!

Answer 2

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!