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    lt  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            gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    lt  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        gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to php

Examples related to mysql

Examples related to hash

Examples related to sha