How to solve the problem "caching_sha2_password" problem

How to solve the problem "caching_sha2_password" problem ?

if you log in to MySQL and find an error like below

[root@localhost ~]# mysql -h 127.0.0.1 -P 3306 -u root -p Enter password: ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

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:

mysql> ALTER USER root IDENTIFIED WITH mysql_native_password BY 'password';

or when creating the user:

mysql> CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';