label: MySQL,MariaDB,mysql_native_password,caching_sha2_password, Access denied for user 'root' @ 'localhost'
For a long time, MySQL has supported different authentication plugins. The original password authorization method is to find the hash value of the password with a specific algorithm and store it in the mysql.user table, but this is not the only authentication method.
When you log in to the server with the correct user and password and have been authorized correctly, the MySQL service also prompts that the service is rejected. You can consider that this may be caused by different password authorization methods.
1. Unix Socket authorization allows users to log in to mysql with a local username and password. The advantage of this authorization method is that there is no need to maintain the authorization password separately. This is possible because Unix Socket native mode is used.
2. The PAM authorization plug-in allows installation, such as LDAP and other back-end systems to authorize, this method is suitable for integration into the organization's existing system.
3. The latest version of MySQL8 uses cache_sha2_password to authorize, which is theoretically more secure. This update will of course be better, but at the cost of upgrading the client driver, otherwise you will need to switch back to the old but compatible licensing method.
Here is the solution: log in to mysql from the command line
sudo mysql -u root -p
Various commands can now be entered. Convert the current database to mysql.
Run the following command to update the authorization plugin. And immediately update the authorization to make it effective.
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
service mysql restart
If you log in to mysql from the shell command line, use the --default-auth option to specify the authorization authentication method. Sometimes you cannot log in because the default authorization verification method is incorrect.
shell> mysql --default-auth=mysql_native_password ...