Can’t log into phpMyAdmin: mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost’
Last updated on April 26th, 2020
Introduction
This error may be due to one of the following reasons:
- Since MySQL 5.7+, you can no longer log into phpMyAdmin using the root account.
See section: MySQL 5.7 below - You have forgotten your root password.
See: How To Reset the MySQL Root Password - The
host
value for root is preventing access via phpMyAdmin.
See: Understanding MySQL Users and Hosts - 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: MySQL Rejecting Correct Password - If using MySQL 5.6 and below
See section: MySQL 5.6 and below
MySQL 5.7 and above
In MySQL 5.7 (released Oct 2015), the root MySQL user is set to authenticate using the auth_socket
plugin rather than with a 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.
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 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.
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
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
. 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