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?

Does PDO support this? I've seen other reports about `mysqli`. — tadman, Apr 25 '18 at 16:34
Answer posted by @黃皓哲 should be marked as accepted answer. — FIL, Feb 04 '19 at 09:26
From terminal login to mysql with this command : mysql -u root -p then enter root password, then paste the following command ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; — user3512810, Mar 26 '21 at 11:52
For anyone who is having no luck from the below answers: I also got this error message in a Laravel app when I had the wrong IP for the database in my .env file! — Charles Wood, Jun 16 '22 at 13:59

score 322 · Answer 1 · answered Jun 09 '18 at 17:28

322

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

answered Jun 09 '18 at 17:28

黃皓哲

3,422
2
7
13

5

Good answer. The gotcha here is you might have to do this every time you add a new user, depending on the server settings – Machavity Jun 28 '18 at 14:18
4

Thank you very much I was looking for a way to convert the new password hashes to the older ones (like user2 format to user1 format in the question) and your answer did exactly the job – Accountant م Jul 25 '18 at 18:29
19

And so ended a 4 day struggle to get the php-7.2.9/11 playing nice with MySQL-8.012. Could someone on the PHP team be convinced to note this in the INSTALL in the tarball? It would save thousands of person hours over the next year or two. – MountainMan Oct 12 '18 at 16:27
9

this should be accepted answer for workaround solution – Yohanim Jan 29 '19 at 17:24
1

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements – Dimmduh Mar 05 '19 at 05:58
2

This worked for me with php7.0 and mysql 8.0 on CentOS just make sure you also restart mysqld – cgclip Jul 25 '19 at 18:51
1

Perfect! It work for me too! Just conected to database SYS and execute the Alter command above. Thanks! – LUISAO Aug 04 '19 at 14:33
1

Alter didn't work for me, so, I dropped and created the user instead. Thank you. – Tigran Aug 19 '19 at 13:58
2

Switching to native authentication basically means you weakened the overall security to allow access. That gives you access but can't be considered a long-term, secure solution. And as @Dimmduh pointed out, you may end up with "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements" – Simon Brown Nov 20 '19 at 10:04
1

@cgclip YES: just make sure you also restart mysqld !! – Rodrigo Feb 27 '20 at 12:46
1

This worked for me, as I choosed new authentication type, but my application does not support it. So I had to "downgrade" it and this solved it. – Gregor Simončič Mar 16 '20 at 20:08
1

That should be the accepted answer but unfortunately nothing in life is fair ! Thank you – Thanasis Jun 17 '20 at 16:57
1

Thank you, it work for me. I use php 7.3 and mysql 8.0 on macOS catalina – Dinesh Sep 15 '20 at 05:19
I'm a little late, but for anyone who is trying to access a remote mysql server over a local IP, the ALTER USER line should also be run for your local internet IP instead of 'localhost' – fromtheloam Feb 25 '21 at 07:57
Thx. This worked for me. But i want understand why this works (can sb expand this answer) - for me this problem was related with phpmyadmin on fresh mysql server 8. – Jakub Ujvvary Mar 10 '21 at 15:02
1

After this restarting mysqld with `--default-authentication-plugin=mysql_native_password` made it work for me. – lycanthrope10100 Mar 23 '21 at 20:11
this answer made me create a password and now I cant even connect, wrong answer. – showtime Oct 20 '21 at 10:48
Works! If you are using Laravel Valet and trying to get PHP 7.3 to work, use `mysql-exec "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';" && brew services restart mysql` to make this configuration change. – aubreypwd Jul 22 '22 at 17:47
This advice need mark: must have)) – Ryabinin Sergey Dec 25 '22 at 03:08

score 95 · Accepted Answer · edited Jun 10 '20 at 17:51

95

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.

edited Jun 10 '20 at 17:51

Dharman

30,962
25
85
135

answered Apr 25 '18 at 16:54

abeyaz

3,034
1
16
20

4

PDO [has the same problem](https://stackoverflow.com/questions/49083573/php-7-2-2-mysql-8-0-pdo-gives-authentication-method-unknown-to-the-client-ca) – Machavity Jun 18 '19 at 15:06
@Machavity do you know if this answer is still valid? Ie. it's not outdated yet with newer PHP versions? – gen Mar 21 '20 at 22:14
1

@gen Based on [this bug](https://bugs.php.net/bug.php?id=78981), looks like it's working as of 7.4.2 – Machavity Mar 22 '20 at 00:58
8

Upgrading to php7.4 does solved the issue. And I think that is the best approach rather than changing the authentication method to `mysql_native_password` as suggested by some answers. – ultrasamad Jun 21 '20 at 17:00
1

I use docker (apache+php in a container and mysql in other container) and this solved my Issue. Thanks! – Joan Galmés Riera Nov 01 '20 at 11:03
1

PHP 7.4.3 here. Had to change to `mysql_native_password` for it to work. – RaminS Jul 28 '21 at 00:56
I have `PHP 7.4.27 (cli) (built: Jan 20 2022)`, still I needed `mysql_native_password`! – tash Mar 08 '22 at 02:57

score 44 · Answer 3 · edited Jun 20 '19 at 05:43

44

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.

edited Jun 20 '19 at 05:43

CJ Dennis

4,226
2
40
69

answered Jan 09 '19 at 02:30

Eranda J.

490
5
7

4

That was the only solution worked for me on localhost server using WAMP server :) – Jodyshop Feb 23 '20 at 19:23
This seemed to fix it on my Mac's homebrew LAMP setup. Didn't experience this problem on debian though. – Magnus Feb 23 '20 at 22:07
1

For those purely beginner, 1) `cd /usr/local/mysql/bin` 2) `./mysql -u root -p` 3) enter the line of code in this post – Jason Feb 26 '21 at 01:10

