Sequel Pro 和 MySQL 连接失败

Sequel Pro and MySQL connection failed

提问by Hattori Hanzō

I just installed mysql on mac from Homebrew

我刚刚从 Homebrew 在 mac 上安装了 mysql

brew install mysql

mysql -V

mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)

from terminal it works and I can login to mysql but from Sequel Pro it says

从终端它工作，我可以登录到 mysql 但从 Sequel Pro 它说

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

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

无法连接到主机 127.0.0.1，或请求超时。

确保地址正确并且您拥有必要的权限，或者尝试增加连接超时（当前为 10 秒）。

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

can't figure out what I am missing

无法弄清楚我错过了什么

回答by Maciej Kwas

This is because Sequel Pro is not ready yet for a new kind of user login, as the error states: there is no driver. Quick fix for non-homebrew installs:

这是因为 Sequel Pro 尚未准备好进行新的用户登录，正如错误所述：没有驱动程序。非自制软件安装的快速修复：

Apple Logo > System Preferences > MySQL > Initialize Database, then type your new password and select 'Use legacy password'

Apple Logo > System Preferences > MySQL > Initialize Database，然后输入您的新密码并选择“使用旧密码”

After restart you should be able to connect. Do it only on fresh installs, because you may lost your db tables otherwise.

重启后应该就可以连接了。仅在全新安装时执行此操作，否则您可能会丢失 db 表。

mysql + homebrew

mysql + 自制软件

Basically you will have to perform some actions manually, however- your database data won't be deleted like in solution above

基本上您必须手动执行一些操作，但是 - 您的数据库数据不会像上面的解决方案那样被删除

• Go to [my.cnf][1] file and in section [mysqld]add line:

  default-authentication-plugin=mysql_native_password

• Login to mysql server from terminal: run mysql -u root -p, then inside shell execute this command (replacing [password]with your actual password):

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';

• exit from mysql shell with exitand run brew services restart mysql.

• 转到 [my.cnf][1] 文件并在部分[mysqld]添加行：

  default-authentication-plugin=mysql_native_password

• 从终端登录到 mysql 服务器： run mysql -u root -p，然后在 shell 中执行此命令（将[password]替换为您的实际密码）：

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';

• 退出 mysql shellexit并运行brew services restart mysql。

Should work.

应该管用。

---

---

my.cnf

我的.cnf

The my.cnf file is located in /etc/my.cnf on Unix/Linux

my.cnf 文件位于 Unix/Linux 上的 /etc/my.cnf

回答by Aidin

TL;DR: Sequel Pro is dead since 2016. Don't downgrade your DB because of a tool. Move on to an alternative tool.

TL;DR：Sequel Pro 自 2016 年起就死了。不要因为工具而降级您的数据库。继续使用替代工具。

---

---

All the other solutions here are recommending changing your DB settings (making it less secure, as advertised by MySQL) for the tool you are using. That's not acceptable to me.

这里的所有其他解决方案都建议为您正在使用的工具更改数据库设置（降低安全性，如 MySQL 所宣传的那样）。这是我不能接受的。

I have always been a huge fan of Sequel Pro, even donated to it. But, with all my passion and love, I am sorry if the tool doesn't have any release since 2016. YOLO, and I need to move on!

我一直是 Sequel Pro 的忠实粉丝，甚至捐赠了它。但是，以我所有的热情和爱，如果该工具自 2016 年以来没有任何版本，我很抱歉。YOLO，我需要继续前进！

The alternative I found (from https://stackoverflow.com/a/55235533/2321594, thanks to @arcseldon) is DBeaverwhich supports MySQL 8's new authentication (non-legacy) method.

我发现的替代方案（来自https://stackoverflow.com/a/55235533/2321594，感谢@arcseldon）是DBeaver，它支持 MySQL 8 的新身份验证（非传统）方法。

PS. The only trick in the tool side, not the DB sideis when you are creating a MySQL 8 connection you might need to go to "Driver Properties" (later can be found in Edit Connection) and turn the value of allowPublicKeyRetrievalto true.

附注。工具方面而非数据库方面的唯一技巧是，当您创建 MySQL 8 连接时，您可能需要转到“驱动程序属性”（稍后可以在“编辑连接”中找到）并将 的值allowPublicKeyRetrieval变为true。

I needed this to connect to my MySQL container created using Docker. To have the IP of MySQL be visible to the outside, for any other application in your ecosystem (not just this tool), you should either create a new user in MySQL, or pass -e MYSQL_ROOT_HOST=%in the run-time or as an ENV.

我需要它来连接到我使用 Docker 创建的 MySQL 容器。为了让 MySQL 的 IP 对外可见，对于生态系统中的任何其他应用程序（不仅仅是这个工具），您应该在 MySQL 中创建一个新用户，或者-e MYSQL_ROOT_HOST=%在运行时或作为 ENV传递。

回答by Ilias Karim

1. Assuming you don't have a mysqlconfiguration, echo the following to ~/.my.conf

1. 假设您没有mysql配置，请回显以下内容~/.my.conf

[mysqld]
default-authentication-plugin=mysql_native_password

1. Sign into mysqlwith mysql -u root -p
2. Set the root user password with ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[PASSWORD]';where [PASSWORD]is a password of your choosing.
3. Restart mysqlwith e.g. brew services restart mysql

2. 登录mysql使用mysql -u root -p
3. 与设置root用户密码ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[PASSWORD]';哪里[PASSWORD]是你选择的密码。
4. 用eg重启mysqlbrew services restart mysql

回答by Xeno

If you connect to MySQL via [email protected], make sure that you reset its password too!

如果您通过 [email protected] 连接到 MySQL，请确保您也重置其密码！

ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '[password]';

ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '[password]';

回答by amrendra

If anyone facing this issue and installed MySQL version >8through .dmgdownload from the official link. In that case please use this guideline.

如果有人遇到此问题并MySQL version >8通过.dmg从官方链接下载安装。在这种情况下，请使用本指南。