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

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.74 (54 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 68% using AI 🤖. Read my Ezoic review to find out how.

67 replies

avatar
  Subscribe  
newest oldest
Notify of
Lisa
Guest
Lisa
Lisa
29 days ago

THANK YOU !!!

George
Guest
George
George
1 month ago

Thanks, works perfectly.

Hai Ngo
Guest
Hai Ngo
Hai Ngo
2 months ago

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

Rob Knol
Guest
Rob Knol
Rob Knol
2 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
Guest
Roman
Roman
2 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;

LXDevs
Guest
LXDevs
LXDevs
3 months ago
TYSM BROH
aza
Guest
aza
aza
4 months ago

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

dssi
Guest
dssi
dssi
4 months ago

Merci

seki
Guest
seki
seki
4 months ago

thx bro i love you greetings from Turkey

Aminudin Anwar
Guest
Aminudin Anwar
Aminudin Anwar
4 months ago

Great awesome..thx u so much

Daniel CCM
Guest
Daniel CCM
Daniel CCM
4 months ago

Thanks so much!!!

Ramesh
Guest
Ramesh
Ramesh
5 months ago

This document is very helpful for me.

Thanks

Faycha Anastasya
Guest
Faycha Anastasya
Faycha Anastasya
5 months ago

Thank you, work for me on ubuntu 18.04 🙂

Evgeniy
Guest
Evgeniy
Evgeniy
5 months ago

article did not help!
ubuntu 18.04 + mySQL 5.7.29

Dario
Guest
Dario
Dario
5 months 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
Guest
blue
blue
8 months ago

sudo thanks

stew
Guest
stew
stew
9 months ago

works like a charm thanks!

davidw
Guest
davidw
davidw
9 months ago

Merci 🙂

adarka
Guest
adarka
adarka
9 months ago

TOP, Thank you

Prince
Guest
Prince
Prince
10 months ago

thank you you really solved my problem

Krimo
Guest
Krimo
Krimo
10 months ago

Thank you it’s help me 🙂

rixton
Guest
rixton
rixton
11 months ago

Worked!!! Thank you very much!

Nevil Chitiyo
Guest
Nevil Chitiyo
Nevil Chitiyo
9 months ago

Thank you!

Muhsin
Guest
Muhsin
Muhsin
11 months ago

Thanks a lot dude. 😀

GILBERTO JOSE SILVA
Guest
GILBERTO JOSE SILVA
GILBERTO JOSE SILVA
1 year ago

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

jose
Guest
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
Guest
art-pl
art-pl
1 year ago

super, Thank you – Dzięki bardzo 🙂

Llf0703
Guest
Llf0703
Llf0703
1 year ago

Thank you

Alexandre Labre
Guest
Alexandre Labre
Alexandre Labre
1 year ago

thank youuu <3 <3 <3

ibk
Guest
ibk
ibk
1 year ago

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

long haohong
Guest
long haohong
long haohong
1 year ago

thanks very much

swi innocent
Guest
swi innocent
swi innocent
1 year ago

thanks works great

Niels
Guest
Niels
Niels
1 year ago

Thanks so much!

pcerqueira
Guest
pcerqueira
pcerqueira
1 year ago

Works well with phpmyadmin and mariadb ! Great, thanks !

yogesh
Guest
yogesh
yogesh
1 year ago

It works perfectly thanks

Jose
Guest
Jose
Jose
1 year ago

Thank you very much! <3

Ady
Guest
Ady
Ady
1 year ago

thanks. it’s very helpfull!

mobin rajaei
Guest
mobin rajaei
mobin rajaei
1 year ago

wow, thx dude, best help in the whole internet

Gabriel Silveira
Guest
Gabriel Silveira
Gabriel Silveira
1 year ago

It worked! Thank you so much.

Abdullah
Guest
Abdullah
Abdullah
1 year ago

IT worked. Thank you

mohammed
Guest
mohammed
mohammed
1 year ago

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

No problem
Guest
No problem
No problem
1 year ago

Thanks guy… well done!

Jesus
Guest
Jesus
Jesus
1 year ago

muchas gracias, me solucionó el problema

Lonnie
Guest
Lonnie
Lonnie
1 year 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!

sanjeev
Guest
sanjeev
sanjeev
1 year ago

Thanks 🙂 You saved me
Keep It up

JAM
Guest
JAM
JAM
2 years ago

You rock you saved my day !

Sam
Guest
Sam
Sam
2 years ago

Awesome information. Saved me a lot of time as a beginner. Thank you so much.

Ane
Guest
Ane
Ane
2 years ago

I can’t believe! I spend all day working on this. Thank you!

Hardy HP
Guest
Hardy HP
Hardy HP
2 years ago

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

Mark
Guest
Mark
Mark
2 years ago

Many many thanks.

Times
Guest
Times
Times
2 years ago

Yesssssss, so clean, so easy, works beautifully.