Access denied for user root localhost using password yes windows

If you have that same problem in MySql 5.7.+ :

Access denied for user 'root'@'localhost'

it’s because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

SELECT user,authentication_string,plugin,host FROM mysql.user;

then you will see it :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connection with root and password, then update the values in the table with command :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you’ll see it has changed :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

And that’s it. You can run this process after running and completing the sudo mysql_secure_installation command.

For mariadb, use

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');

to set password.
More at https://mariadb.com/kb/en/set-password/

answered Nov 26, 2018 at 18:59

Use the instructions for resetting the root password — but instead of resetting the root password, we’ll going to forcefully INSERT a record into the mysql.user table

In the init file, use this instead

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

answered Jul 31, 2013 at 16:42

It didn’t like my user privilege so I SUDO it.
(in bash << sudo set user and password)
(this gives username of root and sets the password to nothing)
(On Mac)

sudo mysql -uroot -p

Try the following commands

~$ sudo /etc/init.d/mysql stop
~$ sudo mysqld_safe --skip-grant-tables &
~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> use mysql;

mysql> update user set password=PASSWORD("root") where User='root';

mysql> flush privileges;

mysql> quit

~$ sudo /etc/init.d/mysql stop

Stopping MySQL database server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

~$ sudo /etc/init.d/mysql start

~$ mysql -u root -p

* MySQL Community Server 5.6.35 is started
~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

answered Sep 20, 2017 at 13:28

for the people who are facing below error in mysql 5.7+ version —

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

1. Open new terminal

2. sudo /etc/init.d/mysql stop
   …
   MySQL Community Server 5.7.8-rc is stopped

3. sudo mysqld_safe --skip-grant-tables &
   this will skipp all grant level privileges and start the mysql in safe mode
   Sometimes the process got stucked just because of

grep: write error: Broken pipe
180102 11:32:28 mysqld_safe Logging to ‘/var/log/mysql/error.log’.

Simply press Ctrl+Z or Ctrl+C to interrupt and exit process

1. mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

1. mysql> use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

1. mysql> update user set authentication_string=password('password') where user='root';
   Query OK, 4 rows affected, 1 warning (0.03 sec)
   Rows matched: 4 Changed: 4 Warnings: 1

2. mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)

3. mysql> quit
   Bye

4. sudo /etc/init.d/mysql stop

..180102 11:37:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
.
* MySQL Community Server 5.7.8-rc is stopped
arif@ubuntu:~$ sudo /etc/init.d/mysql start
..
* MySQL Community Server 5.7.8-rc is started

1. mysql -u root -p

   Enter password:

   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 2
   Server version: 5.7.8-rc MySQL Community Server (GPL)

after mysql 5.7+ version the column password replaced by name authentication_string from the mysql.user table.

hope these steps will help anyone, thanks.

answered Jan 2, 2018 at 6:20

If you are getting this error in Workbench but you are able to log in from terminal then follow this steps.

First simply log in with your current password:

sudo mysql -u root -p

Then change your password because having low strength password gives error sometimes.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';

FLUSH PRIVILEGES;

Then simply exit and again login with your new password:

quit

sudo mysql -u root -p

Once you successfully logged in type the command:

use mysql;

It should show a message like ‘Database changed’ then type:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

After that type:

UPDATE mysql.user set authentication_string=PASSWORD('new-strong-password') where user='root';

Then type:

FLUSH PRIVILEGES;

Then simply exit:

quit

Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.

answered Apr 17, 2020 at 17:04

For my case, I found this error after fresh installation of mysql on Mac OS Big Sur.

What i did to fix it was:
I click on the apple logo, go to system preferences and then click on mysql.

There’s an initialize database button on the opened settings window, I click on that, and then when I try to access again, it’s solved.

answered Apr 4, 2021 at 4:04

answered Aug 4, 2018 at 6:57

avijendravijendr

3,9982 gold badges31 silver badges46 bronze badges

CecherzCecherz

561 silver badge7 bronze badges

nayan dhabardenayan dhabarde

1,8141 gold badge20 silver badges38 bronze badges

Try out the following steps to overcome this issue:

1. Open terminal / command prompt and navigate to the bin folder of the MySQL installation folder. Then run the command mysqld --console.
2. If you can see that line 171010 14:58:22 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled, after executing the above command from the command prompt.
3. Then you need to check that the mysqld is either blocked by the Windows Firewall or another program.
4. If it’s blocked by Windows Firewall then need to unblock from it and save settings.
5. To unblock the mysqld or mysql application, follow the below steps:
   1. Go to command prompt and type wf.msc to open the firewall settings.
   2. Click on Allow an app or feature through Windows Firewall.
   3. Check the mysqld or mysqld instances are available in the list and check the checkbox for the domain, public and private and save the settings.
6. Return to the bin folder and try the command from step 1 again.
7. It should work fine and not show any errors.

It should be possible to run the MySQL console without any problems now!

I worked on Access Denied for User ‘root’@’localhost’ (using password: YES) for several hours, I have found following solution,

The answer to this problem was that in the my.cnf located within
/etc/mysql/my.cnf

the line was either 
bind-address = 127.0.0.1 
            (or)
bind-address = localhost
            (or)
bind-address = 0.0.0.0

I should prefer that 127.0.0.1

I should also prefer 0.0.0.0, it is more flexible 
because which will allow all connections

answered Nov 15, 2017 at 6:36

JordanJordan

1481 silver badge11 bronze badges

answered Feb 9, 2018 at 14:11

answered Dec 16, 2018 at 16:24

answered Mar 13, 2020 at 16:23