Table of Contents
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.
