Sequel Pro and MySQL connection failed

问题:

I just installed mysql on mac from Homebrew

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

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

can't figure out what I am missing

回答1:

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:

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

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

mysql + homebrew

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

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

• exit from mysql shell with exit and run sudo service mysqld restart.

Should work.

---

my.cnf

More info about my.cnf file: For homebrew mysql installs, where's my.cnf?

回答2:

If you connect to MySQL via root@127.0.0.1, make sure that you reset its password too!

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

回答3:

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

[mysqld]
default-authentication-plugin=mysql_native_password

2. Sign into mysql with mysql -u root -p
3. 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.
4. Restart mysql with e.g. brew services restart mysql