php mysqli_connect: authentication method unknown to the client [caching_sha2_password]
PhpMysqlHashShaPhp Problem Overview
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');
}
?>
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?
Php Solutions
Solution 1 - Php
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
Solution 2 - Php
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.
Solution 3 - Php
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.
Solution 4 - Php
If you're on Windows and it's not possible to use caching_sha2_password
at all, you can do the following:
- rerun the MySQL Installer
- select "Reconfigure" next to MySQL Server (the top item)
- click "Next" until you get to "Authentication Method"
- change "Use Strong Password Encryption for Authentication (RECOMMENDED)" to "Use Legacy Authentication Method (Retain MySQL 5.X Compatibility)
- click "Next"
- enter your Root Account Password in Accounts and Roles, and click "Check"
- click "Next"
- keep clicking "Next" until you get to "Apply Configuration"
- click "Execute"
The Installer will make all the configuration changes needed for you.
Solution 5 - Php
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
Solution 6 - Php
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';
Solution 7 - Php
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.
Solution 8 - Php
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
Solution 9 - Php
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.
Solution 10 - Php
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';
Solution 11 - Php
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
Solution 12 - Php
In my case when I was using WAMP server I fixed it using following
- Note the port in mySQL's "my.ini" file. In my case it was changed to 3308 when I was switching b/w MariaDB and MySQL DB.
- Use the port number when you create mysqli object.
Using the above steps I was able to run the program successfully.
Solution 13 - Php
phpMyAdmin GUI way
Note: This method may not work if you have the problem with root user account.
- Login to phpMyAdmin as root.
- Switch to "User Accounts" tab.
- Select the username you get error with.
- From the top buttons, switch to "Login Information" section.
- In "Login Information" box, change "Authentication plugin" from "Caching sha2 authentication" to "Native MySQL authentication". Also, you must fill in other required fields, obviously, including username and password.
- Save it by clicking on the "Go" button at the bottom.
- Return back to terminal and enjoy. :)
Solution 14 - Php
In my case, i'm using PHP Symfony
framework and it's a silly mistake.
The database credential was wrong in paramers.yml
.
After changing the credentials accordingly the problem was gone.
Solution 15 - Php
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);
}
?>