MySQL: When you can’t login via PHP


When attempting to connect to MySQL using PHP, you might encounter an error like the following:

Environment

This error has started to appear from MySQL version 8. The MySQL version that triggered this error was 8.0.11.

  • macOS High Sierra version 10.13.6
  • MySQL 8.0.11 Homebrew

Two Authentication Plugins in MySQL 8

Starting from MySQL 8, there are two authentication plugins available:

mysql_native_password
The traditional authentication plugin.
caching_sha2_password
A new authentication plugin introduced in MySQL version 8.

caching_sha2_password offers several benefits, such as secure password encryption and improved authentication performance through caching. MySQL 8 defaults to using caching_sha2_password.

However, since PHP does not currently support the new authentication plugin (though it might in the future), trying to connect using a password managed by caching_sha2_password results in an error.

Resolving Connection Issues in PHP

The choice of authentication plugin is a per-user setting. You can check the user-specific settings as follows:

If you need to connect with an existing user, you need to modify the settings for that user. Alternatively, you can create a new user using mysql_native_password and connect with that user.

Changing the Authentication Plugin for Existing Users

Execute the following SQL:

Creating a New User with mysql_native_password

Create a user using mysql_native_password like this:

Changing Server Default Settings

When creating a new user, you need to specify the authentication plugin. By default, caching_sha2_password is used. To make mysql_native_password the default, modify your my.cnf and restart the server.