Caching sha2 password ошибка - Oshibku.top - решение и исправление самых разных ошибок

Ok, wasted a lot of time on this so here is a summary as of 19 March 2019

If you are specifically trying to use a Docker image with MySql 8+, and then use SequelPro to access your database(s) running on that docker container, you are out of luck.

See the sequelpro issue 2699

My setup is sequelpro 1.1.2 using docker desktop 2.0.3.0 (mac — mojave), and tried using mysql:latest (v8.0.15).

As others have reported, using mysql 5.7 works with nothing required:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:5.7

Of course, it is possible to use MySql 8+ on docker, and in that situation (if needed), other answers provided here for caching_sha2_password type issues do work. But sequelpro is a NO GO with MySql 8+

Finally, I abandoned sequelpro (a trusted friend from back in 2013-2014) and instead installed DBeaver. Everything worked out of the box. For docker, I used:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest --default-authentication-plugin=mysql_native_password

You can quickly peek at the mysql databases using:

docker exec -it mysql1 bash

mysql -u root -p

show databases;

Источник

Ok, wasted a lot of time on this so here is a summary as of 19 March 2019

If you are specifically trying to use a Docker image with MySql 8+, and then use SequelPro to access your database(s) running on that docker container, you are out of luck.

See the sequelpro issue 2699

My setup is sequelpro 1.1.2 using docker desktop 2.0.3.0 (mac — mojave), and tried using mysql:latest (v8.0.15).

As others have reported, using mysql 5.7 works with nothing required:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:5.7

Finally, I abandoned sequelpro (a trusted friend from back in 2013-2014) and instead installed DBeaver. Everything worked out of the box. For docker, I used:

docker run -p 3306:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest --default-authentication-plugin=mysql_native_password

You can quickly peek at the mysql databases using:

docker exec -it mysql1 bash

mysql -u root -p

show databases;

Источник

Nov 24, 2021

Summary

You have installed MySQL 8 and are unable to connect your database using your MySQL client (Sequel Pro, HeidiSQL etc). Every attempt to connect using your MySQL client results in the following error

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

Authentication plugin ‘caching_sha2_password’ cannot be loaded. The specific module can not be found

Reason

As of MySQL 8.0, caching_sha2_password is now the default authentication plugin rather than mysql_native_password which was the default in previous versions. This means that clients (Sequel Pro, HeidiSQL etc) that rely on the mysql_native_password won’t be able to connect because of this change.

Resolution

1) You can, at a server level, revert to the mysql_native_password mechanism by adding the following to your MySQL configuration files

[mysqld]
default_authentication_plugin=mysql_native_password

2) You can, at a user level, revert to the mysql_native_password mechanism via the following process

Open a terminal window and connect to your MySQL instance via the command line

mysql -u [USERNAME] -p

Enter your MySQL password and press enter and you should be logged into your MySQL instance.

Now run the following SQL command, replacing [USERNAME], [PASSWORD] and [HOST] as appropriate.

Note: [HOST] can be the IP address of your computer which would allow access from your computer only or, in the case of a local development environment, you can use % to allow from any host.

ALTER USER '[USERNAME]'@'[HOST]' \
  IDENTIFIED WITH mysql_native_password \
  BY '[PASSWORD]';

ALTER USER '[USERNAME]'@'%' \
  IDENTIFIED WITH mysql_native_password \
  BY '[PASSWORD]';

Now you should be able to go back to your MySQL client and connect as normal.

References

2.11.4 Changes in MySQL 8.0 — https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
6.4.1.2 Caching SHA-2 Pluggable Authentication — https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

Originally published at https://chrisshennan.com/blog/fixing-authentication-plugin-cachingsha2password-cannot-be-loaded-errors

Subscribe to my newsletter…

… and receive the musings of an aspiring #indiehacker directly to your inbox once a month.

These musings will encompass a range of subjects such as Web Development, DevOps, Startups, Bootstrapping, #buildinpublic, SEO, personal opinions, and experiences.

I won’t send you spam and you can unsubscribe at any time.

Источник

If you have installed MySQL version 8.0 or later and tried to establish a connection to its database, you might run into this error:

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

What is caching_sha2_password Authentication Plugin?

