Table of Contents
When attempting to connect to MySQL using PHP, you might encounter an error like the following:
|
1 2 |
PDOException in Connector.php line 55: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select User, Plugin from mysql.user; +------------------+-----------------------+ | User | Plugin | +------------------+-----------------------+ | sample_user | mysql_native_password | | mysql.infoschema | mysql_native_password | | mysql.session | mysql_native_password | | mysql.sys | mysql_native_password | | root | caching_sha2_password | +------------------+-----------------------+ 5 rows in set (0.01 sec) |
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:
|
1 |
ALTER USER sample_user@localhost IDENTIFIED mysql_native_password BY 'PASSWORD'; |
Creating a New User with mysql_native_password
Create a user using mysql_native_password like this:
|
1 |
CREATE USER another_user@localhost IDENTIFIED mysql_native_password BY 'PASSWORD'; |
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.
|
1 2 |
[mysqld] default_authentication_plugin=mysql_native_password |
