Reputation: 475
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: 122700
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
Reputation: 1
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';
Replace 123
by your password, this worked for me.
Upvotes: 0
Reputation: 21
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '123';
Upvotes: 1
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
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
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
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