CODEWITHSUNDEEP
mysqlmacos
MLEN
MLEN

Reputation: 2561

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

Upvotes: 1

Views: 13111

Answers (5)

Soner from The Ottoman Empire
Soner from The Ottoman Empire

Reputation: 20901

For MySQL 8.x in MacOS Stop all MySQL server service

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

This will start mysql server in this terminal Start new terminal

cd /usr/local/mysql/bin
sudo ./mysql -u root
FLUSH PRIVILEGES;
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;
sudo ./mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
FLUSH PRIVILEGES;
exit;
sudo ./mysqladmin -u root shutdown -p (this will shutdown mysql server that started at the other terminal)
start mysql with the normal way

Upvotes: 0

Zakir
Zakir

Reputation: 1565

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

Upvotes: 16

Mehdi
Mehdi

Reputation: 71

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'

Upvotes: 0

ADN
ADN

Reputation: 65

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

Upvotes: 0

Üneys
Üneys

Reputation: 152

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

Upvotes: 2

Related Questions