Resetting mysql server root password on OS X

First of all, I know there are several threads, but I have tried so many solutions and I cant get anything to work.

I dont have any experience with mysql server and Terminal.

I downloaded mysql server 5.7.19

Following the answer from redtek, here: Setting the MySQL root user password on OS X

I open mysql from system setting, click stop server. Then I open the terminal and write

sudo mysqld_safe --skip-grant-tables 

I asks me for my password (I assume this is the same when I start my computer). I get a message that command not found.

MacBook-Pro:~ XXXXXX$ sudo mysqld_safe --skip-grant-tables Password: sudo: mysqld_safe: command not found MacBook-Pro:~ XXXXXX$ 

UPDATE: When I run the solution below, after opening a new window I get the following errors:

Last login: Sun Aug 13 16:51:49 on ttys002 MacBook-Pro:~ XXXXX$ mysql -u root -bash: mysql: command not found MacBook-Pro:~ XXXXX$ UPDATE mysql.user SET Password=PASSWORD('my-new-password') WHERE User='root'; -bash: syntax error near unexpected token `(' MacBook-Pro:~ XXXXX$ FLUSH PRIVILEGES; -bash: FLUSH: command not found MacBook-Pro:~ XXXXX$ \q 

4 Answers

Stop the MySQL server.

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

Restart it with the --skip-grant-tables option.

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

Open another terminal to connect to the MySQL server using the mysql client.

 cd /usr/local/mysql/bin mysql 

Tell the server to reload the grant tables so that account-management statements work.

FLUSH PRIVILEGES; 

Now reset the password for root user

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; 

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass'); 

Stop the server and restart it normally

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

First step is to stop MySQL service.

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

Then you need to start it in safe mode

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

secondly: 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 

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 yu need to start the MySQL server

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

The command is not found because MySQL installation folder ( /usr/local/mysql/ ) is not included in the system variable PATH.

1. You can add to PATH
2. OR you can use full path /usr/local/mysql/bin/mysqld_safe

It took me a while in resolving this, considering most solutions around are for versions lower than MySQL version 5.7

Follow this below and it could help get you sorted as well.

For Safely ensuring process: - Turn off the tick on "Automatically Start MySQL Server on Startup" inside System Preferences of MySQL (spotlight - mysql)

• Open Terminal and type sudo /usr/local/mysql/support-files/mysql.server start -PS: This is for ensuring its in-line, times were that the next processes were breaking on me.
• Now shut the MySQL service: sudo /usr/local/mysql/support-files/mysql.server stop
• Type sudo mysqld_safe --skip-grant-tables This will have now bypassed the security for MySQL - not safe for operations and not a permanent solution to always allow you to use MySQL. Currently, as you would see, its in a process... This will allow us to do following steps. Leave this tab of Terminal OPEN throughout remaining process!!

Now Cmd+N (new terminal window), and in the new terminal: - sudo /usr/local/mysql/support-files/mysql.server start - update user set authentication_string=password(‘jj’) where user='root' This on older version would have been as update user set password=PASSWORD(“jj”) where user='root’; - FLUSH PRIVILEGES; //This is essential (updates disk instead of cache) to ensuring the next time around when you close mysql and get back it stays accessible as you setup. - \q or quit

Close it all down - All terminals, give your computer a restart, and ensure everything is in order (ofcourse this entails - restart - open terminal - mysql -u root -p (enter) - respond with password you gave on steps above). In my answer: jj was the password set

Cool-Stuff for General knowledge of fairly new (this somehow immediately worked for me after saying Password is not a field or something of sorts, on going in this new Terminal at update user set authentication_string=password(‘jj’) where user='root', so if you had the same, go at it in following steps - in >mysql itself where you are..): - use mysql; - show tables; - describe user; and then continue as steps above from the point of update user set authentication_string=password(‘jj’) where user='root'