CODEWITHSUNDEEP
mysql
nandipati vamsi
nandipati vamsi

Reputation: 383

"Authentication plugin 'caching_sha2_password'

I'm new to MySql environment and installed : MySQL with the following commands:

sudo apt-get update
sudo apt-get install mysql-server
mysql_secure_installation

and also installed mysql workbench.

But when I'm trying to connect my localhost getting the follow error:

"Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory"

Error Screenshot

and even this is the first time I'm posting a question in stackoverflow, sorry for my presentation errors and syntax.

Upvotes: 38

Views: 77983

Answers (5)

meow
meow

Reputation: 2191

So I found the reason for that error message (at least for my case). It's because MySQL as of version 8.04 and onwards uses caching_sha2_password as default authentication plugin where previously mysql_native_password has been used.

This obviously causes compatibility issues with older services that expect mysql_native_password authentication.

Solutions:

  1. Check for an updated version of the client service you are using (most recent workbench for instance).

  2. Downgrade the MySQL Server to a version below that change.

  3. Change the authentication plugin on a per user basis (I didn't find a global option, maybe there exists one though).

Now regarding option 3 this is as simple as altering the user:

ALTER USER user
IDENTIFIED WITH mysql_native_password
BY 'pw';

or when creating the user:

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

When running those statements, you may notice the client returns a message saying 0 row(s) affected - this is a bit of a red herring in that the statement did actually update the row for the user in the mysql.user table - you can check that by running the following:

SELECT *
FROM mysql.user u
WHERE u.plugin = 'mysql_native_password';

See MySQL Server Blog

See Oracle

Upvotes: 43

Shrm
Shrm

Reputation: 453

Login to Mysql or your workbench, and run the following statement for any user who has this issue:

alter USER 'YOURUSERFORMYSQL'@'localhost' identified with mysql_native_password by 'YOURPASSWORD'

In case you did not create the user yet, you can activate this feature during the user creation.

create USER 'NEWUSER'@'localhost' identified with mysql_native_password by 'NEWPASSWORD'

Upvotes: 0

Adil
Adil

Reputation: 51

The latest MYSQL versions have 'caching_sha2_password' as the default authentication type. Which does not allow remote connections to MYSQL and results in caching_sha2_password plugin error. I have fixed it using

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

Now it allows your user to access MySQL from localhost.

If you want to access MySQL from multiple remote hosts then do the following, **

ALTER USER 'yourusername'@'%' IDENTIFIED WITH mysql_native_password BY 'youpassword';

** After every alter command in SQL run the following to take effect.

FLUSH PRIVILEGES;

OR restart MySQL server

Upvotes: 5

Seeker401
Seeker401

Reputation: 99

In the my.cnf file add the following line:

default-authentication-plugin=mysql_native_password

then restart the server.

Upvotes: 9

Anse
Anse

Reputation: 1658

Some more details coming here: That caching_sha2_password plugin is the new default authentication plugin on MySQL 8 server. Only the libmysql library from that MySQL 8 distribution owns this plugin, and it is built statically into libmysql - the C-connector for various clients. That caching_sha2_password is not available separately for downloading.

This is the very first time that libmysql has an important plugin statically included. And this causes any other libmysql (including libmariadb and also older libmysql's) not to connect to MySQL 8 with a user which is defined to use that caching_sha2_password authentication.

I just hope that the guys from MariaDB are so nice to also include that caching_sha2_password in their libmariadb, to restore the drop-in-compatibility between MySQL and MariaDB.

From MySQL's server blog:

Support for caching_sha2_password was added in MySQL 8.0.3. Older versions of libmysqlclient do not support this plugin. So, although client tools that use libmysqlclient older than one available with MySQL 8.0.3 can connect to MySQL 8.0.4 server using users that use other authentication plugins such as mysql_native_password or sha256_password, such client cannot connect to MySQL 8.0.4 server using users which require caching_sha2_password support. For an upgraded database, it means connecting using an existing user account should not face any issues.

Upvotes: 13

Related Questions