In-depth Analysis and Solutions for MySQL ERROR 1698 (28000): Access Denied for User 'root'@'localhost'

Problem Background and Error Analysis

When installing MySQL database on Ubuntu and other Linux systems, users frequently encounter the ERROR 1698 (28000): Access denied for user 'root'@'localhost' message. This error typically occurs when attempting to log into MySQL as the root user, even when the mysql.user table clearly shows that the root user has localhost access privileges.

The fundamental cause lies in MySQL's default use of the auth_socket authentication plugin in Ubuntu systems. This authentication mechanism doesn't rely on traditional password verification but instead authenticates through system user identity. This means only when running as the system root user can one successfully connect to MySQL's root account.

Authentication Plugin Mechanism Analysis

MySQL supports multiple authentication plugins, with auth_socket specifically designed for Unix/Linux systems. Its working principle involves verifying whether the owner of the connection process matches the database user. When using the auth_socket plugin, MySQL checks the UID of the connection process and allows the connection if this UID matches the system user's UID.

To view the current user's authentication plugin configuration, use the following SQL query:

sudo mysql -u root
USE mysql;
SELECT User, Host, plugin FROM mysql.user;

In a typical Ubuntu MySQL installation, the query results might show:

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

Solution One: Modify Root User Authentication Method

The first solution involves changing the root user's authentication plugin from auth_socket to mysql_native_password. This approach allows the root user to log in using traditional password authentication.

Specific operational steps:

# Connect to MySQL as system root
sudo mysql -u root

# Switch to mysql database
USE mysql;

# Update root user's authentication plugin
UPDATE user SET plugin='mysql_native_password' WHERE User='root';

# Refresh privileges
FLUSH PRIVILEGES;

# Exit MySQL
EXIT;

# Restart MySQL service
sudo service mysql restart

After completing these steps, the root user can authenticate using passwords. If password setup is needed, use the ALTER USER statement:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Solution Two: Create New System User

The second solution involves creating a new database user and associating it with the current system user. This method is more secure as it follows the principle of least privilege.

Operational steps:

# Connect to MySQL as system root
sudo mysql -u root

# Switch to mysql database
USE mysql;

# Create new user and set password
CREATE USER 'your_system_user'@'localhost' IDENTIFIED BY 'your_password';

# Grant all privileges
GRANT ALL PRIVILEGES ON *.* TO 'your_system_user'@'localhost';

# Set authentication plugin to auth_socket
UPDATE user SET plugin='auth_socket' WHERE User='your_system_user';

# Refresh privileges
FLUSH PRIVILEGES;

# Exit MySQL
EXIT;

# Restart MySQL service
sudo service mysql restart

After creation, connect to MySQL using the new username:

mysql -u your_system_user

Differential Handling Across MySQL Versions

Authentication plugin names and behaviors may differ across MySQL versions:

In Debian 9 (Stretch) and similar systems, the auth_socket plugin is called unix_socket. The corresponding update statement should be:

UPDATE user SET plugin='unix_socket' WHERE User='your_system_user';

In MySQL 8.0.4 and later versions, the default authentication plugin changes to caching_sha2_password. In this case, no additional plugin update steps are needed when creating new users:

CREATE USER 'your_system_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_system_user'@'localhost';
FLUSH PRIVILEGES;

Newly created users will automatically use the caching_sha2_password plugin and can connect via:

mysql -u your_system_user -p

Practical Application Scenario Analysis

In automated deployment and configuration management scenarios, ERROR 1698 frequently appears when using tools like Ansible and Vagrant. For example, when configuring MySQL with Ansible, if one directly attempts to update the root password while ignoring authentication plugin issues, configuration failure occurs.

The correct handling process should be:

# First ensure ability to connect to MySQL as system root
# Then execute authentication plugin updates
# Finally proceed with password setup and other configurations

This issue is particularly common during installations of mail servers like iRedMail. The iRedMail installer attempts to connect to MySQL for database initialization, and improper root user authentication configuration leads to installation failure.

Best Practice Recommendations

Based on analysis of multiple real-world cases, we recommend the following best practices:

1. In production environments, prioritize creating dedicated database users over directly using root accounts

2. In automation scripts, always check and handle authentication plugin configuration first

3. For newly installed MySQL, immediately configure appropriate authentication methods

4. Regularly review user and privilege configurations in the mysql.user table

By understanding MySQL's authentication mechanisms and properly configuring authentication plugins, ERROR 1698 errors can be effectively avoided, ensuring database security and stable operation.