php mysqli_connect: authentication method unknown to the client [caching_sha2_password]

Question

I am using php mysqli_connect for login to a 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

with caching_sha2_password in the MySQL Server ini file, it's not possible at all to login with user1 or user2;

error: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in...

with mysql_native_password in the MySQL Server ini file, it's possible to login with user1, but with user2, same error;

---

how can I login using caching_sha2_password on the mySql Server?

Answer 1

As of PHP 7.4, this is no longer an issue. Support for caching_sha2 authentication method has been added to mysqlnd.

---

Currently, PHP mysqli extension do not support new caching_sha2 authentication feature. You have to wait until they release an update.

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

They didn't mention PDO, maybe you should try to connect with PDO.

Answer 2

I solve this by SQL command:

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

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

if you are creating new user

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

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

this works for me

Answer 3

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

Remove quotes (') after ALTER USER and keep quote (') after mysql_native_password BY

It is working for me also.

Answer 4

If you're on Windows and it's not possible to use caching_sha2_password at all, you can do the following:

1. rerun the MySQL Installer
2. select "Reconfigure" next to MySQL Server (the top item)
3. click "Next" until you get to "Authentication Method"
4. change "Use Strong Password Encryption for Authentication (RECOMMENDED)" to "Use Legacy Authentication Method (Retain MySQL 5.X Compatibility)
5. click "Next"
6. enter your Root Account Password in Accounts and Roles, and click "Check"
7. click "Next"
8. keep clicking "Next" until you get to "Apply Configuration"
9. click "Execute"

The Installer will make all the configuration changes needed for you.

Answer 5

Like many many people, I have had the same problem. Although the user is set to use mysql_native_password, and I can connect from the command line, the only way I could get mysqli() to connect is to add

default-authentication-plugin=mysql_native_password

to the [mysqld] section of, in my setup on ubuntu 19.10, /etc/mysql/mysql.conf.d/mysqld.cnf

Answer 6

It's working for me (PHP 5.6 + PDO / MySQL Server 8.0 / Windows 7 64bits)

Edit the file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini:

default_authentication_plugin=mysql_native_password

Reset MySQL service on Windows, and in the MySQL Shell...

ALTER USER my_user@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Answer 7

I ran the following command ALTER USER 'root' @ 'localhost' identified with mysql_native_password BY 'root123'; in the command line and finally restart MySQL in local services.

Answer 8

If you're on a Mac, here's how to fix it. This is after tons of trial and error. Hope this helps others..

Debugging:

$mysql --verbose --help | grep my.cnf

$ which mysql
/usr/local/bin/mysql

Resolution: nano /usr/local/etc/my.cnf

Add: default-authentication-plugin=mysql_native_password

-------
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
default-authentication-plugin=mysql_native_password
------

Finally Run: brew services restart mysql

Answer 9

Now you can upgrade to PHP7.4 and MySQL will go with caching_sha2_password by default, so default MySQL installation will work with mysqli_connect No configuration required.

Answer 10

I tried this in Ubuntu 18.04 and is the only solution that worked for me:

ALTER USER my_user@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Answer 11

I am using laravel 5.8 and having MAMP server got this error resolved by adding DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock in .env file like below

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=8889
DB_DATABASE=dbname
DB_USERNAME=root
DB_PASSWORD=root
DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock

Answer 12

I think it is not useful to configure the mysql server without caching_sha2_password encryption, we have to find a way to publish, send or obtain secure information through the network. As you see in the code below I dont use variable $db_name, and Im using a user in mysql server with standar configuration password. Just create a Standar user password and config all privilages. it works, but how i said without segurity.

<?php
$db_name="db";
$mysql_username="root";
$mysql_password="****";
$server_name="localhost";
$conn=mysqli_connect($server_name,$mysql_username,$mysql_password);

if ($conn) {
    echo "connetion success";
}
else{
    echo mysqli_error($conn);
}

?>