MySQL Error 1698 (28000): Access Denied for User 'root'@'localhost' - Complete Solution Guide

Introduction

Have you ever encountered the frustrating MySQL Error 1698 (28000) after installing MySQL on your server or desktop? This error typically appears when attempting to connect to MySQL as the root user:

user@server:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

This error occurs when a regular user attempts to access MySQL with the root account but fails, while using sudo allows successful connection. The root cause lies in MySQL's authentication mechanism, specifically the use of the auth_socket plugin instead of traditional password authentication.

Understanding MySQL Error 1698 (28000)

What Causes This Error?

MySQL Error 1698 is fundamentally an authentication issue that stems from how modern MySQL and MariaDB installations handle user authentication. In recent versions, the default authentication method for the root user has shifted from password-based authentication (mysql_native_password) to socket-based authentication (auth_socket or unix_socket).

Authentication Plugin Differences

1. auth_socket Plugin:

• Authenticates users based on the operating system user credentials
• Checks if the system username matches the MySQL username
• Requires no password but restricts access to local connections only
• Provides enhanced security by leveraging OS-level authentication

2. mysql_native_password Plugin:

• Traditional password-based authentication using SHA-1 hashing
• Allows remote connections with proper credentials
• Compatible with older MySQL versions and legacy applications
• Less secure than modern alternatives but more flexible

3. caching_sha2_password Plugin:

• Default authentication plugin in MySQL 8.0+
• Uses SHA-256 hashing for enhanced security
• Supports password caching for improved performance
• Requires secure connections for initial authentication

Why This Error Occurs

When MySQL is installed on Ubuntu and other Linux distributions, the root user is often configured with the auth_socket plugin by default. This means:

1. The system checks if your OS username matches the MySQL username
2. If you're logged in as a regular user (e.g., john) but trying to connect as MySQL root, authentication fails
3. Using sudo works because it temporarily elevates your privileges to match the root user

Comprehensive Solutions for MySQL Error 1698

Solution 1: Switch to Password Authentication (Recommended)

This is the most straightforward solution that maintains security while providing flexibility:

Step 1: Access MySQL as a privileged user

sudo mysql -u root

Step 2: Change the authentication method to mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_secure_password';

Step 3: Apply the changes

FLUSH PRIVILEGES;

Step 4: Exit and test the connection

EXIT;

mysql -u root -p

Solution 2: Create a New Administrative User

Instead of modifying the root user, create a new user with administrative privileges:

Step 1: Access MySQL with sudo

sudo mysql -u root

Step 2: Create a new user with password authentication

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'secure_password';

Step 3: Grant administrative privileges

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

Step 4: Apply changes

FLUSH PRIVILEGES;
EXIT;

Step 5: Test the new user

mysql -u admin -p

Solution 3: Add System User to MySQL (Advanced)

This solution leverages the auth_socket plugin while allowing your system user to access MySQL:

Step 1: Access MySQL as root

sudo mysql -u root

Step 2: Create a MySQL user matching your system username

CREATE USER 'your_system_username'@'localhost' IDENTIFIED WITH auth_socket;

Step 3: Grant necessary privileges

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

Step 4: Apply changes

FLUSH PRIVILEGES;
EXIT;

Step 5: Connect using your system username

mysql -u your_system_username

Solution 4: Hybrid Authentication (Most Secure)

Configure the root user to support both socket and password authentication:

Step 1: Access MySQL as root

sudo mysql -u root

Step 2: Configure hybrid authentication

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket OR mysql_native_password USING PASSWORD('your_password');

Step 3: Apply changes

FLUSH PRIVILEGES;
EXIT;

This allows both sudo mysql -u root and mysql -u root -p to work.

Verification and Troubleshooting

Check Current Authentication Method

To verify which authentication plugin is being used:

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

Verify Plugin Status

Check if authentication plugins are loaded:

SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE '%socket%' OR PLUGIN_NAME LIKE '%password%';

Common Issues and Solutions

Issue 1: "Plugin 'auth_socket' is not loaded"

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

Issue 2: Cannot connect remotely after changing authentication

• Ensure the user is created with appropriate host permissions
• Check firewall settings and MySQL bind-address configuration

Issue 3: Password authentication not working

• Verify the password meets MySQL's validation requirements
• Check if password validation plugin is enabled

Security Considerations

Best Practices

1. Use Strong Passwords: When switching to password authentication, ensure passwords meet security standards
2. Limit Root Access: Consider using dedicated administrative users instead of root for daily operations
3. Regular Audits: Periodically review user accounts and their authentication methods
4. Network Security: Restrict MySQL access to necessary hosts only
5. Backup Authentication: Maintain multiple ways to access MySQL in case of authentication issues

Security Implications of Each Solution

• Password Authentication: More flexible but requires strong password management
• Socket Authentication: More secure for local access but limits remote administration
• Hybrid Authentication: Provides flexibility while maintaining security options

Prevention and Maintenance

Regular Maintenance Tasks

1. Monitor Authentication Logs: Check MySQL error logs for authentication failures
2. Update Passwords Regularly: Implement password rotation policies
3. Review User Accounts: Regularly audit MySQL user accounts and permissions
4. Test Backup Access: Ensure you have alternative ways to access MySQL

Documentation

Always document your authentication configuration for future reference and team members.

Conclusion

MySQL Error 1698 (28000) is a common authentication issue that occurs due to the shift from password-based to socket-based authentication in modern MySQL installations. Understanding the underlying authentication mechanisms is crucial for implementing the most appropriate solution for your environment.

The recommended approach is to switch to password authentication for the root user while maintaining strong security practices. However, the choice of solution depends on your specific security requirements, infrastructure setup, and operational needs.

By implementing proper authentication methods and following security best practices, you can resolve this error while maintaining a secure and manageable MySQL installation. Remember to always test your changes in a development environment before applying them to production systems.