Access denied for user root - mysql on MAC OS

I know how do skip this problem on ubuntu, but how can i do it on MAC OS?

How can i set password for mysql on MAC?

1) Doesn't work

mysqladmin -u root password NEWPASSWORD

2)Doesn't work

mysqld --skip-grant-tables --skip-networking &

3) This works:

mysql root password forgotten

Solution 1:

You can do the following on Mac (El Capitan)

1. Open a Terminal window, use the command below to stop mysql if it's already running.

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

   You can also check System Preferences > MySQL to see if it is running

2. Start MySQL with this command:

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

3. Open a new terminal window/tab:

   sudo /usr/local/mysql/bin/mysql -u root

   This should open "mysql" prompt. Execute the following command:

   $mysql> UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root';

   Troubleshooting tips:

   A) The command for MySql versions before 5.7 was:

   $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';

   B) If you see ERROR 1046 (3D000): No database selected, then run this command first:

   use mysql;

   C) If you see unknown "Password" field error, then run this command:

   UPDATE USER SET AUTHENTICATION_STRING=password('NewPassword') WHERE user='root'; $mysql> FLUSH PRIVILEGES; $mysql> EXIT

4. Stop MySql server

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

5. Restart MySQL, either through System Preferences > MySql or using a command.

Solution 2:

The solution of

UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root';

wasn’t working for me, but I did

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';

And was able to proceed. I’m using Ver 8.0.12.

Solution 3:

You can do the following on iMac or Mac (High Sierra)

Open a Terminal window, and stop the mysql if it's already running. You can also check this System Preferences > MySQL > see if it is running.

Start MySQL with this command for skipping the main table

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

Open a new terminal window/tab..

sudo /usr/local/mysql/bin/mysql -u root

This should open "mysql" prompt. Execute the below command:

A ) MySQL 5.6 and below

UPDATE mysql.user SET password=PASSWORD('NewPassord') WHERE user='root';

-- or --

B) MySQL 5.7+

UPDATE mysql.user SET authentication_string=PASSWORD('NewPassord') WHERE user='root';

Restart MySQL, either through System Preferences > MySql or using a command.

Solution 4:

MACOS 10.14 MOJAVE || MYSQL 8.0.15

This didn't work on my mac:

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

BUT THIS ACTUALLY WORKED:

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/support-files/mysql.server stop

The installation folder might vary per user, BE AWARE!

Or just Check > System preferences > MySQL > if the server is running, stop it.

then,

Start MySQL with this command:

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/bin/mysqld_safe --skip-grant-tables

Open a new terminal window/tab:

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/bin/mysql -u root

This should open "mysql" prompt. Execute the following command (*scroll right if you don't the full query):

UPDATE mysql.user SET authentication_string='your-password-goes-here' WHERE user='root' and host='localhost';

REMEMBER THAT

mysql-8.0.15-macos10.14-x86_64

(in my case) is the installation folder on your local machine, and it might or might not be different than mine because of OS versions, mysql versions, installation methods used, etc.