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?
   • Option 1
   • Option 2

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.

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.