CODEWITHSUNDEEP
mysql
Liam lin
Liam lin

Reputation: 475

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

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?

Upvotes: 34

Views: 122705

Answers (7)

Ammar Ahmed Khan
Ammar Ahmed Khan

Reputation: 1

This has worked for me

UPDATE mysql.user SET host='%' WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';

Reference: [1]: https://github.com/docker-library/mysql/issues/839#issuecomment-1091834886

Upvotes: 0

prasanna kumar
prasanna kumar

Reputation: 1

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

Replace 123 by your password, this worked for me.

Upvotes: 0

mehmettagil22
mehmettagil22

Reputation: 21

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

Upvotes: 1

Anmol Singh
Anmol Singh

Reputation: 11

@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 me, make sure to follow the proper password level

SHOW VARIABLES LIKE 'validate_password%';

Upvotes: 1

Mojtaba Rezaeian
Mojtaba Rezaeian

Reputation: 8756

I had similar problem but the error was only because of mysql syntax requiring to 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!

Upvotes: 2

HuSharp
HuSharp

Reputation: 577

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

Upvotes: 51

PirrenCode
PirrenCode

Reputation: 470

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.

Upvotes: 7

Related Questions