php mysqli_connect: Authentication method unknown to client

Question

I'm using php mysqli_connect Login to MySQL database (all on localhost)

<?php
//DEFINE ('DB_USER', 'user2');
//DEFINE ('DB_PASSWORD', 'pass2');
DEFINE ('DB_USER', 'user1');
DEFINE ('DB_PASSWORD', 'pass1');
DEFINE ('DB_HOST', '127.0.0.1');
DEFINE ('DB_NAME', 'dbname');

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if(!$dbc){
    die('error connecting to database');    
}
?>

This is the mysql.user table:

MySQL server ini file:

[mysqld]
# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=caching_sha2_password
#default_authentication_plugin=mysql_native_password

Using caching_sha2_password in the MySQL Server ini file, it is impossible to log in with user1 or user2;

Error: mysqli_connect(): Server requested client [caching_sha2_password] unknown authentication method...

Using mysql_native_password in the MySQL Server ini file, you can log in with user1, but the same error occurs with user2;

---

How to log in using caching_sha2_password on mySql server?

Answer 1

I solved this problem via SQL command:

ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

Referenced by https://dev.mysql. com/doc/refman/8.0/en/alter-user.html

If you are creating a new user

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Referenced by https://dev.mysql. com/doc/refman/8.0/en/create-user.html

This worked for me

Answer 2

As of PHP 7.4, this is no longer a problem. mysqlnd added support for the caching_sha2 authentication method.

---

Currently, the new caching_sha2 authentication feature is not supported by the PHP mysqli extension. You'll have to wait until they release an update.

View related posts from MySQL developers: https: //mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

They don't mention PDO, maybe you should try using PDO connection.