Resetting user passwords in MySQL 8.0: A step-by-step guide

Question

My questions and details

I want to change a user's password in MySQL8.0, but almost none of the online methods seem to work.

Here are my mysql.user table details:

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Excalibur        | %         |
| yyy              | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

what did I do

I have tried the following commands:

alter user 'yyy'@'%' identified with mysql_native_password by '12345';

and received the error message:

ERROR 1396 (HY000): Operation ALTER USER failed for 'yyy'@'%'

---

I also tried to operate the mysql.user table myself:

update user set authentication_string=sha1('12345') where user = 'yyy' ;
flush privileges;

Although it works, I cannot log in to yyy via (what I should be) password 12345. Then I took a look at the table and it looked really strange:

+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| Excalibur        | %         | $Arrreee5$tE.D2|7^FTRYjmg1APzveuTWyJ1BaO2al1GKvO3UJO6ZlX06jqbNkT5 |
| yyy              | %         | 8cb2237d0679ca88db6464eac60da96345513964                               |
| mysql.infoschema | localhost | $Arrreee5$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $Arrreee5$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
...

Answer 1

For MySQL 8.0

Ordinary users use the following command to change the password

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Root user uses the following command to change the password

P.S Check using password with "$A$005$", caching_sha2_password authentication plugin should be used, without "WITH caching_sha2_password" it will not work for root user. If it is a general user Permissions need to be granted.

ALTER USER 'userName'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'New-Password-Here';