How to Fix the 'mysqli_connect: authentication method unknown to the client [caching_sha2_password]' Error in PHP?

Mitigating "mysqli_connect: authentication method unknown to the client [caching_sha2_password]" in PHP

When utilizing MySQL's mysqli_connect to authenticate with a local database, you may encounter the enigmatic error, "mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]." This error arises from a mismatch between the authentication plugin employed by the server and the one expected by the client.

The MySQL Server ini file dictates the default authentication plugin used for server connections. By default, this setting is configured to "caching_sha2_password," indicating that the server favors the caching_sha2_password authentication method. In your case, your server has been configured accordingly.

Conversely, your PHP code attempts to connect using legacy authentication methods, such as the "mysql_native_password" method. This mismatch causes the authentication failure and results in the aforementioned error.

Resolution

To resolve this issue, you can modify the authentication method used by the PHP code to match the server's default setting. Alternatively, you can alter the MySQL Server ini file to specify an authentication plugin compatible with the PHP code. Here are some potential solutions:

• Enable the mysql_native_password plugin on the MySQL Server:

  • Open the MySQL Server ini file and locate the "default_authentication_plugin" parameter.
  • Change its value to "mysql_native_password."

• Modify the PHP code to use the caching_sha2_password plugin:

  • Utilize the mysqli_options() function to set the MYSQLI_OPT_AUTH_PLUGIN option to "caching_sha2_password."

Alternatively, you can switch the MySQL user's authentication method to mysql_native_password using SQL commands:

• Alter existing user:

  • Run the SQL command: ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

• Create new user with mysql_native_password:

  • Run the SQL command: CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

By implementing these solutions, you can reconcile the authentication methods used by the MySQL server and the PHP client, allowing you to successfully establish connections to your database.

The above is the detailed content of How to Fix the 'mysqli_connect: authentication method unknown to the client [caching_sha2_password]' Error in PHP?. For more information, please follow other related articles on the PHP Chinese website!