【phpMyAdmin】How to resolve the issue when you cannot log in due to “Permission denied”

Preface
Modified the phpMyAdmin configuration.
Access denied for user ‘username’@‘localhost’

Preface

Although I was able to log in successfully with the MySQL command, for some reason, I couldn’t log in through phpMyAdmin due to “Permission denied.” (Of course, I used the same username and password.)

Furthermore, the mysqld.sock file is present, and its owner and permissions are properly set to 660.

# ls -l /var/run/mysqld/mysqld.sock
srwxrwxrwx 1 mysql mysql 0 Sep 13 08:35 /var/run/mysqld/mysqld.sock

As part of the troubleshooting process, I figured that since I could log in via MySQL, the issue likely stemmed from a misconfiguration in phpMyAdmin. However, it took quite some time to resolve, so I’m documenting the solution here.

Modified the phpMyAdmin configuration.

I modified the phpMyAdmin configuration file.

In my environment, the configuration file was located at the following path:
/etc/phpmyadmin/config.inc.php

I made changes to the following elements in this file:

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';

/* Server parameters */
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3306';

Specified the auth_type setting.

It was originally commented out, so I uncommented it.
This setting seems to use cookies to store authentication information and manage the session. Since this is the most common authentication method for phpMyAdmin, I specified it again.

Specified the host and port settings.

These settings ensure that phpMyAdmin connects to the intended MySQL server.
Explicitly specifying these values seems more reliable, so I set them accordingly.

After implementing the changes above and reconnecting to phpMyAdmin, a “Server Choice:” dropdown menu appeared.

By selecting “127.0.0.1:3306” from the dropdown and entering the username and password, I was finally able to log in successfully…!!

Access denied for user ‘username’@‘localhost’

It’s possible that even after these steps, you might still not be able to log in. However, in such cases, the type of error may change.

If the error “Access denied for user ‘username’@’localhost'” appears, it’s necessary to check the authentication method for the user (in this case, the root user as mentioned in the previous capture).

It seems that starting with MySQL 8.0, the auth_socket plugin is sometimes used. This plugin authenticates users via the system’s UNIX socket rather than a password. In such cases, tools like phpMyAdmin are unable to authenticate using a password, which prevents login through these external tools.

Therefore, it is necessary to configure the target user to allow password-based authentication.
I will log in via MySQL command line to check the settings.

mysql -u username -p

Then, I will check the status of the target user with the following query.

mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'username';
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| username | localhost | auth_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)

It was set to auth_socket…

I will change and update the authentication method using the following query.

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'username';
FLUSH PRIVILEGES;

After checking again with this …

mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'username';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
|username | localhost | mysql_native_password |
+------+-----------+-----------------------+

I can confirm that it has been changed to mysql_native_password.

At this point, try logging into phpMyAdmin again. (You should be able to log in successfully now.)