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

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';

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.

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:

1 Star2 Stars3 Stars4 Stars5 Stars 4.76 (58 votes)

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

p.s. I increased my AdSense revenue by 200% using AI 🤖. Read my Ezoic review to find out how.

Subscribe
Notify of
guest
77 Comments
newest
oldest
Inline Feedbacks
View all comments
Jose
Jose
18 days ago

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.

Cha Cha
Cha Cha
2 months ago

Thank you so much. You save my world. You are genius. (^_^)

RUDAN
RUDAN
5 months ago

RODADNDO AQUI PERFEITAMENTE NO LINUX MINT 20

Eric Schieber Jr.
Eric Schieber Jr.
6 months ago

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.

Adauto
Adauto
6 months ago

Thank you, work for me on ubuntu 20.10

apriyanto
apriyanto
6 months ago

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

no one
no one
7 months ago

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

Lisa
Lisa
8 months ago

THANK YOU !!!

George
George
9 months ago

Thanks, works perfectly.

Hai Ngo
Hai Ngo
9 months ago

Thanks a lot. Your instructions help me go through the problem solving.

Rob Knol
Rob Knol
9 months ago

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

Roman
Roman
10 months ago

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;

Subhodeep Bhattacharjee
Subhodeep Bhattacharjee
7 months ago
Reply to  Roman

Thanks It’s working now…. Thanks a lot.. 🙂

themerc
themerc
5 months ago
Reply to  Roman

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”;

LXDevs
LXDevs
11 months ago
TYSM BROH
aza
aza
11 months ago

Thank you! You are generated really good and valueable content and helped me a lot.

dssi
dssi
11 months ago

Merci

adam
adam
7 months ago
Reply to  dssi

bonjour je suis désolé de te deranger mais jai beau chercher et tout essayer sa ne marche toujour pas..

seki
seki
11 months ago

thx bro i love you greetings from Turkey

Aminudin Anwar
Aminudin Anwar
11 months ago

Great awesome..thx u so much

Daniel CCM
Daniel CCM
1 year ago

Thanks so much!!!

Ramesh
Ramesh
1 year ago

This document is very helpful for me.

Thanks

Faycha Anastasya
Faycha Anastasya
1 year ago

Thank you, work for me on ubuntu 18.04 🙂

Evgeniy
Evgeniy
1 year ago

article did not help!
ubuntu 18.04 + mySQL 5.7.29

Dario
Dario
1 year ago

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.

blue
blue
1 year ago

sudo thanks

stew
stew
1 year ago

works like a charm thanks!

davidw
davidw
1 year ago

Merci 🙂

adarka
adarka
1 year ago

TOP, Thank you

Prince
Prince
1 year ago

thank you you really solved my problem

Krimo
Krimo
1 year ago

Thank you it’s help me 🙂

rixton
rixton
1 year ago

Worked!!! Thank you very much!

Nevil Chitiyo
Nevil Chitiyo
1 year ago
Reply to  rixton

Thank you!

Muhsin
Muhsin
1 year ago

Thanks a lot dude. 😀

GILBERTO JOSE SILVA
GILBERTO JOSE SILVA
1 year ago

Muito obrigado!
Resolveu de vez o meu problema no MySQL 5.7.

jose
jose
1 year ago

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

Thanks!!!

art-pl
art-pl
1 year ago

super, Thank you – Dzięki bardzo 🙂

Llf0703
Llf0703
1 year ago

Thank you

Alexandre Labre
Alexandre Labre
1 year ago

thank youuu <3 <3 <3

ibk
ibk
1 year ago

Yep, like a charm 🙂
Thank you very much, you fine sir!

long haohong
long haohong
2 years ago

thanks very much

swi innocent
swi innocent
2 years ago

thanks works great

Niels
Niels
2 years ago

Thanks so much!

pcerqueira
pcerqueira
2 years ago

Works well with phpmyadmin and mariadb ! Great, thanks !

yogesh
yogesh
2 years ago

It works perfectly thanks

Jose
Jose
2 years ago

Thank you very much! <3

Ady
Ady
2 years ago

thanks. it’s very helpfull!

mobin rajaei
mobin rajaei
2 years ago

wow, thx dude, best help in the whole internet

Gabriel Silveira
Gabriel Silveira
2 years ago

It worked! Thank you so much.

Abdullah
Abdullah
2 years ago

IT worked. Thank you

mohammed
mohammed
2 years ago

Hi dear
thanks a lot for your advice.
Happy new year

No problem
No problem
2 years ago

Thanks guy… well done!

Jesus
Jesus
2 years ago

muchas gracias, me solucionó el problema

Lonnie
Lonnie
2 years ago

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!