Authentication plugin ‘caching_sha2_password’ cannot be loaded

Time:2020-2-18

When using MySQL client tools to connect to MySQL database, many users will encounter the error of authentication plugin ‘caching’ Sha2 ‘password’ cannot be loaded. (I use sqlyog)

The reason is that the encryption rule in the version before mysql8 is MySQL native password. After mysql8, the encryption rule is caching Sha2 password. There are two ways to solve the problem. One is to upgrade the Navicat driver. The other is to return the encryption rule of MySQL user login password to original MySQL native password

In the second way, the solution is as follows

1. Run the command prompt with administrator permission and log in to MySQL (remember to add environment variables)
If you do not add environment variables, please switch to MySQL installation directory, where my MySQL is installedC:Program FilesMySQLMySQL Server 8.0
implement
   mysql -u root -p

   password:123456
Password enter the MySQL password you set
#The screenshot of logging in MySQL is as follows:
Authentication plugin 'caching_sha2_password' cannot be loaded

2. Modify account password encryption rules and update user password

Alter user ‘root’ @ ‘localhost’ identified by ‘password’ password express new; ා modify encryption rules
Authentication plugin 'caching_sha2_password' cannot be loaded

Alter user ‘root’ @ ‘localhost’ identified with MySQL? Native? Password by ‘password’;? Update the user’s password
Authentication plugin 'caching_sha2_password' cannot be loaded
3. Refresh permissions and reset password

Flush privileges; refresh permissions
Authentication plugin 'caching_sha2_password' cannot be loaded

Open the MySQL client tool again to connect, and “success”!!!
Authentication plugin 'caching_sha2_password' cannot be loaded

Reference link: https://www.cnblogs.com/zhurong/p/9898675.html