由于caching_sha2_password而导致MySQL 8.0.11错误连接 [英] MySQL 8.0.11 error connecting due to caching_sha2_password

问题描述

当我尝试连接到MySQL Workbench上的服务器时，出现错误提示

When I try to connect to server on MySQL Workbench, I get the error saying

您的连接尝试无法将用户"root"从您的主机以localhost:3306的身份发送到服务器:

Your connection attempt failed to user 'root' from your host to server as localhost:3306:

身份验证插件caching_sha2_password无法加载:

Authentication plugin caching_sha2_password cannot be loaded:

找不到指定的模块.

似乎我没有安装caching_sha2_password的模块.如何安装此插件?

It seems like I do not have the module for caching_sha2_password installed. How do I install this plugin?

我尝试按照提供的步骤操作此处.但这是我与MySQL的第一次互动，因此我不理解这些步骤.

I tried to follow steps provided here. But this is my first interaction with MySQL, so I did not understand the steps.

例如，在使用SHA-2可插拔身份验证"下，其显示为

For example, under Using SHA-2 Pluggable Authentication, it says

将这些值存储在> mysql.user系统表的plugin和authentication_string列中."

"storing those values in the plugin and authentication_string columns of the > mysql.user system table."

但是mysql.user系统表在哪里，如何访问它并存储值?

But where is the mysql.user system table and how do I access it and store the values?

任何帮助将不胜感激！

推荐答案

您在看错地方.故事是这样的:

You are looking at the wrong places. The story is this:

MySQL 8引入了一种新的身份验证方法: caching_sha2_password ，它提高了安全身份验证(sha2哈希)的性能(因此进行了缓存).这项重大更改已经很晚才成为新帐户的默认设置，以便在发布MySQL 8.0.11时无法为其准备MySQL Workbench(以及许多其他客户端工具).未经准备的客户端应用程序/库将显示上述错误.

MySQL 8 introduced a new authentication method: caching_sha2_password which improves performance (hence the caching) for a secure authentication (the sha2 hashing). This breaking change had been made the default for new accounts pretty late so that MySQL Workbench (and quite a number of other client tools) could not be made ready for it when MySQL 8.0.11 was released. Unprepared client applications/libraries will show the mentioned error.

许多所谓的解决方案"仅建议将身份验证方法切换到较旧的，安全性较低的方法(MySQL本机).这实际上是一种破解，而不是一种解决方案.并且请记住，这仅适用于默认情况下使用新的auth方法的新帐户.除非您明确更改了用户的auth方法，否则现有帐户(例如，当您将旧服务器升级到8.0时)仍然可以使用.

Many of the socalled "solutions" simply recommend to switch the authentication method to the older, less secure one (MySQL native). This is rather a hack than a solution. And keep in mind this only applies to new accounts which by default use the new auth method. Existing accounts (e.g. when you upgrade an older server to 8.0) still work as before, unless you explicitly changed the user's auth method.

与此同时，MySQL Workbench出现了，在MySQL下载页面上有一个候选发布版本( MySQL Workbench 8.0.11 RC )(请参见开发版本"选项卡).将其用于您的8.x服务器.即将推出GA版本.

Meanwhile MySQL Workbench catched up here and there's a release candidate (MySQL Workbench 8.0.11 RC) available on the MySQL download page (see the "Development Releases" tab). Use that for your 8.x server. A GA version will follow soon.

这篇关于由于caching_sha2_password而导致MySQL 8.0.11错误连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！