Access denied for user root - mysql on MAC OS

Question

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

Answer 1

For MySQL 5.7 I had to use:

UPDATE user SET authentication_string = PASSWORD('YourNewPassword'), password_expired = 'N' WHERE User = 'root';

Answer 2

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.

Answer 3

I had a very hard time in fixing this issue on MAC Sierra, 10.12.6, MySql version 5.7.17

Following steps worked for me:

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

Start MySQL with this command:

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 will open "mysql" prompt. Execute following command in mysql prompt one by one:

use mysql;

UPDATE user SET authentication_string = PASSWORD('my_new_password'), password_expired = 'N' WHERE User = 'root';

FLUSH PRIVILEGES;

EXIT

Now Stop MySql server first then start it using below commands

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

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

Hope this solves your issue.

Answer 4

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.

Answer 5

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.

Answer 6

sudo mysql -uroot
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
flush privileges;
Ctrl+D
mysql -uroot -pnew_password  # will work now

Notice the sudo on the first line.