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

Question

for this kind of error; you just have to set new password to the root user as an admin. follow the steps as follows:

[root ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:NO)

1. Stop the service/daemon of mysql running

   [root ~]# service mysql stop   
   mysql stop/waiting
   

2. Start mysql without any privileges using the following option; This option is used to boot up and do not use the privilege system of MySQL.

   [root ~]# mysqld_safe --skip-grant-tables &
   

At this moment, the terminal will seem to halt. Let that be, and use new terminal for next steps.

3. enter the mysql command prompt

   [root ~]# mysql -u root
   mysql> 
   

4. Fix the permission setting of the root user ;

   mysql> use mysql;
   Database changed
   mysql> select * from  user;
   Empty set (0.00 sec)
   mysql> truncate table user;
   Query OK, 0 rows affected (0.00 sec)
   mysql> flush privileges;
   Query OK, 0 rows affected (0.01 sec)
   mysql> grant all privileges on *.* to root@localhost identified by 'YourNewPassword' with grant option;
   Query OK, 0 rows affected (0.01 sec)
   

*if you don`t want any password or rather an empty password

    mysql> grant all privileges on *.* to root@localhost identified by '' with grant option;
    Query OK, 0 rows affected (0.01 sec)*
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

Confirm the results:

    mysql> select host, user from user;
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

5. Exit the shell and restart mysql in normal mode.

   mysql> quit;
   [root ~]# kill -KILL [PID of mysqld_safe]
   [root ~]# kill -KILL [PID of mysqld]
   [root ~]# service mysql start
   

6. Now you can successfully login as root user with the password you set

    [root ~]# mysql -u root -pYourNewPassword 
    mysql> 
   

You can reset your root password. Have in mind that it is not advisable to use root without password.

1) You can set root password by invoking MySQL console. It is located in

C:\wamp\bin\mysql\mysql5.1.53\bin by default.

Get to the directory and type MySQL. then set the password as follows..

    > SET PASSWORD FOR root@localhost = PASSWORD('new-password');

2) You can configure wamp's phpmyadmin application for root user by editing

C:\wamp\apps\phpmyadmin3.3.9\config.inc.php 

Note :- if you are using xampp then , file will be located at

C:\xampp\phpMyadmin\config.inc.php

It looks like this:

        $cfg['Servers'][$i]['verbose'] = 'localhost';
        $cfg['Servers'][$i]['host'] = 'localhost';
        $cfg['Servers'][$i]['port'] = '';
        $cfg['Servers'][$i]['socket'] = '';
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['extension'] = 'mysqli';
        $cfg['Servers'][$i]['auth_type'] = 'config';
        $cfg['Servers'][$i]['user'] = 'root';
        $cfg['Servers'][$i]['password'] = 'YOURPASSWORD';
        $cfg['Servers'][$i]['AllowNoPassword'] = false;

The error "Access denied for user 'root@localhost' (using password:NO)" will be resolved when you set $cfg['Servers'][$i]['AllowNoPassword'] to false

If you priviously changed the password for 'root@localhost', then you have to do 2 things to solve the error "Access denided for user 'root@localhost'":

1. if ['password'] have a empty quotes like ' ' then put your password between quotes.
2. change the (using password:NO) to (using password:YES)

This will resolve the error.

Note: phpmyadmin is a separate tool which comes with wamp. It just provide a interface to MySQL. if you change my sql root's password, then you should change the phpmyadmin configurations. Usually phpmyadmin is configured to root user.

Use mysql -u root -p It will ask for password, insert password and enter.

I was getting the same error on OS X El captain. Mysql version 5.7 . I was able to connect to mysql with root after executing these steps.

Stop the mysql server

sudo mysql.server stop

Start mysql in safe mode

sudo mysqld_safe --skip-grant-tables

Using mysqld, Change the database to mysql and update the details for user 'root'.

show databases;
use mysql;
UPDATE mysql.user 
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
exit;

After that kill the 'mysqld_safe' process and start mysql normally. You should be able to login to mysql using root and new password. SQL docs for more details