"Authentication plugin 'caching_sha2_password'

Question

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"

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

Answer 1

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

See MySQL Server Blog

See Oracle