26

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?

Liam lin
  • 385
  • 1
  • 3
  • 10

4 Answers4

35

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 |
+------------------+-----------+
HuSharp
  • 387
  • 3
  • 4
7

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.

PirrenCode
  • 444
  • 4
  • 14
  • Thank you very much , What I have tried is according [mysql docs](https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html) but I do didn't flush after mysqld start. I think it should be as the same as skip-grant. – Liam lin Jul 09 '19 at 06:53
0

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!
Mojtaba Rezaeian
  • 8,268
  • 8
  • 31
  • 54
0

@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%';
Anmol Singh
  • 1
  • 1