162

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.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?

4
  • Does PDO support this? I've seen other reports about mysqli.
    – tadman
    Apr 25, 2018 at 16:34
  • Answer posted by @黃皓哲 should be marked as accepted answer.
    – FIL
    Feb 4, 2019 at 9: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'; Mar 26, 2021 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! Jun 16, 2022 at 13:59

19 Answers 19

333

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

20
  • 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, 2018 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 Jul 25, 2018 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. Oct 12, 2018 at 16:27
  • 11
    this should be accepted answer for workaround solution
    – Yohanes AI
    Jan 29, 2019 at 17:24
  • 2
    This worked for me with php7.0 and mysql 8.0 on CentOS just make sure you also restart mysqld
    – cgclip
    Jul 25, 2019 at 18:51
100

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.

7
  • 4
    PDO has the same problem
    – Machavity
    Jun 18, 2019 at 15:06
  • 1
    @gen Based on this bug, looks like it's working as of 7.4.2
    – Machavity
    Mar 22, 2020 at 0:58
  • 9
    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, 2020 at 17:00
  • 1
    I use docker (apache+php in a container and mysql in other container) and this solved my Issue. Thanks! Nov 1, 2020 at 11:03
  • 1
    PHP 7.4.3 here. Had to change to mysql_native_password for it to work.
    – RaminS
    Jul 28, 2021 at 0:56
45
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.

3
  • 4
    That was the only solution worked for me on localhost server using WAMP server :)
    – Jodyshop
    Feb 23, 2020 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, 2020 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, 2021 at 1:10
33

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

5
  • 4
    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, 2019 at 7: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, 2021 at 7:30
  • 1
    after adding this to config I get the same error
    – Igor
    Jan 28, 2022 at 8: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, 2023 at 1:07
  • You saved my hours... After changing the user's settings for MySQL native password, on Ubuntu 20.04 I had to add this line to /etc/mysql/mysql.conf.d/mysqld.cnf, and it worked like a charm. Thanks Sep 6, 2023 at 6:01
32

If you're on Windows and it's not possible to use caching_sha2_password at all, you can do the following:

  1. rerun the MySQL Installer
  2. select "Reconfigure" next to MySQL Server (the top item)
  3. click "Next" until you get to "Authentication Method"
  4. change "Use Strong Password Encryption for Authentication (RECOMMENDED)" to "Use Legacy Authentication Method (Retain MySQL 5.X Compatibility)
  5. click "Next"
  6. enter your Root Account Password in Accounts and Roles, and click "Check"
  7. click "Next"
  8. keep clicking "Next" until you get to "Apply Configuration"
  9. click "Execute"

The Installer will make all the configuration changes needed for you.

2
  • 2
    man after a year of upvoting this answer my db was hacked...
    – Code Tree
    Sep 26, 2020 at 1: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. Jul 27, 2021 at 23:52
21

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';
1
  • 1
    it works, thank you... we must config that 2 things: mysql conf and alter user
    – danisupr4
    May 15, 2020 at 18:34
9

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.

1
  • 2
    no spaces here 'root' @ 'localhost' should be read instead ALTER USER 'root'@'localhost' identified with mysql_native_password BY 'root123';
    – d1jhoni1b
    Aug 12, 2020 at 6:28
7

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

1
  • Needed this... Thanks!
    – nykc
    Sep 5, 2020 at 13:45
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.

1
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';
1
  • 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 1, 2022 at 23:47
1

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

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
0

In my case when I was using WAMP server I fixed it using following

  1. 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.

enter image description here

  1. Use the port number when you create mysqli object.

enter image description here

Using the above steps I was able to run the program successfully.

0

phpMyAdmin GUI way

Note: This method may not work if you have the problem with root user account.

  1. Login to phpMyAdmin as root.
  2. Switch to "User Accounts" tab.
  3. Select the username you get error with.
  4. From the top buttons, switch to "Login Information" section.
  5. 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.
  6. Save it by clicking on the "Go" button at the bottom.
  7. Return back to terminal and enjoy. :)
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.

0

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

0

In my case:

I'm using PHP 5.6 and MYSQL 8. Since MYSQL 8 comes with caching_sha2_password as default authentication and PHP 5.6 doesn't support it yet, i have to set MYSQL 8 authentication to mysql_native_password by doing:

add

default-authentication-plugin=mysql_native_password 

to the end of line of /etc/mysql/conf.d/mysql.cnf configuration file in ubuntu 22.04

0

I have the similar issue. Before solving it lets first understand the problem.

MySQL 5.7 and earlier: These versions use mysql_native_password as the default authentication plugin. This plugin uses a simple password hashing algorithm and is widely supported by various MySQL client libraries, including older PHP versions.

MySQL 8.x: The default authentication plugin is caching_sha2_password, which provides better security through the SHA-256 hashing algorithm. This newer authentication method was not initially supported by all MySQL client libraries, including older versions of PHP's MySQL extensions (like mysqli and PDO).

PHP 7.2 and earlier: These versions were released before or around the time MySQL 8.0 was released. As such, the default MySQL extensions in these PHP versions may not support caching_sha2_password out of the box.

PHP 7.4 and later: These versions have improved support for MySQL 8.x and its default caching_sha2_password authentication plugin. Thus, they can connect to MySQL 8.x databases using the new authentication method without requiring configuration changes.

So the problem is when you are using latest MySQL (v8.+) with old PHP ( 7.3.x and earlier) that does not have support for caching_sha2_password you might get similar issue.

to solve this issue you need change mysql default authentication plugin from caching_sha2_password to mysql_native_password

Update my.cnf then restart Mysql

[mysqld]
default_authentication_plugin= mysql_native_password

Login into mysql as root using terminal then run these

- when your mysql host is 'localhost'

ALTER USER 'dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root@123';

- when your mysql host is other than 'localhost', like 10.10.0.2 etc.

ALTER USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'root@123';

[Note] make sure you replace dbuser with your actual database username.

- finally flush all privilege

FLUSH PRIVILEGES;
-4

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

?>

Not the answer you're looking for? Browse other questions tagged or ask your own question.