Yoodley is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

Have you recently been facing the error that says ‘Access denied for user ‘root’@’localhost’, whenever you try to operate MySQL?  In MySQL, the relational database management system, the error might show up when you attempt to access the root password. 

This post elucidates all possible solutions to help fix the error. However, it is equally important to understand what is causing the error ‘Access denied for user ‘root’@’localhost’ in the first place. 

Therefore, let us briefly understand what is causing MySQL to throw up the error ‘Access denied for user ‘root’@’localhost’, and then move on to solutions to fix the problem, followed by answering a few of the most asked questions relevant to the same.

Why does MYSQL say ‘Access denied for user ‘root’@’localhost’?

In all simplicity, you are facing the error ‘Access denied for user ‘root’@’localhost’ because as the root user, you do not have the adequate privilege (permission, in other terms) to access the MySQL database.

Now, to further the discussion, you could be facing the issue due to multiple reasons, some of which include:

• When a user is non-existent to the MySQL server tries accessing the MySQL database.
• When no privilege exists for the user (as mentioned).
• If the user inputs the wrong username or password.

Now the error ‘Access denied for user ‘root’@’localhost’ is extensively of two types:

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

The former error message is thrown up when the root user tries to access the MySQL database with the password ‘yes’, which is wrong or different from the original password. Similarly, the latter error message occurs when the root user enters the password as ‘no’, which is incorrect.

 

How to fix ‘Access denied for user ‘root’@’localhost’?

Now that you know what could possibly be restraining you from accessing the database and throwing the message ‘Access denied for user ‘root’@’localhost’, below we look at X solutions to fix the same.

Solution:

In theory, it is the lack of permissions that throw the error ‘Access denied for user ‘root’@’localhost’. To provide all the permissions to the root user, you can use the ‘grant’ command to add privileges. Use the following chain of commands to grant privileges to the root 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;

To take away the privileges and revert the changes, you can use the ‘revoke’ command. The ‘revoke’ command will do exactly what it sounds like: revoke all the privileges from the root user once you choose to remove the permissions/privileges.

Solution:

If your MySQL 5.7 (or higher version) is running in Ubuntu systems, the root user is to be authenticated using not the password but the auth_sccket plugin by default. 

Although this can come in handy and provide great usability and security, it can cause difficulty when you are trying to allow programs from the outside, such as phpMyAdmin, to access the user.

The main solution to this is to connect MySQL as root by switching authentication from auth_socket to mysql_native_password in the terminal:

sudo mysql

• Run the ALTER USER command to configure the root account and change the authentication type to password.

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

• Now, run FLUSH PRIVILEGES; in the command prompt.
• To confirm if the authentication configuration has been changed to a password, type the following command:

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

• Once confirmed, exit MySQL shell using 

exit

Solution:

This method works specifically for all macOS users. 

• Firstly, install a version of MySQL that is compatible with your MacOS’s version.
• Next, set the password for root during installation and note it down somewhere. Use the following command to set the root password:  

root <root-password>

• Select Use Legacy Password Encryption from the two options available, the other one being Use Strong Password Encryption.
• Using the search tool, open MySQL.prefPane and select the configuration tab.
• Click ‘Select’ option of the ‘Configuration File’ and select /private/etc/my.cnf
• Now, from the MySQL terminal, open a new file or an existing file, and type the following command:

 [mysqld]

 skip-grant-tables

With skipping the grant table, you can log in from anywhere and do almost anything on the database server.

• Next, restart MySQL as follows:

ps aux | grep mysql

kill -9 <pid1> <pid2>

• Upon restarting, run the following command to fix the error:

/usr/local/mysql-<version>-macos<version>-x86_64/bin/mysql -uroot -p<root-password>

Solution:

As we mentioned previously, you could be facing the error because you have been feeding the server the wrong password. Whether you don’t know the password or forgot the correct one, here is how you can reset it, so you can access the MySQL database as a root user again:

• Open /etc/mysql/my.cnf from Configuration File. Now add ‘skip-grant-tables’ under [mysqld] section. Note that ‘skip-grant-tables’ is dangerous; therefore make sure to remove it towards the end of the solution.
• Restart MySQL with the command given below:

service mysql restart

• Since you have added the line of code ‘skip-grant-tables‘ already, you will be able to log in since MySQL will be skipping the grant-tables. Use the command below to log in:

mysql -u root

• Once logged in, flush privileges that are restraining you from accessing the database originally using the command below:

mysql> flush privileges;

• Now, set a new password using the command below:

Password=PASSWORD(‘my_password’) where USER=’root’;

FLUSH PRIVILEGES;

• Since you have already reset the password, it is time to remove ‘skip-grant-tables’ from etc/mysql/my.cnf.
• Restart MySQL again and log in using the new password. The service will no longer show the error.

 

FAQs

1. Where is the MYSQL error log?

Error logs are one of the most important logs in terms of IT operations because it helps in detecting and diagnosing functional problems that simply improves performance. 

The MYSQL error log basically contains error messages, warnings and different notes which are created during the startup and shutdown phases.

MYSQL error logging is always enabled and allows the users to set destination, verbosity levels and time zone. 

A file or the console is a general location or, say, destination of error logs. When no location is specified, then in windows, the error logs are written to host_name.err ( host_name is the host system name) in the data directory, whereas in UNIX/Linux, the console is the default destination of errors. 

A user can change the destination of error logs by specifying the location in the –log-error option (i.e., –log-error=”G:/TMP/mysql_logs/mysql_error.err” or –log-error=/var/log/mysql/error.log).

 

2. How to change the root password for MySQL?

To change the root password in MySQL:

• In order to change the password for MYSQL, a new file must be created with the following contents – ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘PASSWORD’ ; (here password is the new password to be used) and this file should be saved as ~/mysql-pwd. 
• Stop MYSQL with sudo systemctl stop mysql command and then issue the command: sudo mysqld -init-file=~/mysql-pwd. As the command prompt returns, restart the MYSQL using the sudo systemctl start mysql command. 
• Now the MYSQL root user password is changed successfully, and you can login with the new admin password. 

 

3. How to recover the root password for MySQL?

To recover the root password in MySQL:

• Use the sudo service mysql stop command to stop the MYSQL server.
• Now start the MYSQL server using the sudo mysqld_safe –skip-grant-tables –skip-networking &
• Use the mysql -u root command to connect to the MYSQL server as the root user.
• Issue the following commands to reset the root password:

mysql> use mysql;

​mysql> update user set authentication_string=password(‘NEWPASSWORD’) where user=’root’;

​mysql> flush privileges;

​mysql> quit

Note: In the above commands, NEWPASSWORD is the new password to be used.

• Restart the MYSQL daemon using sudo service mysql restart and log in with the new password.