Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

Question

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/

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;

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> 

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

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

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

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

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

8. mysql> quit Bye

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

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

I was using ubuntu 18 and simply installed MySQL (password:root) with the following commands.

sudo apt install mysql-server
sudo mysql_secure_installation

When I tried to log in with the normal ubuntu user it was throwing me this issue.

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But I was able to login to MySQL via the super user. Using the following commands I was able to log in via a normal user.

sudo mysql    
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;

Then you should be able to login to Mysql with the normal account.