19

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
  • 191
  • 1
  • 1
  • 8

2 Answers2

10

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
  • 119
  • 1
  • 3
6

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
  • 400
  • 2
  • 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