MySQL changing authentication type from standard to caching_sha2_password
I had the same problem today. The only way I found to fix it was:
- executing the install file
- select "Reconfigure" over the mysql server
- In Authentication Method tab, select "Use Legacy Authentication Method"
It should fix your problem.
Reset the user password with mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{NewPassword}';
Run
mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
For those of you that are using MySQL Workbench 8.0 CE and still struggling, the following worked for me:
- Under Navigator, click on Administration Administration under Navigator preview, then select Options File.
- Administration - Options File will open. Under the security tab, scroll down until you see "default authentication plugin". Mine was on caching_sha2_password. Changing it to mysql_native_password, clicking apply and restarting MySQL Workbench worked for me.
Administration - Options File preview
I had to delete and re-add the user. It did not automatically change the user's authentication type back to caching_sha2_password, it kept it on standard.