PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client

Question

I'm running MySQL version 8 on PHP 7.0.

I'm getting the following error when I try to connect to my database from PHP:

Connect Error: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

PHP might show this error

Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in D:\xampp\htdocs\reg\server.php on line 10

How can I fix this problem?

I did some Google work. Maybe this will work? https://github.com/laradock/laradock/issues/1392 (not the same problem, but it's the same error message) — Koen Hollander, Sep 17 '18 at 09:18
For folks who stumble across this and are having no success: I also got this error message when I had the wrong IP for the database in my .env file. — Charles Wood, Jun 16 '22 at 13:58

Elias Gikonyo · Answer 1 · 2018-12-21T08:16:34.560

507

@mohammed, this is usually attributed to the authentication plugin that your mysql database is using.

By default and for some reason, mysql 8 default plugin is auth_socket. Applications will most times expect to log in to your database using a password.

If you have not yet already changed your mysql default authentication plugin, you can do so by:
1. Log in as root to mysql
2. Run this sql command:

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

Replace 'password' with your root password. In case your application does not log in to your database with the root user, replace the 'root' user in the above command with the user that your application uses.

Digital ocean expounds some more on this here Installing Mysql

edited Dec 21 '18 at 08:16

answered Dec 21 '18 at 08:09

Elias Gikonyo

5,189
1
8
4

4

The main reason for this problem is that Mysql's default authentication plug-in support has changed in the 8.0 release of MariaDB. For example, the list of Mysql authentication plug-ins supported by 'PHP7.3' can be seen through the 'phpinfo()' function:` mysqlnd debug_trace, auth_plugin_mysql_native_password auth_plugin_mysql_clear_password, auth_plugin_sha256_password `. So change the authentication plugin mechanism in the Mysql server restricted account to 'sha256_password' or 'mysql_native_password': ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'password'; – lupguo Oct 27 '19 at 16:12
1

I get `ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'` for this. My password is `''` (empty) – xjcl Jun 03 '20 at 11:41
It doesn't work for 8.0.20. The command ALTER USER succeeded but the warning still appears. – Clarity Nov 09 '20 at 05:35
2

Thanks for this. I added [mysqld] default-authentication-plugin=mysql_native_password to mysql (8) config file but this did the trick. – marius-ciclistu Dec 24 '20 at 23:26
This worked for me BUT with one added caveat on macOS Big Sur, MySQL 8.0.15: ALTER USER CURRENT_USER() IDENTIFIED WITH mysql_native_password BY 'password'; – Numabyte Jan 23 '21 at 06:12
BY GIVES ME AN ERROR – Feb 12 '21 at 21:33
Got past the first error message, now getting the following: mysqli_real_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES) – MC9000 Aug 16 '21 at 06:59
And then `sudo service mysql restart` – MohammadReza Vahedi Aug 22 '22 at 10:30

score 162 · Answer 2 · edited Apr 21 '19 at 21:54

162

You have to change MySQL settings. Edit my.cnf file and put this setting in mysqld section:

[mysqld]
default_authentication_plugin= mysql_native_password

Then run following command:

FLUSH PRIVILEGES;

Above command will bring into effect the changes of default authentication mechanism.

edited Apr 21 '19 at 21:54

George

6,886
3
44
56

answered Sep 17 '18 at 09:18

michail_w

4,318
4
26
43

45

