Date Tags mysql

Contents

Summary

You have installed MySQL 8 and are unable to connect your database using your MySQL client. 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

or

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 that rely on the mysql_native_password won't be able to connect because of this change.

Resolution

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

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]';

or

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


Comments

comments powered by Disqus