mysql: still can't login after root password reset
 

please help - I've managed to forget my root password and could not log into mysql. I then:

1. stopped mysqld, and restarted with --skip-grant-tables.
2. changed the root password by updating mysql.user
3. flushed privileges
4. restarted mysqld

when trying to start with 'mysql -u root mysql' i get logged in but whenever I try to do something I get

ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

when trying to start with 'mysql -p -u root mysql' so i get prompted for my password and i type it in, i get:

ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

please help this thing is driving me up the wall. as you've probably seen above i'm no mysql expert.

thanks

andre

ok, after several hair-pulling-out-in-frustration days i've managed to crack this one so here's the answer IF anybody happens to be interested. The insert to change your password must be in the format:

update mysql.user set password=PASSWORD('password') where user='root';

note the password() function. i'm amazed nobody could help me on this one, on all the forums where i looked the function was not used so the password is inserted in plain text.

Thank you!
 

Almost 9 years later, I created a new account on this forum just to thank you for the time you took to write the solution to my problem! I spent around 5-6 hours trying to figure this out reading dozens of articles and documentation and thanks to you I was able to solve this problem (which took many production servers down).

And yes, if you get the error "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)" you need to:

1. Stop mysqld, type skip-grant-tables under [mysqld] within the /etc/my.cnf file.
2. Start mysqld
3. login with root mysql -u root -p
4. enter the following command: update mysql.user set password=PASSWORD('password') where user='root';
3. flush privileges
4. restart mysqld

Also, do not forget to erase the "mysql.log" file after you're done. (You must exit "mysql" command first.) Otherwise the command you just entered will be there along with the password. :eek:

mysql: still can't login after root password reset
 

Hi,

@Den

If you add skip-grant-tables under [mysqld] within the /etc/my.cnf file, and do below:

2. Start mysqld
3. login with root mysql -u root -p
4. enter the following command: update mysql.user set password=PASSWORD('password') where user='root';
3. flush privileges
4. restart mysqld

wont fix the issue because it will give an access to any password for root user. Feel free to give it a try.

login with root mysql -u root -p
and type any password and see you will be at mysql prompt which should not happen.