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