Access denied for user ‘root’@’localhost’

Can’t log into phpMyAdmin: mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost’

Last updated on | 84 replies

Introduction

This error may be due to one of the following reasons:

  • Due to changes in MySQL 5.7 / MySQL 8+, you cannot log into phpMyAdmin using the root account.
    See section: MySQL 5.7 / MySQL 8+
  • You are trying to log into phpMyAdmin using an account other than root but are getting an error “Access denied for user (using password: YES)”.
    See article: MySQL Rejecting Correct Password

MySQL 5.7 / MySQL 8+

In MySQL 5.7 (released Oct 2015) and MySQL 8, the root MySQL user is set to authenticate using the auth_socket or caching_sha2_password plugin rather than with mysql_native_password. This will prevent programs like phpMyAdmin from logging in with the root account.

You can either create a new MySQL superuser just for phpMyAdmin or you can try changing the authentication method for root. Personally I would recommend creating a new superuser as it’s not a good idea to allow the root account to be accessed via phpMyAdmin if accessible over the internet.

Choose from one of the two following methods:

Method 1: Create a New Superuser for phpMyAdmin

In terminal, log in to MySQL as root. You may have created a root password when you installed MySQL for the first time or the password could be blank. If you have forgotten your root password, you can always Reset the MySQL Root Password.

sudo mysql -p -u root

Now add a new MySQL user with the username of your choice. In this example we are calling it pmauser. Make sure to replace password_here with your own. You can generate a strong password here.

The command below will create a new user called pmauser (call this what you like) which can access the MySQL server from localhost with the password password_here.

CREATE USER 'pmauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password_here';

Now we will grant superuser privilege to our new user pmauser.

GRANT ALL PRIVILEGES ON *.* TO 'pmauser'@'localhost' WITH GRANT OPTION;

You should now be able to access phpMyAdmin using this new user account.

If you are getting an error for this new user “Access denied for user (using password: YES)”, please read this article.

If you are getting an error “Failed to set session cookie. Maybe you are using HTTP instead of HTTPS”, please read this article.

Method 2: Change root Authentication Method

In order to log into phpMyAdmin as your root MySQL user, you will need to switch its authentication method from auth_socket or caching_sha2_password to mysql_native_password. Note: You can try using caching_sha2_password , but it’s known to causes issues with some versions of PHP.

Open up the MySQL prompt from your terminal:

sudo mysql

If you are getting an error here “ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)”, try the command below and enter your MySQL root password.

sudo mysql -p -u root

Once logged into MySQL, run the following query.

SELECT user,plugin,host FROM mysql.user WHERE user = 'root';

Output:

+------+-------------+-----------+
| user | plugin      | host      |
+------+-------------+-----------+
| root | auth_socket | localhost |
+------+-------------+-----------+
1 row in set (0.00 sec)

Above we can see that the plugin for the root account is set to auth_socket. This may also say caching_sha2_password. You need to change this to mysql_native_password. Also, the host value should be set to localhost or %. If it’s set to anything else, you may not be able to log into phpMyAdmin with root. See: Understanding MySQL Users and Hosts

Run the following query to change the plugin value to mysql_native_password. Make sure to replace enter_password_here with your own. Click here if you need to generate a new password.

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

Flush privileges.

FLUSH PRIVILEGES;

You should now be able to log into phpMyAdmin using your root account.

MySQL 5.6 and below

In MySQL 5.6 and below, you should be able to log into to phpMyAdmin using the root account. However, if you still can’t log in, it may be due to one of the following reasons:

You could also create a new user for phpMyAdmin by following the step for MySQL 5.7 and above as the commands are the same.

Upgrading phpMyAdmin

While you’re here, you may be interested in an article I wrote on manually upgrading phpMyAdmin. Sometimes the repositories can be slow to include the latest version of phpMyAdmin, causing compatibility issues, so make sure you have the latest version:

MySQL Backups

Are you backing up your MySQL databases? I’ve written a detailed guide on how to automatically dump and archive databases using mysqldump and cron:

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

84 replies

Leave a reply

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

  2. Hi,

    I got an error when executiong this:
    ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘enter_password_here’;
    ERROR 1064 (42000): You have an error in your SQL syntax;
    Any help, please
    Regards

    Reply

  6. You need to also provide the ‘with grant option’ if you use method 1.

    GRANT ALL PRIVILEGES ON . TO ‘pmauser’@’localhost’ WITH GRANT OPTION;

    Otherwise, pmauser can not create new users and grant them options to other databases.

    Reply

  9. Ayy bro just wanted to say i was really stuck, really appreciate you. Thanks a bunch man. Much love. Crazy world we live in today. Wish people would just come together.

    Reply

  11. Thank you very much. There is many instruction over internet, but combine with this instruction, its help my frustation. Thank you very much

    Reply

  12. thank u
    you can use this to change phpmyadmin privileges
    GRANT ALL PRIVILEGES ON . TO ‘phpmyadmin’@’localhost’;

    Reply

  16. Super solution! thank you very mutch! After weeks finaly sql is ( allmost ) fixed. Now I can access myphpadmin againand I hope I can restore my old database. Thank you very mutch for sharing this solution

    Reply

  17. Method 2 didn’t work for me. So I’ve found another one that worked.
    mysql> UPDATE mysql.user SET plugin = ‘mysql_native_password’, authentication_string = PASSWORD(‘changeme’) WHERE User = ‘root’;
    mysql> FLUSH PRIVILEGES;

    Reply

    2. double quote was needed for anyone who is stuck here
      UPDATE mysql.user SET plugin = “mysql_native_password”, authentication_string = PASSWORD(“changeme”) WHERE User = “root”;

      Reply

  27. Thank you so much for this. I’m a noob and been banging my head for an hour on a NEW FREAKING installation. Your rage comig made it all that much better.

    Reply

  37. Thank you Mate!!!
    I’ve spend about 2 hours trying everything and everyforum and no one could give me a solution.

    Thanks!!!

    Reply

  55. I’ve been looking for two days for an answer. One post even suggested I completely uninstall MySQL and start over. This one worked perfectly. Apparently each version of MySQL has their own syntax. Mine never recognized the syntax from the other posts but took this one completely. I now have a password for my root as well. Thank You!

    Reply

  60. Thanks so much for the clear guidance, simple steps given and finally…… phpmyadmin running smoothly in my Raspbian Pi.

    Reply

  69. THANK U SO NUCH IT WORK !!!!!!!!!!!!!!!

    I always can login as root before , but now in Ubuntu 18.04 it fail it not work.

    Great a new user work fine 🙂

    Reply