Need help?

Our experts have had an average response time of 13.14 minutes in February 2024 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

Access denied for user ‘root’@’localhost’ (using password yes) MySQL – Fixed

by | Feb 4, 2020

Oops!! Frustrated with ‘Access denied for user ‘root’@’localhost’ (using password yes) in MySQL?

This error may occur due to many reasons which include insufficient privileges for the user to access the database, when MySQL root password is wrong, and so on.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

Why do access denied errors occur in MySQL?

This error may occur due to different reasons in different scenarios. Some of them are:

  • When a user who doesn’t exist on the MySQL server tries to access the database.
  • When there is no privilege for the user.
  • If the username or password is wrong.

Today, we’ll discuss in detail how we fix this Access denied for user ‘root’@’localhost’ (using password yes) in MySQL.

 

How do we fix ‘access denied for user ‘root’@’localhost’ (using password yes) in MySQL’?

Let’s see, how we fix this access denied error for our customers in detail.

 

Solution 1: User is not able to log in

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

Access denied for user 'root'@'localhost' (using password yes) MySQL

 

So, our Support Engineers checked in detail and found that the root password was wrong. So, to reset the password we did the following:

  1. Initially, we opened /etc/mysql/my.cnf
  2. Then we added skip-grant-tables under [mysqld] section. Here we skipped grant tables as there is no other way to log in.

    By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

    Note: skip-grant-tables is dangerous, so we will remove this at the end.

  3. Then, we restarted the MySQL service using the command below:
    service mysql restart

    Now MySQL is configured to skip the grant-tables. So, we can log in now.

  4. We logged into MySQL using:
    mysql -u root 
  5. Then, we flushed the privileges using:

    Access denied for user 'root'@'localhost' (using password yes) MySQL

  6. After that, we set a new password using the command below: UPDATE user SET
    Password=PASSWORD('my_password') where USER='root';
    FLUSH PRIVILEGES;
  7. Then, we removed skip-grant-tables from /etc/mysql/my.cnf
  8. Finally, we restarted the MySQL service and logged in to MySQL using the new password.This fixes the error.

Solution 2: User does not have global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege as seen here:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

This fixes the error.

Solution 3: Change the Password

Another way to resolve the error is by changing the root password:

  1. First, open the command line and open MySQL with the sudo command:

    sudo mysql

  2. Then, we have to enter the account password.
  3. Now, it is time to change the auth_plugin to mysql_native_password as well as root account password as seen here:

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

    Here, we have to replace new_password with a new secure password.

    Furthermore, the mysql_native_password method is a traditional authentication method that lets you log in.

  4. Then, flush the privileges. This will tell the server to refresh the grant tables and apply the changes:

    FLUSH PRIVILEGES;

  5. We can exit the console.
  6. Finally, try logging in to mysql via the root account and the new password we set.

[Need more assistance to fix MySQL Error?- We’re available 24/7.]

Conclusion

In short, this error occurs mainly due to insufficient privileges to the user and can be fixed by granting the privilege. Also, today we discussed in detail how our Support Engineers fix this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

10 Comments

  1. Allan

    I am on step 2 and I don’t know where to put skip-grant-tables on my.cnf. I see it says under the [mysqld] section but where is that part? I tried putting skip-grant-tables on the bottom of the page but I just got back a this error message:
    mysql: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 22!
    mysql: [ERROR] Fatal error in defaults handling. Program aborted!

    Kinda lost.

    Reply
    • Sijin George

      Hello Allan,
      Please create a [mysqld] section first and then add skip-grant-tables under it. If you still have the error and need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

      Reply
      • Essiet Aniekan

        where should i create the mysql section pls. Thanks

        Reply
        • Sijin George

          Hello Essiet,
          Please add the mysqld section in the mysql conf file. Usually, it will be at /etc/my.cnf

          Reply
    • Rolly molly

      I create a user gave all privileges logged in as the created user but still it says access denied to user@localhost. Nothing was wrong nor the password. What is the problem?

      Reply
      • Maheen Aboobakkar

        Hello Rolly,

        We will need to look into the issue in detail. We’ll be happy to talk to you on chat (click on the icon at right-bottom).

        Reply
  2. triplek

    Your article helped me solve my problem. Thank you.

    Reply
    • Hiba Razak

      Hi,
      Glad to know that our article helps you solves the issue 🙂 .

      Reply
  3. Miguel Guzman

    Tu artículo me ayudó a resolver mi problema. Gracias.

    Reply
    • Hiba Razak

      Hi Miguel,
      Thanks for the feedback.Glad to know that our article helps you solves the problem.

      Reply

Submit a Comment

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

Categories

Tags

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF