MySQL 无法加载身份验证插件“caching_sha2_password”

Authentication plugin 'caching_sha2_password' cannot be loaded

提问by Aman Aggarwal

I am connecting MySQL - 8.0 with MySQL Workbench and getting the below error:

我将 MySQL - 8.0 与 MySQL Workbench 连接，并收到以下错误：

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

无法加载身份验证插件“caching_sha2_password”：dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

I have tried with other client tool as well.

我也尝试过其他客户端工具。

Any solution for this?

有什么解决办法吗？

采纳答案by Aman Aggarwal

You can change the encryption of the user's password by altering the user with below Alter command :

您可以通过使用以下 Alter 命令更改用户来更改用户密码的加密：

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

OR

或者

We can avoid this error by make it work with old password plugin:

我们可以通过使用旧密码插件来避免这个错误：

First change the authentication plugin in my.cnf file for Linux / my.ini file in Windows:

首先更改Linux 的 my.cnf 文件中的身份验证插件/ Windows 中的 my.ini 文件：

[mysqld]

default_authentication_plugin=mysql_native_password

[mysqld]

default_authentication_plugin=mysql_native_password

Restart the mysql server to take the changes in affect and try connecting via MySQL with any mysql client.

重新启动 mysql 服务器以使更改生效并尝试通过 MySQL 与任何 mysql 客户端连接。

If still unable to connect and getting the below error:

如果仍然无法连接并出现以下错误：

Unable to load plugin 'caching_sha2_password'

It means your user needs the above plugin. So try creating new user with create user or grant command after changing default plugin. then new user need the native plugin and you will able to connect MySQL.

这意味着您的用户需要上述插件。因此，尝试在更改默认插件后使用 create user 或 grant 命令创建新用户。然后新用户需要本机插件，您将能够连接 MySQL。

Thanks

谢谢

回答by Santhosh Sivan

Note: For MAC OS

注意：对于 MAC 操作系统

1. Open MySQL from System Preferences > Initialize Database >
2. Type your new password.
3. Choose 'Use legacy password'
4. Start the Server again.
5. Now connect the MySQL Workbench

1. 从系统偏好设置 > 初始化数据库 > 打开 MySQL
2. 输入您的新密码。
3. 选择“使用旧密码”
4. 再次启动服务器。
5. 现在连接 MySQL Workbench

回答by twocold

you can change the encryption of the password like this.

您可以像这样更改密码的加密。

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

回答by Araz Ghazaryan

For Windows 10:

对于 Windows 10：

Open the command prompt:

打开命令提示符：

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u root -p
Enter password: *********

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';
Query OK, 0 rows affected (0.10 sec)

mysql> exit

Alternatively, you can change the my.iniconfiguration as the following:

或者，您可以按如下方式更改my.ini配置：

[mysqld]

default_authentication_plugin=mysql_native_password

Restart the MySQL Server and open the Workbench again.

重新启动 MySQL 服务器并再次打开工作台。

回答by Montecarlo

Currently (on 2018/04/23), you need to download a development release. The GAones do not work.

目前（2018 年 4 月 23 日），您需要下载开发版本。该GA那些不工作。

I was not able to connect with the latest GA version (6.3.10).

我无法连接到最新的 GA 版本 (6.3.10)。

It worked with mysql-workbench-community-8.0.11-rc-winx64.msi(from https://dev.mysql.com/downloads/workbench/, tab Development Releases).

它与mysql-workbench-community-8.0.11-rc-winx64.msi（来自https://dev.mysql.com/downloads/workbench/，选项卡Development Releases）一起工作。

回答by axelferreira

I had the same problem, but the answer by Aman Aggarwal didn't work for me with a Docker container running mysql 8.X. I loged in the container

我遇到了同样的问题，但是 Aman Aggarwal 的答案对运行 mysql 8.X 的 Docker 容器不起作用。我登录了容器

docker exec -it CONTAINER_ID bash

then log into mysql as root

然后以root身份登录mysql

mysql --user=root --password

Enter the password for root (Default is 'root') Finally Run:

输入 root 的密码（默认为“root”） 最后运行：

ALTER USER 'username' IDENTIFIED WITH mysql_native_password BY 'password';

You're all set.

你都准备好了。

回答by ImtiazeA

I was installing MySQL on my Windows 10 PC using "MySQL Web Installer" and was facing the same issue while trying to connect using MySQL workbench. I fixed the issue by reconfiguring the server form the Installer window.

我正在使用“ MySQL Web Installer”在我的 Windows 10 PC 上安装 MySQL，并且在尝试使用 MySQL 工作台进行连接时遇到了同样的问题。我通过从安装程序窗口重新配置服务器解决了这个问题。

Clicking on the "Reconfigure" option it will allow to reconfigure the server. Click on "Next" until you reach "Authentication Method".

单击“重新配置”选项将允许重新配置服务器。单击“下一步”，直到到达“身份验证方法”。

Once on this tab, use the second option "Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)".

在此选项卡上，使用第二个选项“使用传统身份验证方法（保留 MySQL 5.x 兼容性）”。

Keep everything else as is and that is how I solved my issue.

保持其他一切不变，这就是我解决问题的方式。

回答by arcseldon

Ok, wasted a lot of time on this so here is a summary as of 19 March 2019

好的，浪费了很多时间，所以这里是截至 2019 年 3 月 19 日的摘要

If you are specifically trying to use a Docker image with MySql 8+, and then use SequelProto access your database(s) running on that docker container, you are out of luck.

如果您专门尝试将 Docker 映像与MySql 8+一起使用，然后使用SequelPro访问在该 docker 容器上运行的数据库，那么您就不走运了。

See the sequelpro issue 2699

见sequelpro issue 2699

My setup is sequelpro 1.1.2 using docker desktop 2.0.3.0 (mac - mojave), and tried using mysql:latest (v8.0.15).

我的设置是 sequelpro 1.1.2 使用 docker desktop 2.0.3.0 (mac - mojave)，并尝试使用 mysql:latest (v8.0.15)。

As others have reported, using mysql 5.7 works with nothing required:

正如其他人所报告的那样，使用 mysql 5.7 不需要什么：

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:5.7

Of course, it is possible to use MySql 8+ on docker, and in that situation (if needed), other answers provided here for caching_sha2_passwordtype issues do work. But sequelpro is a NO GO with MySql 8+

当然，可以在 docker 上使用 MySql 8+，在这种情况下（如果需要），此处针对caching_sha2_password类型问题提供的其他答案确实有效。但是 sequelpro 不适合 MySql 8+

Finally, I abandoned sequelpro (a trusted friend from back in 2013-2014) and instead installed DBeaver. Everything worked out of the box. For docker, I used:

最后，我放弃了 sequelpro（2013-2014 年值得信赖的朋友），而是安装了DBeaver。一切都开箱即用。对于码头工人，我使用了：

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest --default-authentication-plugin=mysql_native_password

You can quickly peek at the mysql databases using:

您可以使用以下命令快速查看 mysql 数据库：

docker exec -it mysql1 bash

mysql -u root -p

show databases;

回答by u5602117

like this?

像这样？

docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql --default-authentication-plugin=mysql_native_password
mysql -uroot --protocol tcp

Try in PWD

试试手残

https://github.com/GitHub30/docs/blob/change-default_authentication_plugin/mysql/stack.yml

https://github.com/GitHub30/docs/blob/change-default_authentication_plugin/mysql/stack.yml

or You shoud use MySQL Workbench 8.0.11.

或者您应该使用 MySQL Workbench 8.0.11。

回答by Ahmed Ziyad

• Open MySQL Command Line Client

• Create a new user with a new pass

• 打开 MySQL 命令行客户端

• 使用新通行证创建新用户

Considering an example of a path to a bin folder on top, here's the code you need to run in the command prompt, line by line:

考虑顶部 bin 文件夹路径的示例，这是您需要在命令提示符下逐行运行的代码：

cd C:\Program Files\MySQL\MySQL Server 5.7\bin
MySQL -u root -p    
current password...***  
CREATE USER 'nativeuser'@'localhost'  
IDENTIFIED WITH mysql_native_password BY 'new_password';

• Then, you can access Workbench again (you should be able to do that after creating a new localhost connection and using the new credentials to start using the program).

• 然后，您可以再次访问 Workbench（在创建新的 localhost 连接并使用新凭据开始使用该程序后，您应该能够这样做）。

Set up a new local host connection with the user name mentioned above (native user), login using the password (new_password)

用上面提到的用户名（本地用户）建立一个新的本地主机连接，使用密码（new_password）登录

Courtesy: UDEMY FAQs answered by Career365 Team

礼貌：由 Career365 团队回答的 UDEMY 常见问题解答