score 32 · Answer 4 · answered Jun 20 '19 at 06:22

32

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.

answered Jun 20 '19 at 06:22

CJ Dennis

4,226
2
40
69

2

man after a year of upvoting this answer my db was hacked... – Code Tree Sep 26 '20 at 01:54
2

I doubt this was the root cause of your database hack. Thanks for the post OP, it helped me configure MySQL for a legacy application running PHP 5.6. – incredimike Jul 27 '21 at 23:52

score 30 · Answer 5 · answered Nov 09 '19 at 22:50

30

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

answered Nov 09 '19 at 22:50

dBags

447
4
4

2

Also, keep in mind if the user is created prior this change it will not work. So, you should FIRST set default-authentication in the config and than create the user in order to work! – Oleg Popov Nov 22 '19 at 07:21
1

This worked on Ubuntu 20.04 with MySQL 8.0 and PHP 5.6. Note that you must set the tables to INNODB for this too work – rubo77 Apr 19 '21 at 07:30
1

after adding this to config I get the same error – Igor Jan 28 '22 at 08:52
This worked for me with Ubuntu 20.04.6 LTS (I had to edit `/etc/mysql/mysql.cnf` and run `systemctl restart mysql.service`), MySQL version 8.0.33-0ubuntu0.20.04.4, PHP 5.6, and I did not need to recreate the user. – Raffi Aug 14 '23 at 01:07

score 19 · Answer 6 · edited May 26 '19 at 12:30

19

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';

edited May 26 '19 at 12:30

weegee

3,256
2
18
32

answered May 03 '19 at 20:11

Chinnon Santos

413
4
11

it works, thank you... we must config that 2 things: mysql conf and alter user – danisupr4 May 15 '20 at 18:34

score 8 · Answer 7 · edited May 24 '19 at 05:31

8

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.

edited May 24 '19 at 05:31

Syed Ekram Uddin

2,907
2
29
33

answered May 24 '19 at 04:20

JRonald Moreno

89
1
3

2

no spaces here `'root' @ 'localhost'` should be read instead `ALTER USER 'root'@'localhost' identified with mysql_native_password BY 'root123';` – d1jhoni1b Aug 12 '20 at 06:28

score 7 · Answer 8 · answered Nov 06 '19 at 23:19

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

score 5 · Answer 9 · answered Nov 11 '19 at 14:39

5

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.

answered Nov 11 '19 at 14:39

Pavel Niedoba

1,554
2
19
36

2

Not quite. It's buggy in 7.4.0 and 7.4.1. [Fixed in 7.4.2](https://bugs.php.net/bug.php?id=78981) – Machavity Mar 22 '20 at 00:59

score 4 · Answer 10 · edited Sep 09 '19 at 02:15

4

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';

edited Sep 09 '19 at 02:15

Błażej Michalik

4,474
40
55

answered Sep 08 '19 at 21:22

imjesr

89
4

perfect. I'm testing. The other answers require rehauling my entire system, which is currently working just to access one item. I'm think I need a rotating "HOT" (Always updated) server going forward. – Ken Ingram Jun 01 '22 at 23:47

score 1 · Answer 11 · answered Jul 25 '22 at 14:34

If you have not yet already changed your MySQL default authentication plugin, you can do so by:

Log in as root to MySQL
Run the following SQL command:

a. if you are running MySQL in a different server:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'password';

b. if you are running MySQL in a different server:

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

score 0 · Answer 12 · answered Apr 22 '21 at 01:51

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

score 0 · Answer 13 · answered Aug 24 '21 at 07:50

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.

score 0 · Answer 14 · answered Sep 06 '21 at 17:06

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. :)

score 0 · Answer 15 · answered Oct 05 '21 at 10:51

0

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.

answered Oct 05 '21 at 10:51

sh6210

4,190
1
37
27

score 0 · Answer 16 · answered Dec 10 '22 at 01:28

0

In Digital Ocean Managed Mysql, we have an option to change encryption, you can change to legacy and it'll work ok.

answered Dec 10 '22 at 01:28

Usama Munir

589
9
11

score -4 · Answer 17 · answered Oct 10 '19 at 02:44

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);
}

?>

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

17 Answers17

phpMyAdmin GUI way

Linked

Related