php mysqli_connect：客户端未知的身份验证方法 [caching_sha2_password]

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

提问by Guti_Haz

I am using php mysqli_connectfor login to a MySQL database (all on localhost)

我正在使用 phpmysqli_connect登录到 MySQL 数据库（都在本地主机上）

<?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 表：

MySQL Server ini File:

MySQL 服务器 ini 文件：

[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_passwordin the MySQL Server ini file, it's not possible at all to login with user1 or user2;

与caching_sha2_password在MySQL服务器ini文件，它不可能在所有与用户1或用户2登录;

error: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in...

错误：mysqli_connect()：服务器请求客户端未知的身份验证方法 [caching_sha2_password] 在...

with mysql_native_passwordin the MySQL Server ini file, it's possible to login with user1, but with user2, same error;

与mysql_native_password在MySQL服务器ini文件，有可能与USER1登录，但用户2，同样的错误;

---

---

how can I login using caching_sha2_passwordon the mySql Server?

如何caching_sha2_password在 mySql Server 上使用登录？

采纳答案by abeyaz

edit: As of PHP 7.4, this is no longer an issue.

编辑：从 PHP 7.4 开始，这不再是问题。

Currently, php mysqli extension do not support new caching_sha2 authentication feature. You have to wait until they release an update.

目前，php mysqli 扩展不支持新的 caching_sha2 身份验证功能。你必须等到他们发布更新。

Check related post from mysql developers : https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

检查来自 mysql 开发人员的相关帖子：https: //mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-thinkations/

They didnt mention PDO, maybe you should try to connect with PDO.

他们没有提到PDO，也许你应该尝试连接PDO。

回答by u8979354

I solve this by SQL command:

我通过 SQL 命令解决了这个问题：

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

由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

由https://dev.mysql.com/doc/refman/8.0/en/create-user.html引用

this works for me

这对我有用

回答by Eranda Jayamaha

ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

Remove quotes (') after ALTER USERand keep quote (') after mysql_native_password BY

删除后的引号 (')ALTER USER并保留后的引号 (')mysql_native_password BY

It is working for me also.

它也对我有用。

回答by CJ Dennis

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

如果您使用的是 Windows 并且根本无法使用caching_sha2_password，您可以执行以下操作：

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"

1. 重新运行 MySQL 安装程序
2. 选择 MySQL Server 旁边的“Reconfigure”（最上面的项目）
3. 单击“下一步”，直到进入“身份验证方法”
4. 将“使用强密码加密进行身份验证（推荐）”更改为“使用传统身份验证方法（保留 MySQL 5.X 兼容性）”
5. 点击下一步”
6. 在帐户和角色中输入您的根帐户密码，然后单击“检查”
7. 点击下一步”
8. 继续单击“下一步”，直到进入“应用配置”
9. 点击“执行”

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

安装程序将为您进行所需的所有配置更改。

回答by Chinnon Santos

It's working for me (PHP 5.6 + PDO / MySQL Server 8.0 / Windows 7 64bits)

它对我有用（PHP 5.6 + PDO / MySQL Server 8.0 / Windows 7 64bits）

Edit the file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini:

编辑文件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...

在 Windows 和 MySQL Shell 中重置 MySQL 服务...

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

回答by dBags

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

像许多人一样，我也遇到了同样的问题。虽然用户设置为使用 mysql_native_password，并且我可以从命令行连接，但我可以让 mysqli() 连接的唯一方法是添加

default-authentication-plugin=mysql_native_password

默认身份验证插件=mysql_native_password

to the [mysqld] section of, in my setup on ubuntu 19.10, /etc/mysql/mysql.conf.d/mysqld.cnf

到 [mysqld] 部分，在我在 ubuntu 19.10 上的设置中，/etc/mysql/mysql.conf.d/mysqld.cnf

回答by Pavel Niedoba

Now you can upgrade to PHP7.4 and MySQL will go with caching_sha2_passwordby default, so default MySQL installation will work with mysqli_connectNo configuration required.

现在您可以升级到 PHP7.4 并且 MySQL 将caching_sha2_password默认使用，因此默认 MySQL 安装将mysqli_connect无需配置即可使用。

回答by JRonald Moreno

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.

我ALTER USER 'root' @ 'localhost' identified with mysql_native_password BY 'root123';在命令行中运行了以下命令 ，最后在本地服务中重新启动 MySQL。

回答by imjesr

I tried this in Ubuntu 18.04 and is the only solution that worked for me:

我在 Ubuntu 18.04 中尝试过这个，这是唯一对我有用的解决方案：

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

回答by kp123

If you're on a Mac, here's how to fix it. This is after tons of trial and error. Hope this helps others..

如果您使用的是 Mac，这里是修复它的方法。这是经过大量的反复试验之后。希望这对其他人有帮助..

Debugging:

调试：

$mysql --verbose --help | grep my.cnf

$ which mysql
/usr/local/bin/mysql

Resolution: nano /usr/local/etc/my.cnf

解析：nano /usr/local/etc/my.cnf

Add: default-authentication-plugin=mysql_native_password

添加：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

最后运行：brew services restart mysql