1

There are many similar questions, but none of them helped me.

I had been using MySQL on Windows with root/root credentials for a while, but since lately, I'm not able to login

> mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

> mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

> mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

> mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to reset root password: (in admin console)

> cat C:\mysql-init 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

> mysqld --init-file=C:\mysql-init --console

but there is an error line:

2015-12-17T10:34:14.616701Z 1 [ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords
2

Ok, I started mysqld with --skip-grant-tables option. After that, I was able to connect using mysql client and reset my password with

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
2

Enable skip-grant-tables in my.ini and restart MySQL. Now root can login without password so that you can reset your password:

update mysql.user set password=password('newpassword') where user='root';
1
$ mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

ODBC user is the default username under windows even if you didn't create that user at setup time then it will show you this error message.

$ mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Based on the information

I had been using MySQL on Windows with root/root credentials

This is expected behavior since user root has password but you did not enter it.

$ mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

$ mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

You will get this error when you enter wrong password.

To reset your root password:

1) if you have another Admin user login by using it then execute the following command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

2) if you don't have another administrator user follow the steps in the following URL: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

  • I could have been more clear more clear that I've already done that. – pacholik Dec 17 '15 at 11:13
  • @pacholik do you have a lot of users in your database, or just root – Ahmad Abuhasna Dec 17 '15 at 11:21
  • I have different user too, but I can't login to it neither (same errors). – pacholik Dec 17 '15 at 11:52
  • @pacholik there is a test we can try here, stop MySQL service, then go to data_dir\mysql then move user.* files and replace them with clean one. then start it again and try to login using mysql -uroot -P[port] – Ahmad Abuhasna Dec 17 '15 at 13:26
  • Didn't help (same errors). – pacholik Dec 17 '15 at 14:08
1

I've found that sometimes, especially when I have more than one MySQL instance installed on a machine, I get similar errors, unless I explicitly specify the port (with -P) and/or host (with -h). Try this:

mysql -uroot -p -P3306 -h127.0.0.1

Substituting the actual port (check my.ini if you don't know) of course.

In this case, it is possible that your password has not changed, does not need to be reset, etc. Try using your old, formerly functional root password with this login string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.