Login Failed After the authentication_string Field Is Changed to Display the Password for RDS for MySQL

Scenario

After you use Navicat to modify the field authentication_string of the root account in the user table to display the password, the user cannot log in to the client.

This problem may occur in MySQL-8.0.20.6.

Possible Causes

The way the password was changed was incorrect. The hash key of the authentication_string field in the user table should not be changed directly. The password of the root user should be reset on the console.

Solution

MySQL 8.0 does not support the password function. Therefore, you should perform the following steps to rectify the fault:

1. Locate the authentication_string field of account rdsAdmin and run the following command:

   update mysql.user set authentication_string='XXX'

   XXX indicates the new password.

   

2. Reset the password of account root.

   ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'XXX';
   flush privileges;

   

   After the password is changed, the user can log in to the system as user root.