How to resolve ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'?

Question

I can't login with root ,I think root's password may be changed.But I can't change root's password either.

Mysql version is v8.0.16.

I have used --init-file to specificd alter sql at mysqld booting

alter user 'root'@'localhost' identified by 'mynewpassword'

but it doesn't work.

I used --skip-grant-tables --user=mysql so I could add a new user, and my new user works. I try to alter root,but it failed again.

mysql> alter user 'root'@'localhost' identified by 'mynewpassword';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

here is the table user's content

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

Do anyone have any idea?

Answer 1

According to the mysql docs and this one - check the special --init-file option. And then try to FLUSH PRIVILEGES;

Also, please see this related post.

This should help.

Answer 2

You Can try:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';

rather than

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';

When you use the following command

mysql> use mysql;
mysql> select user,host from user;

you can find that root's host is '%'

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