Mysql. The server requested authentication method unknown to the client [caching_sha2_password] #1390

mvladimir7 · 2018-02-23T13:44:00Z

Info:

Docker version: 17.12.0-ce, build c97c6d6
Laradock commit: 13f55ef
System info: Win
System info disto/version: 10

Issue:

New fresh laradock installation. All default settings. Driver is shared.
Except:

DATA_SAVE_PATH=../../data

Can't connect to the mysql from phpMyAdmin.

#2054 - The server requested authentication method unknown to the client

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

But I can connect to mysql inside mysql container with standard root/root credentials.

I tried to delete all containers and images. Several times downloaded laradock. Tried to reinstall Docker. No success.

Expected behavior:

Can connect to mysql with phpMyAdmin

Logs

$ docker logs laradock_mysql_1
2018-02-23T13:22:10.682788Z 0 [Warning] [MY-011070] Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it is deprecated and will be removed in a future release.
2018-02-23T13:22:10.682938Z 0 [Warning] [MY-010915] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-02-23T13:22:10.695441Z 0 [System] [MY-010116] /usr/sbin/mysqld (mysqld 8.0.4-rc-log) starting as process 1 ...
mbind: Operation not permitted
mbind: Operation not permitted
2018-02-23T13:22:11.537504Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T13:22:11.542367Z 0 [Warning] [MY-000000] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2018-02-23T13:22:11.549449Z 0 [Warning] [MY-010315] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549556Z 0 [Warning] [MY-010315] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549594Z 0 [Warning] [MY-010315] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549629Z 0 [Warning] [MY-010315] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549676Z 0 [Warning] [MY-010323] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549727Z 0 [Warning] [MY-010323] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.549779Z 0 [Warning] [MY-010311] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.565161Z 0 [Warning] [MY-010330] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.565260Z 0 [Warning] [MY-010330] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-02-23T13:22:11.570621Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL).

The text was updated successfully, but these errors were encountered:

mvladimir7 · 2018-02-23T15:41:19Z

Root cause

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

Corrective action

Downgrade mysql version

RohitVenturit · 2018-07-10T11:22:50Z

Don't uninstall MySQL latest version. Rather follow these steps https://gist.github.com/benlinton/d24471729ed6c2ace731
Basically you need to unlink latest installed mysql, install older MSQL version and link it.

ondast · 2018-07-21T13:13:11Z

If you want to use mysql 8 with native password (the old way) you can follow the following steps:

Add this to my.cnf in laradock\mysql\my.cnf under [mysqld]:
default-authentication-plugin=mysql_native_password
Clear the data-folder (will destroy your data), rebuild the mysql container and it should work.

undefinedplayer · 2018-09-07T20:51:18Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

najamalvi · 2018-09-28T13:14:44Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password';
Bravoo. This is the solution

MaiQuocDuy · 2018-10-25T02:56:08Z

Hi Mr. Ailionx and Mr. Najamalvi,
I'm a student in a country which don't speak English. I'm very happy when reading your guide but I don't understand what steps I should do. Please tell me more details. Thank so much !!!

CoderBill · 2018-10-26T18:33:52Z

Don't uninstall MySQL 8. Sign into mysql using a terminal program:
mysql -u root -p
my_password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

This does the trick.

allexiusw · 2018-10-27T02:55:43Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

work for me, thank you!

MaiQuocDuy · 2018-10-27T03:10:34Z

It also work for me. Many many thanks for you. Have a nice day!!!

…

On Sat, Oct 27, 2018 at 9:55 AM William Alexander ***@***.***> wrote: alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it. work for me, thank you! — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1390 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AqZUP-QX0NTNn9J98RuhesSqjAuiuGnWks5uo8s6gaJpZM4SQ5gP> .

zacksleo · 2018-11-03T05:03:55Z

added these lines in docker-compose.yml fixed my issue:

    mysql:
        image: mysql:8
        command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-authentication-plugin=mysql_native_password']

Crowbrammer · 2018-11-19T04:59:42Z

