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

Nasser Hadjloo picture Nasser Hadjloo · Jun 8, 2010 · Viewed 758k times · Source

I'm new to MySQL, I'm trying to run WordPress in my Windows desktop and it needs MySQL.

I install everything with Web Platform Installer which is provided by Microsoft. I never set a root password for MySQL and in the final step of installing WordPress, it asks for a MySQL server password.

What is the default password for root (if there is one) and how to change it?

I tried:

mysql -u root password '123'

But it shows me:

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

After this I try:

mysql -u root -p

However, it asks for a password which I don't have.


Update: as Bozho suggested, I did the following:

  1. I stopped the MySQL Service from Windows services
  2. Opened CMD
  3. Changed the location to c:\program files\mysql\bin
  4. Executed the command below

    mysqld --defaults-file="C:\\program files\\mysql\\mysql server 5.1\\my.ini" --init-files=C:\\root.txt

  5. The command ran with a warning about character set which I mentioned below

  6. I start the MySQL service from Windows services
  7. I write in the command line

    mysql -u root -p EnterPassword: 123 // 123 was the password

  8. The command line shows the following error

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

How do I solve this? I'm waiting to hear from you.

Answer

user2977819 picture user2977819 · May 29, 2014

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.

  1. enter the mysql command prompt

    [root ~]# mysql -u root
    mysql> 
    
  2. 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)
  1. 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
    
  2. Now you can successfully login as root user with the password you set

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