FB

How to Fix MySQL Error: Access Denied for User ‘root’@’localhost’

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.

22 thoughts on “How to Fix MySQL Error: Access Denied for User ‘root’@’localhost’”

  1. thank you Ben but i don’t resolve my issue because i work on windows so some commands like sudo don’t accept.
    if you have another solutions for me.

  2. Thanks it solved the issue.

    After adding skip-grant-tables , at time of login still promt for password , just press enter.
    After login execute
    FLUSH PRIVILEGES;
    ALTERUSER’root’@’localhost’ IDENTIFIED BY ‘your_new_password’;
    //After executing above command I got error ,
    ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement

    But after that I removed skip-grant-tables from my.cnf and re-logged in and it worked with new password.

  3. Hi, thanks for the well written help file, but newbe problem may exist???

    In my case, I am able to get into mysql CML with a root password (sudo mysql -u root -p) , but I am not able to log into myPhpAdmin or mysql_workbench as root.

    I am able to log into both GUI apps using my (not-root) user password.

    I can only add and remove databases while using CML as root.

    I am able to add/remove tables as non-root user in both GUIs.

    Any thoughts?

    Thanks,
    John

  4. Enter it in the command line. It’ll ask for a password after. All you gotta do is enter a new password for it and you’re all set! It worked for me.

    mysql -u root -p

  5. hitakshij@hitakshis-Air ~ % sudo mysql -protocol=tcp –host=ip –user=root –port=3306 –default-character-set=utf8 –comments –database=eeprod < "/Users/hitakshij/Dump20230406.sql"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'root'@ip' (using password: YES)

    can someone please help… while importing my sql file in workbench (am using through commandline) am tired of ir .

  6. mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Password@1234’;
    Query OK, 0 rows affected (0.00 sec)

    Am stuck here

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. .