FB

This “access denied” error is one of the most common errors you’ll get when working with MySQL.

Learn how to fix it, and see a range of solutions if the suggested fix does not work, in this article.

Access Denied Error

When you try to connect to a MySQL database on your own computer (called “localhost”), you may get this error:

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

You might get an error code in front of it:

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

You might also get the error with “using password no”:

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

You’ll see this if you log into MySQL using the command line:

mysql -u root -p

You might also see this if you log in to MySQL using an IDE such as MySQL Workbench. Or even if you use phpMyAdmin.

What does this mean? How can you fix it?

There are a few solutions to this, which I’ve detailed below. Try one, and if it doesn’t work, try another one.

Also a tip for logging in: don’t enter your password in the command line itself, because this will be stored in your command history. Use the -p option, as mentioned above, and then you’ll be prompted to enter the password.

 

Solution 1: Sudo then Change Password

If you get the “access denied” error, one way to solve it is by using sudo to log in to mysql and change the root password.

Step 1: Open the command line on your system.

Step 2: Open mysql using the sudo command:

sudo mysql

Step 3: Enter the password for this account.

Step 4: Change the auth_plugin to mysql_native_password, and the password for the root account, in a single command:

ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'your_new_password';

Substitute the word your_new_password with a new secure password that you want to use for the root account.

The mysql_native_password method is a traditional method of authentication and will allow you to login.

Step 5: Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:

FLUSH PRIVILEGES;

Step 6: You can confirm that the new authentication method, or plugin, is used by selecting from the mysql.user table.

SELECT user, plugin
FROM mysql.user

Results:

user plugin
root mysql_native_password

Step 7: Exit the console by pressing CTRL + D or typing exit.

exit;

Step 8: Log in to mysql using the root account and the new password you set, which should work:

mysql -u root -p

You should now be logged in to the root account in mysql.

 

Solution 2: Edit My.cnf File

If the above solution did not work, you may need to edit the mysql.cnf file to allow for changes to the root account.

Step 1: Open the my.cnf file. This may be stored in:

/etc/my.cnf
/etc/mysql/my.cnf

If you’re not sure where it is, search your MySQL installation folder (e.g. on Windows or Mac) for the file.

If you don’t have a my.cnf file (MacOS does not include one by default). You can create one in the /etc folder if you like.

Step 2: Add the word skip-grant-tables under the word [mysqld]. Your file may look like this:

[mysqld]
skip-grant-tables

Step 3: Restart the MySQL server.

Step 4: Login to the root account:

mysql -u root -p

Step 5: Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:

FLUSH PRIVILEGES;

Step 6: Set a new password for the account:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Substitute the word your_new_password with a new secure password that you want to use for the root account.

Step 7: Open the my.cnf file you opened in step 1, and remove the line about skip-grant-tables, and save the file.

Step 8: Restart the MySQL server again.

Step 9: Log in to the root account again:

mysql -u root -p

You should now be able to log in successfully with your new password and not get an error.

 

Conclusion

Either of these two solutions should hopefully solve the problem for you, and you should no longer get the error “Access denied for user ‘root’@’localhost'”.

If you have any questions, feel free to use the comments section below.

Share via
Copy link
Powered by Social Snap