Basic PDO user with a possible solution. You do not have to uninstall MySQL 8.x or even do the above (maybe... I'm not using Laradock). You just need to go to your MySQL installer, reconfigure your server, and select Legacy Authentication Method (which retains MySQL 5.x compatibility). I figured this out through trial and error after my dsn in my PDO didn't allow me to connect.

Finding the Installer in Windows
Click 'Reconfigure'
Select the bottom option

mostafaznv · 2018-12-01T14:55:46Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

you saved my life.
I changed default auth plugin to mysql native password in /etc/my.cnf and then used your command.
my problem solved

darlandieterich · 2018-12-12T12:12:29Z

if external/remote use replace 'localhost' to '%'

thpkml · 2018-12-14T14:29:01Z

The latest version of mysql :

initialize database
choose 'legacy password'
problem solved for me
simples (unless I face more issues later ;) )

yeysus · 2018-12-22T15:30:29Z

added these lines in docker-compose.yml fixed my issue:

    mysql:
        image: mysql:8
        command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-authentication-plugin=mysql_native_password']

You just saved my life @zacksleo, Thanks. Installing latest drupal 8.6.4 with MySQL 8.0.13 both via docker.

thicolares · 2019-01-05T13:30:22Z

If you want to use mysql 8 with native password (the old way) you can follow the following steps:

Add this to my.cnf in laradock\mysql\my.cnf under [mysqld]:
default-authentication-plugin=mysql_native_password
Clear the data-folder (will destroy your data), rebuild the mysql container and it should work.

Just in case someone else is using laradock too, by "clear the data-folder" I guess is:

   $ rm -rf ~/.laradock/data

stephonfraser · 2019-01-10T19:30:29Z

Don't uninstall MySQL 8. Sign into mysql using a terminal program:
mysql -u root -p
my_password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

This does the trick.

I'm using windows and I don't know which terminal program I should use for this. I really need help on this.

srinivasakumar-a · 2019-01-17T19:47:37Z

Basic PDO user with a possible solution. You do not have to uninstall MySQL 8.x or even do the above (maybe... I'm not using Laradock). You just need to go to your MySQL installer, reconfigure your server, and select Legacy Authentication Method (which retains MySQL 5.x compatibility). I figured this out through trial and error after my dsn in my PDO didn't allow me to connect.

Finding the Installer in Windows
Click 'Reconfigure'
Select the bottom option

thanks a lot for your work around and it worked for me! :)

sammysaita · 2019-01-30T19:17:34Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password';
Bravoo. This is the solution

this really helped me

leandroruel · 2019-02-23T16:08:58Z

just remove damn mysql and use mariadb 10.x as main database, it works fine.

ivan-khuda · 2019-03-25T09:55:11Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; This one helps me. Thank you!

vipankumar-JFL · 2019-05-11T04:36:58Z

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpassword';

login through command line, it works after that

parth11291 · 2019-05-18T16:02:59Z

Access denied for user 'root'@'localhost' (using password: YES)

i have already used ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpassword';

what to do now??

leewanvu · 2019-06-03T06:45:29Z

If you want to use mysql 8 with native password (the old way) you can follow the following steps:
Add this to my.cnf in laradock\mysql\my.cnf under [mysqld]:
default-authentication-plugin=mysql_native_password
Clear the data-folder (will destroy your data), rebuild the mysql container and it should work.

Just in case someone else is using laradock too, by "clear the data-folder" I guess is:
   $ rm -rf ~/.laradock/data

@thicolares Thanks so much. You save my life.

XorExpr · 2019-08-22T06:50:41Z

Don't uninstall MySQL 8. Sign into mysql using a terminal program:
mysql -u root -p
my_password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

This does the trick.

well done! thanks

Yusov · 2019-10-17T19:29:17Z

if you use mysql latest version in docker container, you can type this command into docker-compose.yaml command: --default-authentication-plugin=mysql_native_password

mysql:
image: mysql:latest
command: --default-authentication-plugin=mysql_native_password
ports:
- 3306:3306

