1

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
MLEN
  • 2,162
  • 2
  • 20
  • 36

4 Answers4

14

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
Zakir
  • 1,305
  • 1
  • 10
  • 13
2

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
Üneys
  • 152
  • 2
  • Any idea why it does not work for me? This is after I open a new terminal window: 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 – MLEN Aug 13 '17 at 14:53
  • hmmm..i think u try using this commands in this page https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html i searched for u..Try update your mysql server.i hope you win – Üneys Aug 13 '17 at 14:59
  • @Üneys mysql -u root command gives me error Access denied for user 'root'@'localhost' (using password: NO) – Mansuu.... Feb 09 '18 at 10:19
0

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
ADN
  • 65
  • 1
  • 8
0

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'

Mehdi
  • 61
  • 4