Stuck with access Denied for user 'root' @ 'localhost' - Terminal, Mac

Question

I am stuck trying to access mysql. This is my first time, please be patient with me.

At first I tried to configure Ruby and Rails, and everything worked correctly, expecting that access would be denied when connecting to the server. I executed this command.

mysql -uroot -p 

I tried various passwords, including leaving it blank and getting this error.

 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

I assume that I need to reset the password for the root user, but I just can't get it to work.

Answer 1

One method:

• stop your mysql server.

• start the MySQL server with the --skip-grant-tables option. It allows you to connect to the server without a password.

   /path/to/mysqld --skip-grant-tables & 

• connect to your server using mysql client:

   mysql 

• change the root password (replace NewPassord with what you want):

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

• restart the MySQL server.

There are other ways to reset the MySQL user password: http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

Answer 2

@Muur the last step did not work for me (mysql Ver 14.14 Distrib 5.7.19) I had to change the request to update user set authentication_string=password('1111') where user='root'; update user set password_expired=N where user='root';

Answer 3

I had a similar problem and it worked like a charm -

Later versions of mysql implement a newer authentication scheme, not all libraries conform to this. You can revert to classic authentication by adding the following entry to your my.cnf

 [mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 # Some libraries not compatible with latest authentication scheme as per the SO article [1]. default-authentication-plugin=mysql_native_password 

Just add the following to /private/etc/my.cnf

# Allow connections from localhost only
bind-address = 127.0.0.1

Link: https://www.reddit.com/r/mysql/comments/ae7rf4/access_denied_for_user_rootlocalhost_mac_os/