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

When i get to 'flush privileges' i get a key icon - i attempted the flush privileges three times and it says 'incorrect passwords attempts'...? what does this mean?

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

This worked for me, thank you!

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

Make sure you have Stopped MySQL first (above).
Run the server in safe mode with privilege bypass: sudo mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;
Then
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

I am using MYSQL 8.0.28 on mac m1 and this worked for me, but make sure you close down MYSQL safe start before restarting it after this process and your new password set here will work

Worked for me too! Thank you!