A few days ago I forgot the root password of the MySQL, which is running on my Mac. I wasn’t working with MySQL for at least a month. So what I needed was to reset the password so that I can use the service as I needed it. Here’s how I did it.

The first step is to stop MySQL service. I stoped it like this:

sudo /usr/local/mysql/support-files/mysql.server stop

Then I need to start it in safe mode:

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

This should be an ongoing command until the process is finished so let’s open another shell/terminal window, log in with no password:

mysql -u root
UPDATE mysql.user SET Password=PASSWORD('my-new-password') WHERE User='root';
FLUSH PRIVILEGES;
\q

if you are using MySQL 5.7 then you have to do this instead of the commands stated above. Because in MySQL 5.7, the password field in mysql.user table is removed, now the field name is ‘authentication_string’.

mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('my-new-password') WHERE User='root';
FLUSH PRIVILEGES;
\q

Now again I need to start the MySQL server.

sudo /usr/local/mysql/support-files/mysql.server start

After all of these processes was done, I was able to login as root with the new password I just assigned.