Bobcares

How to Fix MySQL Error with Authentication Plugin ‘caching_sha2_password’?

PDF Header PDF Footer

An error between the client and server authentication methods is usually the cause of the MySQL error along with the authentication plugin ‘caching_sha2_password’. Read the article to learn more. At Bobcares, with our MySQL Support Service, we can handle your issues.

Overview
  1. Fixing MySQL Error with Authentication Plugin ‘caching_sha2_password’
  2. Reason for the Error
  3. How to Resolve the Error?

Fixing MySQL Error with Authentication Plugin ‘caching_sha2_password’

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

1. caching_sha2_password:

i. This is the default authentication plugin in MySQL 8.0 and later versions.

ii. It implements SHA-256 authentication and uses server-side caching for better performance.

iii. It has additional features and is more secure compared to the older plugin.

mysql error authentication plugin 'caching_sha2_password'

2. sha256_password (deprecated):

i. This plugin also implements SHA-256 authentication but without the caching feature.

ii. It is deprecated and may be removed in future versions, so it’s not recommended to use this plugin.

When using caching_sha2_password, we may face the following error messages:

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

Or,

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

Reason for the Error

As of MySQL 8.0, caching_sha2_password is the default authentication plugin instead of mysql_native_password, which was the default in previous versions. This change can cause connection issues with MySQL clients like Sequel Pro and HeidiSQL that rely on mysql_native_password.

How to Resolve the Error?

There are two main ways to resolve this issue: changing the default authentication plugin at the server level or changing it at the user level. Let’s see the details:

Option 1

In order to change the Default Authentication Plugin at the Server Level, run the following steps:

i. Locate the MySQL configuration file, which could be named my.cnf or my.ini depending on your operating system.

ii. Add or modify the following lines under the [mysqld] section:

[mysqld]
default_authentication_plugin=mysql_native_password

iii. After making the changes, restart the MySQL server to apply the new configuration.

Option 2

Now, to change the Authentication Plugin at the User Level, run the below steps:

i. Open a terminal window and connect to the MySQL instance using the command line:

mysql -u [USERNAME] -p

Replace [USERNAME] with the actual MySQL username.

ii. Enter the MySQL password when prompted.

iii. Once logged in, execute the following SQL command to change the authentication plugin for a specific user:

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

Replace [USERNAME], [PASSWORD], and [HOST] with our specific details:

iv. Run the following command to ensure the changes take effect:

FLUSH PRIVILEGES;

v. Try connecting again using the preferred MySQL client (e.g., Sequel Pro, HeidiSQL), and it should work with the mysql_native_password plugin.

[Want to learn more? Click here to reach us.]

Conclusion

To sum up, this should resolve most compatibility issues related to the new default caching_sha2_password plugin.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Get featured on the Bobcares blog and share your expertise with a global tech audience.

WRITE FOR US
server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

Speed issues driving customers away?
We’ve got your back!