Sequel Pro and MySQL connection failed
MysqlDatabase ConnectionSequelproMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
This is because Sequel Pro is not ready yet for a new kind of user login, as the error states: there is no driver.
mysql + homebrew
Basically you will have to perform some actions manually, however- your database data won't be deleted like in solution below
-
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 (replacing [password] with your actual password):ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';
-
exit from mysql shell with
exit
and runbrew services restart mysql
.
Should work.
Quick fix (destructive method)
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.
my.cnf
The my.cnf file is located in /etc/my.cnf on Unix/Linux
For those who is still struggling with Sequel Pro problems: Sequel Pro was a great product, but with tons of unresolved issues and last release being dated to 2016 perhaps it's a good idea to look for some alternatives. There is a fork of SequelPro called SequelAce that seems to be pretty stable and up-to-date, it keeps similar functionality, similar look and feel, yet at the same time it is devoid of old Sequel Pro problems Alternatives
Solution 2 - Mysql
TL;DR: Sequel Pro is dead since 2016. Don't downgrade your DB because of a tool. Move on to an alternative tool.
Update 2020: Sequel Pro is officially dead but unofficially alive! You can find the "nightly" builds that don't have this issue (i.e. support Mysql 8 auth) in here: https://sequelpro.com/test-builds
Update 2021: Sequel Ace is a good similar alive alternative: https://github.com/Sequel-Ace/Sequel-Ace#installation (Credits to Maciej Kwas's answer)
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.
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!
The alternative I found (from https://stackoverflow.com/a/55235533/2321594, thanks to @arcseldon) is DBeaver which supports MySQL 8's new authentication (non-legacy) method.
PS. The only trick in the tool side, not the DB side is 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 allowPublicKeyRetrieval
to 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.
Solution 3 - Mysql
-
Assuming you don't have a mysql configuration, echo the following to
~/.my.conf
[mysqld] default-authentication-plugin=mysql_native_password
-
Sign into mysql with
mysql -u root -p
-
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. -
Restart mysql with e.g.
brew services restart mysql
Solution 4 - Mysql
Sequel Pro is officially dead and no longer supports newer MySql features. However, the good news is that it was replaced by Sequel Ace which is available on GitHub and App store. The app is free of charge and looks like official replacement for Sequel Pro as the post was made by one of the collaborators of Sequel Pro.
Ps. I decided to post this as an answer as others have not mentioned that there is now an up-to-date replacement for Sequel Pro
Solution 5 - Mysql
If you connect to MySQL via [email protected], make sure that you reset its password too!
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '[password]';
Solution 6 - Mysql
It's working for me. if you are getting this error :
> 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):
Plz try this solution
Solution 7 - Mysql
If anyone facing this issue and installed MySQL version > 8
through .dmg
file; downloaded it from the official link. In that case please use this guideline. I am adding the same details below in case the link will not be available in the future.
This issue is because you're using the new Strong Password Encryption
which is a new feature in MySQL 8, if you installed MySQL 8 using the .dmg
file, you can go to System Preferences > MySQL
and then click on Initialize Database
type your password and select the second option Use Legacy Password Encryption
.