and then recreate the users. `ALTER USER $user IDENTIFIED WITH mysql_native_password BY 'passwd'` (ref [ALTER USER manual page](https://dev.mysql.com/doc/refman/5.7/en/alter-user.html) – danblack Sep 17 '18 at 09:23
1

$user is not a valid variable. not usefull – vincent thorpe Jun 14 '19 at 15:44
6

Found my.cnf on Ububtu at /etc/mysql/my.cnf – Marco Floriano Oct 29 '19 at 18:56
I don't see that line in mysql 8.0 on Ubuntu 19 – code-8 Mar 28 '20 at 17:51
@cyber8200 you can always add this line. – michail_w Mar 29 '20 at 18:11
I don't see my.cnf in macbook – Akshay Hazari Apr 02 '20 at 20:32
1

@AkshayHazari Please follow this thread: https://stackoverflow.com/questions/10757169/location-of-my-cnf-file-on-macos – michail_w Apr 03 '20 at 13:40
1

Where is my.cnf file on Windows 7? – Clarity Nov 09 '20 at 05:48
@Clarity it depends on software you're using. You should find root directory of your MySQL or MariaDB first. Usually it's installed under Program Files, but it may vary if you're using packages like XAMP or WAMP. – michail_w Nov 12 '20 at 10:41
Finally on Linux: `sudo service mysql restart` – MohammadReza Vahedi Aug 22 '22 at 10:31

score 117 · Answer 3 · edited Dec 26 '22 at 01:01

117

I've tried a lot of other solutions, but only this works for me.

Thanks for the workaround.

Check your .env

MYSQL_VERSION=latest

Then type this command

$ docker-compose exec mysql bash
$ mysql -u root -p

(login as root)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';

Then go to phpmyadmin and login as :

host -> mysql
user -> root
password -> root

edited Dec 26 '22 at 01:01

starball

20,030
7
43
238

answered Dec 23 '18 at 17:38

Francesco Taioli

2,687
1
19
34

1

this solution worked for me as a charm. U saved my day also! what I did additionally was to fork mysql docker and encase the solution in the build of mysql container: https://github.com/cybalex/mysql/commit/e5cb4cf80ca69066a185bfa7dfeeb44de93d4c31 – Oleksii Zymovets Aug 19 '19 at 22:27
1

Thank you. it helps a lot. I just need that last alter query to run this on docker mysql – Ankit Oct 03 '19 at 09:43
2

`[HY000][1396] Operation ALTER USER failed for 'default'@'%'` happens on the last alter... any ideas? – Chris Stage Apr 03 '20 at 15:55
@TaioliFrancesco thanks for your answer it works, but do we have to run theses queries each time w start mysql container ? is there a way to do it via docker compose ? – SlimenTN May 23 '20 at 11:56
@SlimenTN no, only once and then the container rember the mysql setting – Francesco Taioli May 24 '20 at 08:14
@TaioliFrancesco ok but you know when automating things via CI/CD tool we might need to drop containers and start new ones, are the settings will remain the same ? – SlimenTN May 24 '20 at 08:32
i'm new in the ci/cd so i'm not the right person to ask, but i think that if the container are the same the config will remain, otherwise if the container will be drop and rebuild at each new pipeline instance, the config won't remain the same – Francesco Taioli May 24 '20 at 12:21
Can i put the empty password for root and how i can make it ? – F. Dakia Apr 15 '21 at 08:25
@F.Dakia i haven't try it. One solution could run the first two commands and change the third one like this ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY ''; – Francesco Taioli Apr 16 '21 at 16:11
If you can rebuild container with your MySQL database I would rather recommend to check this solution - https://github.com/laradock/laradock/issues/1392#issuecomment-563011691. As an example, you can check how laradock made a setup for MySQL - https://github.com/laradock/laradock/tree/master/mysql. – Scofield Jul 07 '21 at 18:29
Thanks, ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; work for me in connection remote – elias sharafi Jan 15 '22 at 13:11

score 15 · Answer 4 · answered May 12 '19 at 12:11

15

None of the answers here worked for me. What I had to do is:

Re-run the installer.
Select the quick action 're-configure' next to product 'MySQL Server'
Go through the options till you reach Authentication Method and select 'Use Legacy Authentication Method'

After that it works fine.

answered May 12 '19 at 12:11

Stuperfied

322
2
10

2

If you used the installer and you're using the classic username/password method for connecting to your database, this method is the way to go. – Matt Wohler May 16 '19 at 15:47
I'm not finding Authentication Method in the installer :( – Charles Wood Jun 16 '22 at 13:51
1

@CharlesWood You need to use the MYSQL Installer https://dev.mysql.com/downloads/installer/ – 13garth Jan 31 '23 at 16:42

score 10 · Answer 5 · answered Apr 21 '19 at 18:03

Faced the same problem, I was not able to run wordpress docker container with mysql version 8 as its default authentication mechanism is caching_sha2_password instead of mysql_native_password.

In order to fix this problem we must reset default authentication mechanism to mysql_native_password.

Find my.cnf file in your mysql installation, usually on a linux machine it is at the following location - /etc/mysql

Edit my.cnf file and add following line just under heading [mysqld]

default_authentication_plugin= mysql_native_password

Save the file then log into mysql command line using root user

run command FLUSH PRIVILEGES;

This solution solves my problem, but I don't have to flush the privileges. — sianipard, Jul 14 '20 at 15:18

score 2 · Answer 6 · answered Feb 23 '19 at 16:12

2

I'm using Laravel Lumen to build a small application.
For me it was because I didn't had the DB_USERNAME defined in my .env file.

DB_USERNAME=root

Setting this solved my problem.

answered Feb 23 '19 at 16:12

CIRCLE

4,501
5
37
56

score 0 · Answer 7 · answered Feb 24 '19 at 05:01

0

In my.cnf file check below 2 steps.

check this value -

old_passwords=0;

it should be 0.
check this also-

[mysqld] default_authentication_plugin= mysql_native_password Another value to check is to make sure

[mysqld] section should be like this.

answered Feb 24 '19 at 05:01

sanjaya

204
2
4
11

Where would you find the "my.cnf" file? – Tim Kruger Feb 25 '19 at 08:41
laradock/mysql/my.cnf – sanjaya Feb 25 '19 at 16:44

score 0 · Answer 8 · answered Apr 17 '19 at 07:17

preferences -> mysql -> initialize database -> use legacy password encryption(instead of strong) -> entered same password

as my config.inc.php file, restarted the apache server and it worked. I was still suspicious about it so I stopped the apache and mysql server and started them again and now it's working.

PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client

8 Answers8

Linked

Related