Resetting Your Forgotten MySQL Password
Posted: May 17, 2007 Filed under: Database | Tags: MySQL, password, security 18 Comments »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 you should be well aware of doing in from the terminal.
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.
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
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.
[...] 5 on Mac OS X Snow Leopard (10.6.1), set a password and forgot it. I tried to follow this tutorial: http://blog.innovativethought.net/2007/05/17/resetting-your-forgotten-mysql-password/ but when I [...]
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 :)