PHP 与 MySQL 8.0+ 错误：服务器请求客户端未知的身份验证方法

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

提问by mohammed yaser

I'm running MySQL version 8 on PHP 7.0.

我在 PHP 7.0 上运行 MySQL 版本 8。

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

当我尝试从 PHP 连接到我的数据库时出现以下错误：

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

连接错误：SQLSTATE[HY000] [2054] 服务器请求客户端未知的身份验证方法

PHP might show this error

PHP 可能会显示此错误

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

警告：mysqli_connect()：服务器请求客户端未知的身份验证方法 [caching_sha2_password] 在 D:\xampp\htdocs\reg\server.php 第 10 行

How can I fix this problem?

我该如何解决这个问题？

回答by Elias Gikonyo

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

@mohammed，这通常归因于您的 mysql 数据库正在使用的身份验证插件。

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.

默认情况下，出于某种原因，mysql 8 默认插件是 auth_socket。大多数情况下，应用程序希望使用密码登录到您的数据库。

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:

如果您还没有更改您的 mysql 默认身份验证插件，您可以这样做：
1. 以 root 身份登录到 mysql
2. 运行此 sql 命令：

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.

将“密码”替换为您的根密码。如果您的应用程序没有使用 root 用户登录到您的数据库，请将上述命令中的“root”用户替换为您的应用程序使用的用户。

Digital ocean expounds some more on this here Installing Mysql

数字海洋在这里对此进行了更多说明安装 Mysql

回答by michail_w

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

您必须更改 MySQL 设置。编辑 my.cnf 文件并将此设置放在 mysqld 部分：

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

以上命令将使默认身份验证机制的更改生效。

回答by Taioli Francesco

i've try a lot of ways, but only this work for me

我尝试了很多方法，但只有这个对我有用

thanks for workaround

感谢您的解决方法

check your .env

检查你的 .env

MYSQL_VERSION=latest

then type this command

然后输入这个命令

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

(login as root)

（以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 :

然后转到 phpmyadmin 并以以下身份登录：

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

• 主机-> mysql
• 用户-> 根
• 密码-> 根

hope it help

希望它有帮助

回答by Stuperfied

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

这里没有一个答案对我有用。我必须做的是：

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

1. 重新运行安装程序。
2. 选择产品“MySQL Server”旁边的快速操作“重新配置”
3. 浏览选项直到您到达身份验证方法并选择“使用传统身份验证方法”

After that it works fine.

之后它工作正常。

回答by shivinder thakur

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.

面对同样的问题，我无法使用 mysql 版本 8 运行 wordpress docker 容器，因为它的默认身份验证机制是 caching_sha2_password 而不是 mysql_native_password。

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

为了解决这个问题，我们必须将默认身份验证机制重置为 mysql_native_password。

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

在 mysql 安装中找到 my.cnf 文件，通常在 linux 机器上它位于以下位置 - /etc/mysql

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

编辑 my.cnf 文件并在标题 [mysqld] 下添加以下行

default_authentication_plugin= mysql_native_password

default_authentication_plugin= mysql_native_password

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

保存文件，然后使用 root 用户登录到 mysql 命令行

run command FLUSH PRIVILEGES;

运行命令 FLUSH PRIVILEGES;

回答by CIRCLE

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.

我正在使用 Laravel Lumen 构建一个小型应用程序。
对我来说，这是因为我没有在 .env 文件中定义 DB_USERNAME。

DB_USERNAME=root

Setting this solved my problem.

设置这个解决了我的问题。

回答by sanjaya

In my.cnf file check below 2 steps.

在 my.cnf 文件中检查以下 2 个步骤。

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

• 检查这个值 -

  old_passwords=0;

  应该是0。

• 也检查一下——

  [mysqld] default_authentication_plugin= mysql_native_password 另一个要检查的值是确保

  [mysqld] 部分应该是这样的。

回答by Tr?n ??c

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

as my config.inc.phpfile, 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.

作为我的config.inc.php文件，重新启动了 apache 服务器并且它工作了。我仍然对此表示怀疑，所以我停止了 apache 和 mysql 服务器并再次启动它们，现在它可以工作了。