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

Edit my.ini file and add skip-grant-tables and restart your MySQL server :

[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
skip-grant-tables
# Change here for bind listening
# bind-address="127.0.0.1" 
# bind-address = ::1 

Please run this below command from the console to skip the user table verification while launching MySQL database from the command prompt

mysqld -skip-grant-tables

'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.

[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 does 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 a new terminal for the 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 log in as root user with the password you set

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

7. 1) You can set the root password by invoking MySQL console. It is located in 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, the 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 previously changed the password for 'root@localhost', then you have to do 2 things to solve the error "Access denied for user 'root@localhost'":
   1. if ['password'] have 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.