Reset MySQL Root Password in Mac OS

Reset MySQL Root Password in Mac OS.md

Reset mysql root password in Mac OS:

First Stop MySQL:

1. Go to: 'System Preferences' >> 'MySQL' and stop MySQL

OR,

1. sudo /usr/local/mysql/support-files/mysql.server start
2. sudo /usr/local/mysql/support-files/mysql.server stop
3. sudo /usr/local/mysql/support-files/mysql.server status

Process to Reset MySQL Root Pass in Mac:

1. Make sure you have Stopped MySQL first (above).

2. Run the server in safe mode with privilege bypass: sudo mysqld_safe --skip-grant-tables

3. In a new window connect to the database, set a new password and flush the permissions & quit: mysql -u root

4. For MySQL older than MySQL 5.7 use:

   UPDATE mysql.user SET Password=PASSWORD('rootpass') WHERE User='root';

   For MySQL 5.7+ use:

   UPDATE mysql.user SET authentication_string=PASSWORD("rootpass") WHERE User='root';

5. Now flush privileges:

   FLUSH PRIVILEGES;

6. Restart MySQL server.

7. More info: http://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-os-x

For mysql 8 in MacOS

1. stop all mysql server service
2. cd /usr/local/mysql/bin
3. sudo ./mysqld_safe --skip-grant-tables
4. this will start mysql server in this terminal

Start new terminal

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

Thanks! works for mysql 8.0.28

Thank you so much! This worked for me.

To add, if your sql was installed with brew, you may not have the executables in /usr/local/mysql/bin, so check /opt/homebrew/Cellar/mysql/8.3.0_1/bin and just follow the exact same steps.

like so, i have copied from the solution of @DouggyC , and just added the file path

For mysql 8.3.0 in MacOS

stop all mysql server service
cd /opt/homebrew/Cellar/mysql/8.3.0_1/bin
sudo ./mysqld_safe --skip-grant-tables
this will start mysql server in this terminal

Start new terminal

cd /opt/homebrew/Cellar/mysql/8.3.0_1/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
`

When I typed this: mysql -u root, I got this error!

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
had the same issue too, it was because you had not started the mysql service again.