Thursday, November 15, 2007

MySql: Access denied for user 'root'@'localhost'

By default, MySql has no root password when installed, hence the following should work in most cases. But in some cases, mysql and mysqladmin simply throw Access denied message and you have no clue how to change the password after that.

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

Here is an elegant solution for this highly irritating but common problem.


This is how you reset the password.

1. Start the mysql server instance or daemon with the --skip-grant-tables option. (security setting)

$ mysqld --skip-grant-tables

2. Then Execute these statements.

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

3. Finally, restart the instance/daemon without the --skip-grant-tables option.

$ /etc/init.d/mysql restart

4. You should be able to connect with your new password.

$ mysql -u root -p
Enter password: my_password

courtesy: Shawn Tucker @ MySql Forums

23 comments:

Anonymous said...

This has been of tremendous assistance to me, thank you so much for taking the time to share this information.

_George

Anonymous said...

It was an excelent solution, i was terribly stuck with this problem and it wroked for me with the above steps.

Thank you Shawn Tucker for your valuable information.

Anonymous said...

Good , clear and works on Vista too.
Many thanks

Unknown said...

I spent a lot of time figuring out a solution for this error. Glad it is helping a lot of folks with similar issues.

Michael said...

Thanks so much Anoj! This helped me a lot.

Luke said...

Thankyou very much Anoj, extremely elegant and immediately effective on Vista business

Unknown said...

thank u :) it worked 4 me

Anonymous said...

this was very helpful. however i had to edit my /etc/my.cnf file adding skip-grant-tables to the [mysqld] section in order to follow the instructions on this page. once i did that everything worked great.

thanks so much

remlwc

Vaka said...

Thank you very much, I have tried a lot to get rid of this error till I found this site.

I appreciate your help.

Regards,

Vaka

Anonymous said...

Thank you so much. I was struggling with this since last week or so.
Jayesh Patel

Anonymous said...

Thank you thank you thank you. If anyone who has anything to do with the mysql website ever reads this, pls take a look at this; your detailed 'access denied' instructions were worthless.

RJ Scott said...

[total layman] I have root user with a working password. I know this because I can log in from phpMyAdmin. I am trying to connect via openoffice.org database and of course it doesn't work. I get the following error: The connection to the data source "mydbname" could not be established. Access denied for user 'root'@'m333f33d0.tmodns.net' (using password: YES). I made up some numbers there so I hope they're not needed to find a solution. Any thoughts on what to do if the password is already working by other means?

Anonymous said...

Thank you very much. It worked perfectly.

Unknown said...

Thank you very much. Its work in Windows 7 with WAMP sever.

Anonymous said...

Thank you a lot...from Argentina

Anonymous said...

This is great! The best solution and work perfectly for MySQL on Vista.

Thank you!

Uruk said...

Man, that's exactly what I needed! Thanks so much for sharing that advice! You have no idea!

Or maybe you do?

Thanks again!

Unknown said...

Tried the trick. there is a more comprehensive procudure to change the root password for the mysql root user; http://www.cyberciti.biz/tips/recover-mysql-root-password.html
hope this one helps.

Andrew said...

Hey, man. I know this is an old thread, but I have a similar problem (and I haven't found anyone with such a clear grasp of the issues as you, so far).

Without changing a thing, MySQL suddenly stopped allowing me access through another user (not root) with a password, from my php script. The failure is in mysql_select_db (not mysql_connect - which seems to work fine).

Short story, I reinstalled everything and my error is "Access denied for user 'reporter'@'%' to database ''". I've changed permissions on everything fodler and file I can find, recreated the user, used OLDPASSWORD, etc. and nothing has helped.

Apache 2.2.4 runs on Win2K, with PHP 4.4.4
MySQl 5.1.50 runs on Vista - same lan.

I really need help. I can email you more details if you need them. (andrew@snowsoft.co.nz)

Thanks,
~AS.

Anonymous said...

Hi !

I am also facing same problem, but when I use the command "mysqld --skip-grant-tables" after that when i give "echo $?" to see that where my previous command is success or not it show 127. So i think that there must be some problem. M using Linux Mint 9.

Dmac said...

Hey guys, most of you applied the solution and it worked. I'm having problems applying the solution.

I am trying to execute the instrucitons, or better yet, the mysql> prompt.

I'm frustrated! Could somebody walk me through it. Mobile phone 347 368-5466

Unknown said...

Dmac where are you stuck?
The above instructions are linux specific, but should work in windows as well.

johnny said...

thanks so much for this solution!!!!