How to Fix phpMyAdmin Login Issues with MySQL 5.7 on Ubuntu

When working with web development stacks, one of the most frustrating roadblocks can be database connectivity issues. If you’ve recently upgraded to MySQL

5.7 or higher and suddenly found yourself locked out of phpMyAdmin with the dreaded “#1698 – Access denied for user ‘root’@’localhost'” error, you’re not alone. This is a common problem that affects thousands of developers, particularly those working with Ubuntu-based systems.

Understanding the Root Cause: MySQL 5.7’s Authentication Changes

MySQL 5.7 introduced significant security changes to how root authentication works. The most important change was switching the default authentication plugin from mysql_native_password to auth_socket. This means that instead of using a password to verify your identity, MySQL now checks whether you’re logged in as the system’s root user.

While this increases security, it creates a problem for web interfaces like phpMyAdmin, which can’t use this socket-based authentication method.

Read: How to Install MySQL Workbench on Ubuntu 22.04

Why Your Previous Configuration No Longer Works

If you’ve previously used phpMyAdmin with earlier MySQL versions, you might have been able to log in as root with no password or with a simple password. This configuration no longer works because:

1. The root user now uses socket authentication (auth_socket plugin)
2. Web applications like phpMyAdmin cannot authenticate using this method
3. Even if you change the password in your configuration files, the authentication method itself is the barrier

Solution 1: Create a Dedicated phpMyAdmin User (Recommended)

The safest and most permanent solution is to create a separate database user specifically for phpMyAdmin. This approach has several advantages:

• It follows security best practices of using dedicated accounts for specific purposes
• It avoids modifying the root user’s authentication method
• It provides better security isolation

Step-by-Step Instructions:

sudo mysql --user=root mysql

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'your_secure_password';

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

sudo nano /etc/dbconfig-common/phpmyadmin.conf

# dbc_dbuser: database user
dbc_dbuser='phpmyadmin'

# dbc_dbpass: database user password
dbc_dbpass='your_secure_password'

sudo systemctl restart apache2

Now when you access phpMyAdmin, you can log in with your new ‘phpmyadmin’ user and password.

Read: How to Install MySQL 8.0 on RHEL and CentOS Stream 9

Solution 2: Change Root Authentication Method (Less Secure)

If you absolutely need root access through phpMyAdmin, you can change the authentication plugin for the root user. However, this approach is less secure and not recommended for production environments.

sudo mysql --user=root mysql

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

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

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

After this change, you should be able to log into phpMyAdmin using the root username and the password you set.

Solution 3: Reconfigure phpMyAdmin (For MariaDB Users)

If you’re using MariaDB instead of MySQL, you might prefer reconfiguring phpMyAdmin:

sudo dpkg-reconfigure phpmyadmin

During the reconfiguration process:

• Choose to reinstall the database for phpMyAdmin
• Select TCP/IP as the connection method
• Enter ‘localhost’ as the host name
• Use default port 3306
• Enter ‘phpmyadmin’ as the database name
• Set ‘root’ as the username
• Set your password
• Select your web server (usually Apache2)

Read: How to Install MariaDB on Ubuntu 22.04

Which Solution Should You Choose?

For most situations, Solution 1 (creating a dedicated phpMyAdmin user) is the best approach because it:

• Maintains the enhanced security introduced in MySQL 5.7+
• Follows the principle of least privilege
• Provides a permanent solution that won’t be affected by upgrades
• Separates system administration from database administration

Only use Solution 2 in development environments where convenience outweighs security concerns.

Troubleshooting Common Issues

Still Can’t Create Databases?

If you can log in but can’t create databases, you might need to add specific privileges:

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Error After MySQL Update

If the error returns after a MySQL update, it’s likely because updates reset the authentication plugin to the default. In this case, you’ll need to reapply your solution of choice.

Permission Issues with Configuration Files

If you encounter permission issues when editing configuration files, make sure you’re using sudo to edit them:

sudo nano /etc/phpmyadmin/config.inc.php

Understanding MySQL Authentication Plugins

To fully appreciate why this issue occurs, it helps to understand the authentication plugins:

• auth_socket: Authenticates users based on the operating system’s user credentials, which is more secure but doesn’t work with web applications
• mysql_native_password: The traditional authentication method that uses password hashing

MySQL 5.7 changed the default from mysql_native_password to auth_socket for the root user to enhance security, but this created compatibility issues with tools like phpMyAdmin.

Best Practices for MySQL Security

When setting up MySQL and phpMyAdmin, consider these security best practices:

1. Never use ‘root’ for application connections
2. Create dedicated users with limited privileges for each application
3. Use strong passwords and consider password rotation policies
4. Restrict database access to localhost when possible
5. Regularly audit user privileges to ensure they follow the principle of least privilege
6. Keep MySQL updated to benefit from security patches

FAQ About MySQL 5.7+ and phpMyAdmin Authentication

---

If you like the content, we would appreciate your support by buying us a coffee. Thank you so much for your visit and support.