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

Question

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.

The first command is wrong, should be `-p` or `--password`, but not `password`. That's why mysql responds with USING PASSWORD NO. — Konerak, Jun 08 '10 at 05:59
Nobody seems to have given him the obvious answer. The password for the `root` userid is **blank** after the initial install. So just do `mysql -u root -p` and then just hit enter, i.e. leave the password blank. Then of course put a password on the `root` userid. — RiggsFolly, Feb 05 '15 at 10:54
@RiggsFolly nope, doesn't work! blank password gives me access denied! — J86, Jul 20 '15 at 20:26
I want add a comment as I am not sure the accepted answer is the correct one. For me typing mysql -u root -p and then once prompt for the password, give the right one(the one set at installation) worked. Instead proving the name of a DB to the -p with a DB I know exist did not worked. — Carmine Tambascia, May 26 '18 at 19:14

score 89 · Answer 1 · edited Dec 17 '16 at 03:48

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> 
```

Press Enter after "[root ~]# mysqld_safe --skip-grant-tables &" and open another terminal to enter "[root ~]# mysql -u root" — Liuda, Feb 23 '16 at 20:01
i met this problem, and i did alot, just forget restarting the db services... many thanks! — Bruce Lee, Jun 25 '17 at 04:40
At step 3, I get `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)` — MrMartin, Mar 01 '19 at 12:04
@MrMartin, create that dir ```sudo mkdir -p /var/run/mysqld``` and give permission to it ```sudo chown mysql:mysql /var/run/mysqld``` — niceday, Jul 02 '19 at 10:00
`SELECT * from user;` gives a non-empty table. So I have to skip truncating the table, then `grant all privileges on *.* to root@localhost ...` gives an error: `The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement`. How to solve this? — Defd, Dec 24 '20 at 05:10

Bozho · Accepted Answer · 2010-06-08T07:32:57.173

60

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

edited Jun 08 '10 at 07:32

answered Jun 08 '10 at 05:54

Bozho

554,002
136
1,025
1,121

@Bozho - I follow the instruction aand create a text file and call it from the mentioned command, after that I face with `100608 [warning] --default-character-set is deprecated and will be removed in a future release. please use --character-set-server instead` .-------------------------------- and finally when I start my mysql service again I face withthe exact error but this time with `(using password:yes)` what should I do? – Nasser Hadjloo Jun 08 '10 at 06:13
@Bozo - Note that I couldn't run your command with `mysqldt-nt` I run it by `mysqldt -nt` Actuallythe first one is not recognizable for cmd. I'm waiting to hear from you. – Nasser Hadjloo Jun 08 '10 at 06:33
@Bozho - I've update my question with the bug. thank you for your time. – Nasser Hadjloo Jun 08 '10 at 08:50
@Bozho - is there a way to update / customise my mysql errors? **Update "Access denied for user 'root@localhost' (using password:NO)" to "Access denied for user 'myname@localhost' (using password:NO)" ** – Hitesh Nov 17 '16 at 09:57

score 23 · Answer 3 · edited Mar 13 '19 at 21:06

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.

no you cant.. mysql will not start: c:\wamp64\bin\mysql\mysql5.7.23\bin>mysql ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO) — Jaxx0rr, Dec 19 '18 at 19:44

score 12 · Answer 4 · answered Jul 05 '16 at 10:54

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

score 9 · Answer 5 · answered May 16 '19 at 11:10

9

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

answered May 16 '19 at 11:10

Vikash Kumar

367
3
5

This worked after I was getting Access denied for users. – Shawn W Jun 13 '20 at 02:15
This worked when all of the above did not. (Mac os big sur, fresh install of MySQL 8) – Shad Apr 21 '21 at 21:01

score 6 · Answer 6 · edited Mar 16 '18 at 00:43

6

Simply edit my.ini file in C:\xampp\mysql\bin path. Just add:

skip-grant-tables

line in between lines of # The MySQL server [mysqld] and port=3306. Then restart the MySQL server.

Looks like:

edited Mar 16 '18 at 00:43

Stephen Rauch

40,722
30
82
105

answered Mar 16 '18 at 00:19

Ryan Oscar

229
1
4
16

When I do this I get another error `communications link failure` – Ahmed Hussein May 24 '18 at 21:01

score 5 · Answer 7 · answered Jun 08 '10 at 05:56

5

Make sure the MySQL service is running on your machine, then follow the instructions from MySQL for initially setting up root (search for 'windows' and it will take you to the steps for setting up root):

http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html

answered Jun 08 '10 at 05:56

sholsapp

13,679
8
44
62

This solution wont work for me because i don't have the password which it need in second step. – Nasser Hadjloo Jun 08 '10 at 06:41
This is what I was looking at when I sent the link, are you sure this doesn't work? I don't see any reference to the old password. For Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd'); – sholsapp Jun 08 '10 at 19:32
1

As I told before when I write `mysql -u root` I face with problem and I can not login to mysql to run second line. so How to use this command? – Nasser Hadjloo Jun 09 '10 at 06:49

score 4 · Answer 8 · answered Apr 05 '14 at 18:50

4

Another solution if someone gets the error The specified password for user account ‘root’ is not valid, or failed to connect to the database server also with the right password, is the follow

•In the Windows registry, delete the mysql_pwd reg key under HKCU\Software\Microsoft\WebPlatformInstaller

•Unistall older version of MySQL .NET connector

•Download and install the latest MySql .NET Connector.

answered Apr 05 '14 at 18:50

Silverstorm

12,730
2
34
52

Checking password and database name worked for me.. +1 – Abhinav Dobhal Mar 29 '20 at 19:04

score 4 · Answer 9 · answered Dec 01 '16 at 02:30

For MySQL 5.7. These are the below steps:

Stop your MySQL server completely. This can be done by accessing the Services window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

Open your MS-DOS command prompt using "cmd" inside the Run window. Inside it navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

Execute the following command in the command prompt: mysqld.exe -u root --skip-grant-tables

Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

Navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

Enter mysql and press enter.

You should now have the MySQL command prompt working. Type use mysql; so that we switch to the "mysql" database.

Execute the following command to update the password:

update user set authentication_string=password('1111') where user='root';

score 4 · Answer 10 · edited Jun 20 '20 at 09:12

For some information I've get error after changing password:

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

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

In both cases there was error.

But the thing is after that I've tried it with

mysql -uroot -ppassword instead of

mysql -u root -p password -> with spaces between -uroot and -ppassword so maybe if someone get trouble can try this way.

score 3 · Answer 11 · answered Jun 22 '15 at 19:21

In your code replace the 'root' with your Server username and password with your server password. For example if you have DB and your php files on the server http://www.example.com then obviously you would have to enter into this server site using your username and password.

score 3 · Answer 12 · answered Aug 04 '18 at 06:22

3

If you are using XAMPP just go to C:\xampp\phpMyAdmin and then open config.inc.php find $cfg['Servers'][$i]['password'] = '' line and put your password there.

answered Aug 04 '18 at 06:22

Ajay Rawat

138
1
10

score 2 · Answer 13 · edited Feb 05 '15 at 08:20

2

Some times it just happens due to installation of Wamp or changing of password options of root user. One can use privilages-->root (user) and then set password option to NO to run the things without any password OR set the password and use it in the application.

edited Feb 05 '15 at 08:20

Pieter Goosen

9,050
5
29
53

answered Feb 05 '15 at 08:07

Sayyad

21
1

score 2 · Answer 14 · edited May 19 '20 at 16:13

2

Change the password from config.inc.php present in C:\xampp\phpMyAdmin.
Type mysql -u root -p in the command prompt.
You will be asked to enter the password. Enter that password which you updated in the config.inc.php.

edited May 19 '20 at 16:13

double-beep

3,889
12
24
35

answered May 19 '20 at 15:39

user12682744

31
3

score 2 · Answer 15 · answered May 21 '20 at 18:54

2

if you changed the port to non standard one, then you need to specify it:

$connection = mysqli_connect('localhost:3308', 'root', '', 'loginapp');

answered May 21 '20 at 18:54

Mitch

237
1
17

score -1 · Answer 16 · answered Apr 27 '21 at 08:30

-1

It happens because of the security reason.

try with the following

mysql -u root -p

click enter and enter the password and try again

answered Apr 27 '21 at 08:30

Janadari Ekanayaka

489
4
6

https://stackoverflow.com/a/56167185/2227743 – Eric Aya Apr 27 '21 at 09:50

score -2 · Answer 17 · edited Mar 04 '14 at 20:47

-2

mysqladmin -u root -p password

enter your current password

then

enter your new password

edited Mar 04 '14 at 20:47

0bserver07

3,043
1
23
53

answered Oct 07 '13 at 16:26

djrconcepts

626
5
6

1

The whole point is that the OP _doesn't know_ the current password, because _it was never set_. – Nateowami Jul 14 '14 at 11:12
I knew my password but had issue with login with temp password. this answer worked for me – Bernard Jan 15 '18 at 00:01

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

17 Answers17

Linked

Related