A few weeks ago I ran into an issue where I found myself locked out of the MySQL server that runs locally on my Mac. After some research and toying around I was able to reset the password. So for those of you unaware of how this process works, I’m going to share it with you. Now remember, this is done from my Mac, many of you that are running from an another system might find the solution to not be accurate for you.
Also, please be advised. Use this method at your own risk. You are a responsible human, if you don’t feel comfortable doing this procedure find someone to help you – I am not responsible for any lost of data or corruption on your system.
Stopping MySQL
First stop the service. You can either do this using the preference pane if you have that installed if you don’t, your likely well aware of doing it from the terminal. Though this should work for most users.
sudo /usr/local/mysql/support-files/mysql.server stop
You can restart using:
sudo /usr/local/mysql/support-files/mysql.server start
Skipping Access Tables
Alright – so open up a Terminal window and execute:
/usr/local/mysql/bin/safe_mysqld --skip-grant-tables
For MySQL 5 Installations do – (thanks to RY for pointing it out):
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables
Running the Reset
Ok – so you have safe_mysqld running in one Terminal window, now open up another one and execute “/usr/local/mysql/bin/mysql mysql” (no quotes). If you aren’t familiar you are opening up the MySQL console and opening the mysql table.
Write the reset query into the console as follows:
UPDATE user SET Password=PASSWORD('YOUR_PASSWORD')
WHERE Host='localhost' AND User='root';Replacing “YOUR_PASSWORD” with your desired password of course. Once you’ve done that just exit the console “exit;” close the safe_mysqld execution and restart your MySQL server in normal mode.
Innovative Thought 
hey mate,
i went through your process here and it all worked with the exception of the syntax for resetting the password – i used UPDATE user SET password = password(‘…….’) WHERE user = ‘root’ AND host=’localhost’; which I found on another article http://www.franzone.com/2008/04/01/how-can-i-recover-my-lost-mysql-root-password/#comment-4213 but wanted to thank you for this post as i was really really stuck and your atricile along with the one i just referenced got me through.
Thanks you so much, you’ve no idea what a nightmare i was having!
Ronan
did you cut and paste the syntax into terminal? There are smart quotes in the article syntax that wont work – I replaced these with plain quotes and it ran fine.
Ronan, glad it helped you… not sure why the syntax wouldn’t have worked for you — seems to be the same to me. Perhaps case-sensitivity in the column names… name sure.
Thanks for the instructions on how to do this! Very appreciated.
i went and used mysqld_safe –skip-grant-tables but this is what is said
090515 20:27:30 mysqld_safe Logging to ‘/usr/local/mysql/data/richard-downess-macbook.local.err’.
touch: /usr/local/mysql/data/richard-downess-macbook.local.err: Permission denied
chown: /usr/local/mysql/data/richard-downess-macbook.local.err: Permission denied
090515 20:27:30 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe: line 100: /usr/local/mysql/data/richard-downess-macbook.local.err: Permission denied
rm: /usr/local/mysql/data/richard-downess-macbook.local.pid: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 137: /usr/local/mysql/data/richard-downess-macbook.local.err: Permission denied
090515 20:27:30 mysqld_safe mysqld from pid file /usr/local/mysql/data/richard-downess-macbook.local.pid ended
/usr/local/mysql/bin/mysqld_safe: line 100: /usr/local/mysql/data/richard-downess-macbook.local.err: Permission denied
any ideas? i have the preferences pane loaded and it is showing mysql as stopped….
Richard I would imagine that the MySQL installation was probably installed by the admin account or account other then yours? Make sure your account has admin rights on the Mac, you may be required to run it with sudo at the terminal – in which case if you are not an admin user, you’ll have to make sure you are on the sudoers list on the mac. A blog with information on adding yourself to the sudoers list can be found at http://www.macmartine.com/blog/2008/04/adding_a_user_to_sudoers_file.html
I don’t know about everyone else, but for the version of MySQL I have…
safe_mysqld
is instead
mysqld_safe.
But thanks for your help. :)
Good catch RY, mysqld_safe is what is available it in MySQL 5, it seems – I’ve updated the post to reflect that. Thanks for pointing it out.
You just saved me a lot of heartache. THANK YOU. I had the same problem as Richard, but when I prefaced the commands with “sudo” it all worked out.
I had the same problem with the syntax, not sure what the problem is, but i tried this one and it worked:
UPDATE mysql.user SET Password=PASSWORD(‘vsn123′) WHERE User=’root’; FLUSH PRIVILEGES;
Hi,
I tried the above process, everything worked fine but when I enter the new password it says incorrect password.
Also when I retry the above process now, it asks for the password again as soon as I enter
sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables.
Could somebody help me out with this.
Thanks,
Naveen
Naveen,
Remember that when you execute sudo it is asking you for you administrative users password (either your password if you are on the sudoers file, or the password of root). When resetting the password you may want to use a simple password temporarily so you can login the database first to confirm the reset, then selecting a more secure password after the reset is confirmed.
Had a problem as well – simply changed the single quotes to a double quote, used mysql_safe, and made sure I didn’t use the $ in my password (which is what hosed it up the first time).
Thanks for figuring out how to do this and then posting it!
Thanks Jay, I’m glad I was able to give you a starting point!
P.S. I also had to use sudo in the first terminal window
On opening the second terminal window, and using ” /usr/local/mysql/bin/mysql mysql” (withouth quotes) resulted in ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’…Any ideas how I can resolve this?? :(
First make sure that you have the server running successfully without the grant tables. Next you might consider running the server using the sudo command if you didn’t previously. There are several entries in the MySQL support forum with that error message that might be able to provide you with some solid insight.
Thanks !! That worked :)
Awesome, thanks!!! The only thing I would add for novices like me is how to stop the server.
/Library/StartupItems/MySQLCOM/MySQLCOM stop
Thank you Robert. I definitely did avoid addressing how to stop the service since many users would be different (depending on operating system). Thanks for posting how to do it for Mac OSX.
Dude, you totally rock!!! Thank you!!! You just saved me a TON of time.
What’s your paypal so I can buy you a beer? :)
Thanks Mike, I’m really glad it helped you. I’m not one to turn away beer money :), I’ll email you at your gmail.com account with my address. I truly appreciate your kind words.
Done!
I greatly appreciate it. Thank you very much.
Thanks for your valuable blog. It helped me a great deal.
Thank you. Everything works as instructed.
Thankyou Thankyou Thankyou Thankyou!!!!!
I had the same “Permission denied” errors as Richard. I solved it with a sledgehammer by logging into my Mac’s root account and doing it from there. Then it worked with no problem.
I’m fairly new to MySQL, and I tried to change the MySQL root account password. I did what it told me to do, but something obviously went wrong. I thought I’d lost a week’s worth of work.
Keep up the good work!
Beautifully simple and works – thanks
simple and works – thanks!
seems to have worked, but there are smart quotes in the query as it is in the article text. These are different characers to plain quotes and caused me problems cutting and pasting it into terminal (serves me right for being lazy!) – so better to type it
I’m getting this error:
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
You didn’t select a database. Probably you omitted the second mysql word which is the database. Your command should be “mysql mysql” or better still “mysql -D mysql” or “mysql –database mysql”
thanks for the tip! worked for me using the comments of the post.
Summarized how it worked for me there: http://laurent.bristiel.com/resetting-mysql-root-password/
I’m really glad it helped you Laurent. Thanks for the referred link back too.
Thank you so much! this was a huge help :)
I got this error message
-bash: /usr/local/mysql/bin/mysqld_safe: No such file or directory
As well as this one
bash: /usr/local/mysql/bin/safe_mysqld: No such file or directory
I do have MySQL installed, it was working perfectly until I mistakenly overwrote the root password.
Any suggestions?
Thanks!
Hi, I entered this in the second window in Terminal:
UPDATE user SET Password=PASSWORD(‘glc900′) WHERE Host=’localhost’ AND User=’root’;
I got the error:
-bash: syntax error near unexpected token `(‘
I copied the UPDATE code to a text editor and then copy pasted from there. What am I doing wrong?
Thanks
Greg, it sounds to me like your running that command directly into the terminal shell and not your MySQL console. Make sure you follow the instructions right underneath the “Running the Reset” headline.
There is an error around (‘glc900′). Use normal single or double quotes around PASSWORD(“glc900”). The error is caused by a back tick behind 900. MySQL sees back ticks as escape character
In this case particularly, it might be the comment system adding in those back tick and the “smart” single quote.
I had the same problem as Greg,iam new to MYSQL and i dont understand the ‘running the reset’.-bash syntax error near unexpected token'(‘. please healp me
thank you
Thank you very much. Now I can log into phpMyAdmin and everything is working. :-)