or use oldest version mysql.`

zzkevinlim · 2019-10-31T16:11:55Z

This worked for me on CentosOS.

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

source: https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to

ahmadshiddiq · 2019-11-30T04:59:19Z

This worked for me on CentosOS.
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';
source: https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to

This works very well for me :D

djkolka · 2019-12-20T15:50:18Z

Its work for me !!!
Don't uninstall MySQL 8. Sign into mysql using a terminal program:
mysql -u root -p
my_password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

StanleyMasinde · 2020-01-08T15:36:35Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

This worked for me. Thank you

chadyred · 2020-01-19T12:53:45Z

And actually with docker-compose.yaml

version: "3"
services:
  database:
    image: mysql:8.0.19
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: test
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: test

resham58 · 2020-01-27T08:38:22Z

This worked for me on CentosOS.
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';
source: https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to

This worked for me. Thank You.

brianjohn9527 · 2020-02-04T03:48:44Z

Basic PDO user with a possible solution. You do not have to uninstall MySQL 8.x or even do the above (maybe... I'm not using Laradock). You just need to go to your MySQL installer, reconfigure your server, and select Legacy Authentication Method (which retains MySQL 5.x compatibility). I figured this out through trial and error after my dsn in my PDO didn't allow me to connect.

Finding the Installer in Windows
Click 'Reconfigure'
Select the bottom option

This really helps me fix my problem! Thanks for sharing!!!

D3M1S22 · 2020-03-24T12:05:23Z

For Mac users, just go to System Preference, MySql panel, initialize Database, if use "strong password encryption" is selected choose "Use Legacy Password encryption", insert the root password and then click ok. It will require your pc password. Thats all.

sunil-tamang · 2020-04-05T16:57:51Z

or You can follow this video if you want or reset mysql password
https://www.youtube.com/watch?v=T5wg7ekMje0

adroutos · 2020-05-08T02:14:28Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

That works for me as well!! Thanks brow!

hackdavid · 2020-05-30T05:40:20Z

if you are facing "caching_sha2_password" error during connectivity of your mysql database using python ,
go through this
https://www.youtube.com/watch?v=YNq-EuQEJos

VadneyK · 2020-06-03T20:48:20Z

You can also fix this issue using phpmyadmin:
navigate to users->
change password->
enter your new password
make sure to select "Native MYSQL authentication" on the drop down "password hashing" menu

noveleven · 2020-09-22T07:10:41Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

Amazing😄

zahangir05 · 2020-12-21T06:12:13Z

Add following line under [mysqld] of your mysql configuration file, so called my.cnf

default-authentication-plugin=mysql_native_password

sredeck · 2021-03-07T12:16:31Z

alter user 'username'@'localhost' identified with mysql_native_password by 'password';

thank you...it worked for me

aives81 · 2021-06-14T16:44:14Z

It's work for me too !!
Thank you

daniaaalarshad · 2021-07-02T21:27:01Z

Root cause

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

Corrective action

Downgrade mysql version

Thanks to tell the root of the problem :)

daniaaalarshad · 2021-07-02T21:27:57Z

Don't uninstall MySQL 8. Sign into mysql using a terminal program:
mysql -u root -p
my_password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

This does the trick.

Thankyou so much <3

GoodJob · 2021-11-02T08:13:13Z

Changing Auth Method for User didn't worked for me, but changing standard Auth method of MySQL 8 - did.

Add lines:
[mysqld]
default-authentication-plugin=mysql_native_password

At the end of:
/etc/mysql/my.cnf

Rmann-Ngoy · 2022-02-02T15:20:05Z

if you are using Mysql 8.0.+ on Mac,

Uninstall Mysql from system preferences
re-install it and choose 'legacy password'
problem solved for me.

asousajnri · 2023-04-06T01:08:43Z

Worked for me with this https://stackoverflow.com/a/55235533/13922206

rivolity · 2024-11-11T01:11:51Z

in mysql 8.4

command: [
      '--character-set-server=utf8mb4',
      '--mysql-native-password=ON',
      '--collation-server=utf8mb4_unicode_ci'
    ]

alvesrafa · 2025-01-10T18:40:29Z

    mysql:
        image: mysql:8
        command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-authentication-plugin=mysql_native_password']

You saved me, thanks!!

Im running mysql on server and i need to add this variables manually, but is easy:

you need to find your my.cnf and edit

sudo nano /etc/mysql/my.cnf

Add these lines on the end:

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-authentication-plugin=mysql_native_password

Save and restart the service:

sudo systemctl restart mysql

mvladimir7 closed this as completed Feb 23, 2018

suheb mentioned this issue Mar 13, 2018

Update mysql version to latest #1426

Merged

3 tasks

Kinmarui mentioned this issue Jun 28, 2018

current version don't work with latest mysql pacoorozco/ssham#15

Closed

Abhinav1217 mentioned this issue Jan 20, 2019

Can not connect to mysql inside docker container sqlectron/sqlectron-gui#461

Closed

danepowell mentioned this issue Mar 14, 2019

Update CONTRIBUTING.md acquia/blt#3456

Merged

kesselb mentioned this issue Apr 30, 2019

Add mysql 8.0 CI run nextcloud/server#15311

Merged

ghost mentioned this issue Aug 18, 2019

Yet another "Could not connect to the MySQL service. Please check the config file." digininja/DVWA#311

Closed

michz mentioned this issue Nov 8, 2019

issue michz/shinage-server#46

Open

LukeTowers mentioned this issue Apr 2, 2020

October CMS unable to connect to the database octobercms/october#5021

Closed

nvinayvarma189 mentioned this issue May 7, 2020

Unable to login to admin account after successful installation. Open-Web-Analytics/Open-Web-Analytics#591

Closed

shucon mentioned this issue Jan 10, 2021

[5.1.0-rc1] looping on login form phpmyadmin/phpmyadmin#16544

Closed

Mysql. The server requested authentication method unknown to the client [caching_sha2_password] #1390

Mysql. The server requested authentication method unknown to the client [caching_sha2_password] #1390

Comments

mvladimir7 commented Feb 23, 2018 • edited Loading

Info:

Issue:

Expected behavior:

Logs

mvladimir7 commented Feb 23, 2018

Root cause

Corrective action

RohitVenturit commented Jul 10, 2018

ondast commented Jul 21, 2018

undefinedplayer commented Sep 7, 2018

najamalvi commented Sep 28, 2018

MaiQuocDuy commented Oct 25, 2018

CoderBill commented Oct 26, 2018

allexiusw commented Oct 27, 2018

MaiQuocDuy commented Oct 27, 2018 via email

zacksleo commented Nov 3, 2018

Crowbrammer commented Nov 19, 2018 • edited Loading

mostafaznv commented Dec 1, 2018

darlandieterich commented Dec 12, 2018

thpkml commented Dec 14, 2018

yeysus commented Dec 22, 2018

thicolares commented Jan 5, 2019

stephonfraser commented Jan 10, 2019

srinivasakumar-a commented Jan 17, 2019

sammysaita commented Jan 30, 2019

leandroruel commented Feb 23, 2019

ivan-khuda commented Mar 25, 2019

vipankumar-JFL commented May 11, 2019

parth11291 commented May 18, 2019

leewanvu commented Jun 3, 2019 • edited Loading

XorExpr commented Aug 22, 2019

Yusov commented Oct 17, 2019 • edited Loading

zzkevinlim commented Oct 31, 2019 • edited Loading

ahmadshiddiq commented Nov 30, 2019

djkolka commented Dec 20, 2019

StanleyMasinde commented Jan 8, 2020

chadyred commented Jan 19, 2020

resham58 commented Jan 27, 2020

brianjohn9527 commented Feb 4, 2020

D3M1S22 commented Mar 24, 2020

sunil-tamang commented Apr 5, 2020

adroutos commented May 8, 2020

hackdavid commented May 30, 2020

VadneyK commented Jun 3, 2020

noveleven commented Sep 22, 2020

zahangir05 commented Dec 21, 2020

sredeck commented Mar 7, 2021

aives81 commented Jun 14, 2021

daniaaalarshad commented Jul 2, 2021

Root cause

Corrective action

daniaaalarshad commented Jul 2, 2021

GoodJob commented Nov 2, 2021

Rmann-Ngoy commented Feb 2, 2022 • edited Loading

asousajnri commented Apr 6, 2023 • edited Loading

rivolity commented Nov 11, 2024

alvesrafa commented Jan 10, 2025

mvladimir7 commented Feb 23, 2018 •

edited

Loading

Crowbrammer commented Nov 19, 2018 •

edited

Loading

leewanvu commented Jun 3, 2019 •

edited

Loading

Yusov commented Oct 17, 2019 •

edited

Loading

zzkevinlim commented Oct 31, 2019 •

edited

Loading

Rmann-Ngoy commented Feb 2, 2022 •

edited

Loading

asousajnri commented Apr 6, 2023 •

edited

Loading