Access denied for user ‘root’@’localhost’

In mysql, Access denied for user ‘root’@’localhost’ error is due to the root user not having sufficient privilege to access the database. Either the root user password is incorrect, or the root user password is not set, or the root user has insufficient privileges.

In mysql, “access denied for user ‘root’@’localhost’ (using password: yes)” error is shown while the access denied for user root localhost using password yes. In mysql, “access denied for user ‘root’@’localhost’ (using password: no)” error is shown while the access denied for user root localhost using password no.

In mysql, if you attempt to access the root password, it throws error saying “Access denied for user ‘root’@’localhost’”. This exception is due to various reasons. Here we can see how this error is handled and how to correct it.

All the privileges should be granted to root user. By default, mysql does not provide the root user with all of the privileges. These privileges should be added to the root user.

If the root user has trouble logging in, the error below will appear in mysql 

Access denied for user ‘root’@’localhost’
access denied for user ‘root’@’localhost’ (using password yes)
access denied for user ‘root’@’localhost’ (using password no)
access denied for user ‘root’@’localhost’ (using password: yes)
access denied for user ‘root’@’localhost’ (using password: no)
access denied for user ”@’localhost’ (using password: no)
access denied for user @’localhost’ (using password: no)

Root cause

You try to connect mysql using root user. Mysql denied access to login with root user. The denial could be due to various reason. Mysql requires valid user name and password to login. The user must have enough privileges to login and should be provided with enough permission.

The above error will be thrown due to any of the above reason is not satisfied. The root password is mismatch or nor provided. The user does not have enough permission to login to the system.

How to reproduce this issue

This error occurs under various conditions.This error will be seen if you login with root user without password. It is a easy way of creating this error. There are various ways to replicate this error.

mysql -u root 

Solution 1

The first option is to use the command below to attempt login with username and correct password. This command will prompt you to enter your password. Enter the right password. Mysql will validate the username and password. If both are matching with the existing system, then mysql will allow to login. The error “Access denied for user ‘root’@’localhost’” is thrown due to the invalid password.

$>mysql -u root -p 
Enter password:

Solution 2

If you have forgotten the root user, the following steps will help you to update your root password. The root user will be modified in PASSWORD() function with the provided password. The password is set as “password” in the example below. This step will allow to reset the root user password.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

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

mysql> flush privileges;

Solution 3

By default, the root user will not have a plugin attached. Update the root user with the plugin ‘ mysql native password ‘ This plugin ‘ mysql native password ‘ allows native authentication. For any command, the error “Access denied for user ‘root’@’localhost’” is shown if the mysql native password plugin is not set to the root user.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> update mysql.user set plugin = 'mysql_native_password' where User='root';

mysql> flush privileges;

Solution 4

The password of the root user and the plugin must be changed. The alter command will change the password of the root user and update the plugin as “mysql native password.” The plugin stores a password in a native format and allows native authentication.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> ALTER mysql.user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> exit

start mysql again

Solution 5

The root user has the admin privileges. Provide all permissions for the root user to connect from the local system. The grant command will add the privileges to the root command. If you want to revert the privilege, use the ‘revoke’ command. The ‘revoke’ command will remove the privileges from the user.

mysql> CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
        or
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;

mysql> FLUSH PRIVILEGES;