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

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.