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 tát 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 sánh I could add a new user, and my new user works. I try to tát 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?

asked Jul 9, 2019 at 6:13

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 |
+------------------+-----------+

answered Sep 3, 2021 at 3:41

1

According to tát the mysql docs and this one - kiểm tra the special --init-file option. And then try to tát FLUSH PRIVILEGES;

Also, please see this related post.

This should help.

answered Jul 9, 2019 at 6:37

1

I had similar problem but the error was only because of mysql syntax requiring to tát escape uderline character _ in username using a backslash.

alter user 'user_db1'@'localhost' identified by 'mynewpassword'; ==>> caused error
alter user 'user\_db1'@'localhost' identified by 'mynewpassword'; ==>> success!

answered Jul 6, 2023 at 3:18

@HuSharp suggested comment:

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

along with following this: https://linuxhint.com/mysql-access-denied-user-root-localhost/ worked for má, make sure to tát follow the proper password level

SHOW VARIABLES LIKE 'validate_password%';

answered Aug 28, 2023 at 8:52

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

answered Nov 9, 2023 at 14:43

4

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

Replace 123 by your password, this worked for má.

CPlus

4,51239 gold badges27 silver badges67 bronze badges

1