How Can I Resolve Access Denied Error in MariaDB/MySQL in my phpMyAdmin?

Recently, while working on the final preparations for our application defense, I encountered an unexpected error in phpMyAdmin which denied me access to MariaDB. The error popped up without any recent changes to our codebase, which was perplexing. Here’s how I approached solving this issue, aiming to help anyone facing a similar situation.

Understanding the Error

The error in question typically reads something like this:

#1045 - Access denied for user 'username'@'localhost' (using password: YES)

This error signifies that the login credentials used to access the MariaDB server through phpMyAdmin are incorrect. It can either mean the username or the password, or both are wrong, or the user might not have access from the specified host.

Troubleshooting Steps

1. Check Connection Credentials: The first step is to ensure that the username and password are indeed correct. These are usually stored in your configuration settings, which for phpMyAdmin is located in config.inc.php. Here’s what the relevant part of this file typically looks like:

$cfg['Servers'][$i]['host'] = 'localhost';
    $cfg['Servers'][$i]['user'] = 'root';
    $cfg['Servers'][$i]['password'] = 'yourpassword';
    $cfg['Servers'][$i]['auth_type'] = 'config';

Ensure that the username and password specified are correct. You can also try changing 'auth_type' from 'config' to 'cookie', which will prompt you to enter your credentials on login to phpMyAdmin.

1. Check User Privileges in MariaDB: If the credentials are correct but you still face issues, check if the database user has appropriate privileges set in MariaDB. Connect to MariaDB using the command-line interface with a root or administrative user and check user privileges by running:

SHOW GRANTS FOR 'username'@'localhost';

Replace 'username' and 'localhost' with the specific user and host. The output should give you insights into whether your user has sufficient rights.

1. Reset or Change User Password: If you suspect the credentials might be wrong or have forgotten the password, reset it. This can be done from the MariaDB shell:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');
    FLUSH PRIVILEGES;

Again, replace 'username', 'localhost', and 'newpassword' with appropriate values.

1. Check Host from Where User Can Connect: Sometimes, your user might be restricted to connect from a different host. If you’ve moved your environment or are connecting from a different local setup, this might be the issue. Check and possibly update the host:

UPDATE mysql.user SET Host='newhost' WHERE User='username' AND Host='oldhost';
    FLUSH PRIVILEGES;

1. Restarting Services: After making changes in the configuration or the database, make sure to restart the Apache/Nginx and MariaDB/MySQL services to apply changes:

sudo systemctl restart apache2
    sudo systemctl restart mysql

Or replace apache2 and mysql with whatever service you are using.

Summary

By carefully checking the configuration files, verifying the user privileges, and ensuring correct host settings in MariaDB, this daunting ‘Access Denied’ error can often be resolved. The steps outlined aim to provide a methodical approach to diagnosing and solving common database connection issues in development environments.