How to Fix phpMyAdmin Login Issues with MySQL 5.7 on Ubuntu

If you’ve recently upgraded to Ubuntu 16.04 or newer with MySQL 5.7+, you might have encountered an unexpected authentication error when trying to access phpMyAdmin with your root credentials.

The familiar error message #1698 - Access denied for user 'root'@'localhost' can be frustrating, especially when you’re certain your credentials are correct.

I experienced this firsthand after upgrading my development environment from Ubuntu 15.10 to 16.04. What seemed like a routine upgrade quickly turned into a troubleshooting session as my previously working phpMyAdmin setup suddenly refused to accept my root login.

In this guide, I’ll walk you through why this happens and provide multiple solutions to fix the authentication problem, ensuring you can regain access to your databases through phpMyAdmin.

Understanding the Problem

What Changed in MySQL 5.7?

MySQL 5.7 introduced a significant security model change that affects how the root user authenticates. In previous versions, you could simply use a username and password combination. However, in MySQL 5.7 and later:

• The root user now uses the auth_socket plugin by default
• This plugin requires sudo privileges for root authentication
• phpMyAdmin cannot use sudo, so it cannot authenticate as root

This security enhancement improves your server’s protection but creates complications for tools like phpMyAdmin that don’t have sudo capabilities.

Read: How to Install MySQL Workbench on Ubuntu 22.04

Solution 1: Create a Dedicated phpMyAdmin User (Recommended)

The safest and most sustainable solution is to create a dedicated user for phpMyAdmin with appropriate privileges. This approach:

• Works across MySQL updates
• Follows security best practices
• Provides a permanent solution

Step-by-Step Instructions

1. Connect to MySQL using sudo

   sudo mysql --user=root mysql
   

2. Create a new user for phpMyAdmin

   CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'your_strong_password';
   GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
   FLUSH PRIVILEGES;
   

   Security Note: Replace ‘your_strong_password’ with a secure password. Never use the example passwords from tutorials in production environments!

3. Update phpMyAdmin configurationEdit the phpMyAdmin configuration file using sudo:

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

   Update the user and password sections:

   # dbc_dbuser: database user
   dbc_dbuser='phpmyadmin'
   
   # dbc_dbpass: database user password
   dbc_dbpass='your_strong_password'
   

4. Restart services

   sudo service apache2 restart
   sudo service mysql restart
   

5. Access phpMyAdminNavigate to http://localhost/phpmyadmin and log in with your new credentials:
   • Username: phpmyadmin
   • Password: your_strong_password

Read: How to change MySQL root password 

Solution 2: Change MySQL Root Authentication Method

If you prefer to continue using the root user with phpMyAdmin (generally not recommended for production servers), you can change the authentication plugin:

1. Check current authentication method

   sudo mysql --user=root
   

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

   You’ll likely see auth_socket as the plugin for root.

2. Change authentication method to password-based

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

3. Try logging into phpMyAdminNow you should be able to log in with:
   • Username: root
   • Password: your_root_password

   Warning: This change will need to be reapplied after MySQL updates, as they often reset the authentication method.

Solution 3: Reconfigure phpMyAdmin

If you’re still having issues, reconfiguring phpMyAdmin might help:

sudo dpkg-reconfigure phpmyadmin

Follow the interactive prompts:

• Choose to reinstall database for phpMyAdmin: Yes
• Select connection method: TCP/IP
• Enter hostname: localhost
• Port number: 3306
• MySQL database name: phpmyadmin
• Username: root (or your preferred username)
• Password: your_password
• Administrative user: root
• Web server to reconfigure: apache2

Troubleshooting Common Issues

Cannot Create New Databases

If you can log in but cannot create new databases, you might need additional privileges:

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

Authentication Issues After MySQL Update

MySQL updates may reset your authentication settings. After updates, you might need to:

1. Check authentication method again
2. Reapply the appropriate solution
3. Restart services

Best Practices for MySQL and phpMyAdmin Security

1. Avoid using the root account for routine database operations
2. Create specific users with the minimum necessary privileges for each application
3. Use strong passwords and change them regularly
4. Restrict remote access to MySQL unless specifically needed
5. Keep MySQL and phpMyAdmin updated to the latest security patches

Conclusion

The authentication changes in MySQL 5.7+ enhance security but require adjustments to how you interact with phpMyAdmin. By creating a dedicated phpMyAdmin user or modifying the authentication method, you can regain access to your database management interface while maintaining appropriate security levels.

Remember that the most secure approach is to create dedicated database users with appropriate privileges rather than using the root account for everyday tasks. This practice not only improves security but also provides better audit trails and makes your system more resilient to updates.

FAQ

Why did this change in MySQL 5.7?

The change to auth_socket plugin for root authentication was implemented as a security enhancement to prevent brute force attacks against the root user and to ensure that only local system administrators with sudo privileges could access the root MySQL account.

Will my changes persist after a system update?

Changes to the authentication method for the root user may be reset during MySQL updates. The dedicated user approach (Solution 1) is more likely to persist across updates.

Can I use these solutions for MariaDB too?

Yes, most of these solutions also work for MariaDB, as it follows similar authentication principles. The specific commands may vary slightly depending on the MariaDB version.

Is it safe to grant ALL PRIVILEGES to my phpMyAdmin user?

For development environments, it’s generally acceptable. For production servers, you should consider granting only the specific privileges needed for your applications.

How can I verify my MySQL authentication settings?

Run the following command to check user authentication methods:

SELECT User, Host, plugin FROM mysql.user;

What if I forget my phpMyAdmin password?

You can reset it by connecting to MySQL as root and running:

ALTER USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

The post How to Fix phpMyAdmin Login Issues with MySQL 5.7 on Ubuntu appeared first on net2.