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.
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:
For MySQL 5 Installations do - (thanks to RY for pointing it out):
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.