caching_sha2_password is MySQL’s latest authentication plugin which brings some major advancements to the connection encryption, compared to the other authentication mechanisms. The first is, an in-memory cache for faster authentication. Next is a RSA-based password exchange that is independent of the SSL library against which MySQL is linked. And finally, it supports Unix socket-files and shared-memory protocols.

You can read more about its features here.

Why I can’t connect to MySQL 8.0?

The reason for this is while MySQL 8.0 uses caching_sha2_password as the default authentication plugin, your MySQL client hasn’t been compatible with it yet. It uses the older versions of libmysqlclient which do not support this caching_sha2_password plugin. Thus it failed to connect.

For example, Sequel Pro users have been experiencing this due to the fact that Sequel Pro hasn’t supported MySQL 8 (issue #2699) and the caching_sha2_password
plugin is missing (issue 3037).

Note from the Sequel Pro team on Jun 20:

Side note: Support for caching_sha2_password will probably not happen anytime soon. Sequel Pro currently uses the 5.5 MySQL client library and this plugin is only included with the most recent 8.0 library. Since there may be internal changes between those versions and we made some customizations ourselves, we have to go 5.5 -> 5.6 -> 5.7 -> 8.0 and check compatibility at each step.
But not even the switch to 5.6 is scheduled for the next release.

Well, that sucks!

Until now, there are a couple solutions for this:

1. Change to legacy password for MySQL Server:

Go to System Preferences -> MySQL
From Instance tab, choose Initialize Database
Choose Use legacy Password Encryption
Restart the Server

Now you might be able to connect to the database again.

2. Use `mysql_native_password`

You can use the ALTER command to change the encryption of the password to mysql_native_password instead of the latest authentication plugin caching_sha2_password

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

3. Roll back to the earlier version of MySQL

Back to MySQL 5.7 for example:

But those are just temporary solutions. It’s not recommended if you want to use the latest encryption method.

4. Use another GUI Client which supports MySQL 8

Download TablePlus here or update the TablePlus app to the latest version.
Connect to any version of MySQL, even it requires two-step authentication.
Enjoy coding!

TablePlus is a modern, native client with intuitive GUI tools to create, access, query & edit multiple relational databases: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Amazon Redshift, MariaDB, CockroachDB, Vertica, Cassandra, Oracle, and Redis.

It’s native, beautiful, and available for free.

Источник

I just installed MySQL Ver 14.14 Distrib 5.7.22 with Homebrew on my macOS v10.13.4.
I ran the command:
brew install mysql

After the installation completed, as directed by Homebrew, I ran the command:
mysql_secure_installation
and was returned the error: Error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found

I tried a few things like changing default_authentication_plugin to mysql_native_password in the my.cnf file but it still throws the same error.

Next I tried running:
mysql_upgrade -u root
and I was thrown the same error again mysql_upgrade: Got error: 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found while connecting to the MySQL server Upgrade process encountered error and will not continue.

Any help is appreciated.

asked Apr 20, 2018 at 16:01

So, I found the running process of mysqld by sudo lsof -i tcp:3306 then I killed it using sudo kill -9 <PID>.

After this I tried mysql_secure_installation again, but ran into a new error :

Error: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (61)

After trying a few fixes for getting mysql.sock to work, I started the MySQL server with sudo mysql.server start then went ahead with mysql_secure_installation to set password for the root user.

This worked for me finally.

Note: Homebrew asks you to first do mysql_secure_installation before starting the MySQL server for the first time but that just made me run into endless loop of errors.

answered Apr 21, 2018 at 12:50

Restart the mysql server to take the changes in affect and try connecting via mysql with any mysql client.

Otherwise use docker

docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql --default-authentication-plugin=mysql_native_password
mysql -uroot --protocol tcp

Try in PWD

Summary

Reason

Resolution

References

Subscribe to my newsletter…

What is caching_sha2_password Authentication Plugin?

Why I can’t connect to MySQL 8.0?

Until now, there are a couple solutions for this:

1. Change to legacy password for MySQL Server:

2. Use mysql_native_password

3. Roll back to the earlier version of MySQL

4. Use another GUI Client which supports MySQL 8

Интересное по теме:

2. Use `mysql_native_password`