无法从 Windows 桌面应用程序“MySQL Workbench"连接到 MySQL 数据库(在 WSL2 上运行)； [英] Cannot connect to MySQL database (running on WSL2) from Windows Desktop Application "MySQL Workbench"

问题描述

我在适用于 Linux 的 Windows 子系统(WSL2 版本)上设置了 MySQL.我对 MySQL 比较陌生，但我已经确认了以下内容:

I set up MySQL on Windows Subsystem for Linux (WSL2 version). I'm relatively new to MySQL, but I have confirmed the following:

• 它正在运行(ps ax | grep mysqld 返回一个值)
• 它在默认主机 127.0.0.1
上运行
• 它在默认端口 3306
上运行

• It is running (ps ax | grep mysqld returns a value)
• It is running on default host 127.0.0.1
• It is running on default port 3306

要登录到 mysql shell，我使用命令 sudo mysql -u root -p.没有 sudo，我无法登录 shell.

To login to the mysql shell, I use the command sudo mysql -u root -p. Without sudo, I am unable to login to the shell.

我认为这个问题与运行 MySQL 服务的主机有关，但我不知道如何更改它并正确连接.以下是 MySQL Workbench 中连接设置的屏幕截图.

I assume that this issue has something to do with the host that the MySQL service is running on, but I have no idea how to change that and properly connect. Below is a screenshot of the connection setup in MySQL Workbench.

以下是我使用显示的设置和我的 root 用户密码时出现的错误.

And below is the error that I get when I use the settings shown and my root user password.

推荐答案

事实证明，这与 WSL 完全无关，而是与 MySQL 用户的身份验证方法有关.

Turns out, this had nothing to do with WSL at all, but rather the authentication method for the MySQL user.

从 MySQL 5.5.10 版本开始，用户可以使用 auth_socket 身份验证.就我而言，我使用 Linux apt 存储库来配置和安装 MySQL，并将其设置为默认身份验证方法，如以下命令的输出所示:

As of MySQL version 5.5.10, users have the ability to use auth_socket authentication. In my case, I used the Linux apt repository to configure and install MySQL, and this was set as the default authentication method, as shown by the output of the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

MySQL Workbench 不支持这种类型的身份验证，因此，您必须恢复到旧的身份验证方法，mysql_native_password.

MySQL Workbench does not support this type of authentication, and therefore, you must revert back to the old method of authentication, mysql_native_password.

要做到这一点，请在以 root 或您尝试连接到 MySQL Workbench 的任何用户身份登录时运行以下命令:

To do this, run the following command while logged in as root, or whatever user you are trying to connect to MySQL Workbench with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

这样做之后，MySQL 工作台连接正常.

After doing that, MySQL workbench connects fine.

这篇关于无法从 Windows 桌面应用程序“MySQL Workbench"连接到 MySQL 数据库(在 WSL2 上运行)；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！