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

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 tướng '/var/log/mysql/error.log'.

Simply press Ctrl+Z or Ctrl+C to tướng interrupt and exit process

4. mysql -u root

Welcome to tướng the MySQL monitor. Commands kết thúc with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, năm ngoái, 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 tướng 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 tướng get a quicker startup with -A

Database changed

6. mysql> update user phối 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 tệp tin /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 tướng the MySQL monitor. Commands kết thúc 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 tướng log in with the normal ubuntu user it was throwing bầm this issue.

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

But I was able to tướng login to tướng MySQL via the super user. Using the following commands I was able to tướng 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 tướng login to tướng Mysql with the normal tài khoản.

If you are getting this error in Workbench but you are able to tướng 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 lượt thích 'Database changed' then type:

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

After that type:

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

Then type:

FLUSH PRIVILEGES;

Then simply exit:

quit

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

alter user 'root'@'localhost' identified with mysql_native_password by '$your_password$';

it worked for bầm.

note: use strong password

for example

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

I faced this problem while installing Testlink on Ubuntu server, I followed these steps

mysql -u root
use mysql;
update user phối password=PASSWORD("root") where User='root';
flush privileges;
quit

Now stop the instance and start again i.e

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

Well the easiest way to tướng reset root password is:

• restart mysqld --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to tướng use --skip-grant-tables in conjunction with --skip-networking to tướng prevent remote clients from connecting.

• Connect to tướng the mysqld server with this command:

• shell> mysql Issue the following statements in the mysql client. Replace the password with the password that you want to tướng use.

• mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') -> WHERE User='root'; mysql> FLUSH PRIVILEGES;

• Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).

Source Mysql documentation and personal experience:

http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

In my case:

• I phối plugin authentication to tướng "" (empty) and I can't run rẩy mysql server:

SOLUTION:

1. nano /etc/mysql/my.cnf
2. edit:

[mysqld]

skip-grant-tables

3. service mysql restart
4. mysql -u root
5. use mysql
6. UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root'
7. flush privileges

I resolved the same issue by running Workbench as administrator.

...I guess it's because of restrictions on company computers, in my case...

MySQL mặc định password for root is assigned depending on the way you have installed MySQL.

If you have installed it from MySQL Yum repository, MySQL SUSE repository, or RPM packages directly downloaded from Oracle, you can obtain the password using following command:

sudo grep 'temporary password' /var/log/mysqld.log

Try out the following steps to tướng overcome this issue:

1. Open terminal / command prompt and navigate to tướng the bin thư mục of the MySQL installation thư mục. Then run rẩy the command mysqld --console.
2. If you can see that line 171010 14:58:22 [Note] --secure-file-priv is phối to tướng NULL. Operations related to tướng importing and exporting data are disabled, after executing the above command from the command prompt.
3. Then you need to tướng kiểm tra that the mysqld is either blocked by the Windows Firewall or another program.
4. If it's blocked by Windows Firewall then need to tướng unblock from it and save settings.
5. To unblock the mysqld or mysql application, follow the below steps:
   1. Go to tướng command prompt and type wf.msc to tướng open the firewall settings.
   2. Click on Allow an tiện ích or feature through Windows Firewall.
   3. Check the mysqld or mysqld instances are available in the list and kiểm tra the checkbox for the domain name, public and private and save the settings.
6. Return to tướng the bin thư mục and try the command from step 1 again.
7. It should work fine and not show any errors.

It should be possible to tướng run rẩy the MySQL console without any problems now!

I resolved the same issue using next sql and restarting MySQL server:

update mysql.user phối Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',Create_tablespace_priv='Y'
where user='root';

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

The answer to tướng 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

I don't think you have to tướng escape the --init-file parameter:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.6\\my.ini" --init-file=C:\\mysql-init.txt

Should be:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.6\\my.ini" --init-file=C:\mysql-init.txt

for the above problem ur password in the system should matches with the password u have passed in the program because when u run rẩy the program it checks system's password as u have given root as a user ví gives u an error and at the same time the record is not deleted from the database.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
class Delete
{
    public static void main(String []k)
    {
        String url="jdbc:mysql://localhost:3306/student";

        String user="root";
        String pass="jacob234";
        try
        {
            Connection myConnection=DriverManager.getConnection(url,user,pass);
            Statement myStatement=myConnection.createStatement();
            String deleteQuery="delete from students where id=2";
            myStatement.executeUpdate(deleteQuery);
            System.out.println("delete completed");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
    }
}

Keep ur system password as jacob234 and then run rẩy the code.

With bầm was the same problem, but it was caused, because i was using the mysql server on 32 (bit) and the workbench was running on 64(bit) version. the server and the workbench need to tướng has the same version.

xpress

I was facing the same problem when I'm trying to tướng connecting Mysql database using the Laravel application. I would lượt thích to tướng recommend please kiểm tra the password for the user. MySQL password should not have special characters lượt thích #, &, etc...

cause might be missing mysqld tệp tin in /var/run/mysqld

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables
sudo service mysql start

if tệp tin does not exits then create file

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

check now you are able to tướng login mysql -uroot -p123 otherwise do

sudo mysql -u root
use mysql;
show tables;
describe user;
update user phối authentication_string=password('1111') where user='root';
FLUSH PRIVILEGES;
exit;
mysql -uroot -p123

link - mysqld_safe Directory '/var/run/mysqld' for UNIX socket tệp tin don't exists

For windows:-

If the instance configuration fails with similar issue and if you cannot log in to tướng the root tài khoản.

Steps I followed to tướng fix the issue:-

• Stop MySql service if running.
• Uninstall MySql using 'remove' option from the installation wizard.
• If MySql service is not removed from services,

sc delete

• Delete all data containing in the MySql thư mục ('Program files', 'Program data' or the custom installation directory you have given).
• Remove MySql path from the environmental variable.
• Disable windows firewall.
• Reinstall and config root tài khoản with new password.

Error : Access denied for user 'sa'@'Developer' (using password: YES)

By using the Create User feature and accessing all privileges on the MySQL server, I was able to tướng resolve the same problem.

Create user 'sa'@'Developer' identified by 'developer@123';
Grant all privileges on * . * to tướng 'sa'@'Developer';
Flush privileges;