3

I'm getting the exact same problem as this question. The problem is that the solution isn't really organized to be easily understandable in my situation.

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

I don't have anything to initialize the DB in macs system preferences. The second answer says:

  • 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

The information given as to where to find my.cnf is found in this post and states:

mysql --help

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

It looks like there are three areas where the my.cnf file is and I have no idea what one to edit, or how I should edit it.

Another solution I have found is from this thread:

After installing MySQL, authenticate using the CLI e.g

mysql -uroot

Then run the following command to use the old authentication method:

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Lastly, flush the privileges:

FLUSH PRIVILEGES;

Now you should able to connect using SequelPro again (using the specified password).

The second solution is reported to cause sequel pro to crash by multiple users in the answers comments, so I'm guessing it's hacky.

How do I connect mysql ver 8.0.12 to sequel pro? Should I just use another GUI? It seems this problem happens with workbench as well

Just incase it gets asked I'm using Mac OSX Sierra 10.13.5

Community
  • 1
  • 1
user3325126
  • 1,284
  • 4
  • 15
  • 36

1 Answers1

3

Here is what worked for me on macOS Mojave (10.14.3) with brew installed mysql 8.

1) Add this line to my.cnf located in /usr/local/etc

default-authentication-plugin=mysql_native_password

2) Connect to your running mysql server on the command line as root user and then enter:

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

replace 'password' with the root password for your mysql server.

3) Just to be sure all the changes are picked up be the mysql server I restarted the server.

These instructions were taken from the mysql.com documentation which you can find here:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatible-connectors

Alex S.
  • 116
  • 6