How to Fix MySQL's 'Unknown Authentication Method [caching_sha2_password]' Error with mysqli_connect?

Resolving MySQL Authentication Error: "Unknown Authentication Method [caching_sha2_password]" with mysqli_connect

When attempting to establish a database connection using mysqli_connect, you may encounter an error stating "The server requested authentication method unknown to the client [caching_sha2_password]." This error arises from a mismatch between the authentication method configured on the MySQL server and the method supported by your PHP code.

Specifically, the MySQL Server ini file sets default_authentication_plugin to caching_sha2_password. This plugin provides enhanced security but requires client support for this method. However, your PHP code uses the default MySQL native password authentication mechanism, which is not compatible with caching_sha2_password.

To resolve this issue, you have two options:

Option 1: Change Authentication Plugin on MySQL Server

You can change the default_authentication_plugin in the MySQL Server ini file to mysql_native_password. This will allow your PHP code to connect using the native password authentication method.

Option 2: Specify Authentication Method in PHP Code

Alternatively, you can specify the authentication method in your PHP code to match the server's configuration. This can be done by using the mysqli_options() function before calling mysqli_connect().

mysqli_options(mysqli, MYSQLI_OPT_AUTH_PLUGIN, 'caching_sha2_password');

Changing User Authentication

If neither of the above options resolves the issue, you may need to change the authentication method for the specific user attempting to connect. This can be done using the following SQL command:

ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'new_password';

By implementing these solutions, you can successfully establish a connection to MySQL using mysqli_connect despite the default authentication method set on the server.

The above is the detailed content of How to Fix MySQL's 'Unknown Authentication Method [caching_sha2_password]' Error with mysqli_connect?. For more information, please follow other related articles on the PHP Chinese website!