Resetting Your Forgotten MySQL Password

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.

Advertisements

45 thoughts on “Resetting Your Forgotten MySQL Password”

  1. 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

    1. 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.

  2. 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.

  3. 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….

    1. 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

  4. 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. :)

  5. 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.

  6. 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;

  7. 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

    1. 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.

  8. 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!

  9. 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?? :(

    1. 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.

    1. 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.

    1. 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.

  10. 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!

  11. 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

    1. 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”

  12. 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!

  13. 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

    1. 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.

      1. 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

      2. In this case particularly, it might be the comment system adding in those back tick and the “smart” single quote.

      3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s