Access denied for user 'root@localhost' (using password:NO)
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:
- I stopped the MySQL Service from Windows services
- Opened CMD
- Changed the location to c:\program files\mysql\bin
-
Executed the command below
mysqld --defaults-file="C:\\program files\\mysql\\mysql server 5.1\\my.ini" --init-files=C:\\root.txt
The command ran with a warning about character set which I mentioned below
- I start the MySQL service from Windows services
-
I write in the command line
mysql -u root -p
EnterPassword: 123 // 123 was the password
-
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.
Solution 1:
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)
-
Stop the service/daemon of mysql running
[root ~]# service mysql stop mysql stop/waiting
-
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.
-
enter the mysql command prompt
[root ~]# mysql -u root mysql>
-
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)
-
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
-
Now you can successfully login as root user with the password you set
[root ~]# mysql -u root -pYourNewPassword mysql>
Solution 2:
You can reset your root password. Have in mind that it is not advisable to use root without password.
Solution 3:
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'":
- if ['password'] have a empty quotes like ' ' then put your password between quotes.
- 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.
Solution 4:
Use mysql -u root -p It will ask for password, insert password and enter.