How to Solve MySQL Error 1698: A Detailed Guide

Have you tried to access the MySQL database as the root user but bumped into the error message MySQL error 1698 (28000): Access denied for user ‘root’@’localhost’? This is a common issue for people working with MySQL. It prevents access to the database through various means, including terminal, PHPMyAdmin, or MySQL client interfaces. More common than with any other operating system(OS), this error pops up for Ubuntu system users. 

In this detailed guide, we will discuss the main causes of MySQL error 1698 and provide effective solutions to address and resolve this error. So, let’s get started on resolving this problem after which you will regain quick access to your MySQL database.

What is MySQL error 1698 and the reasons behind it?

Behind the MySQL error 1698 is the problem of how MySQL checks if someone trying to use it as the ‘root’ user is allowed to do so. With some operating systems, MySQL does not use a regular password for the ‘root’ user. The MySQL root account checks it through the auth_socket or unix_socket plugin. This plugin operates based on operating system credentials rather than a password, checking if the user connection request comes through a UNIX socket and then validating the username against the system’s credentials.

Basically, if you’re not logged into your OS as the ‘root’ user, MySQL won’t let you access it as the ‘root’ user. This method is supposed to make things more secure and easier, but in that process, the error 1698 may arise.

This error pops up because there’s a conflict between the security measures and user expectations. Understanding the reason behind the error is the first step in fixing error 1698 and finding ways to keep the database both safe and easy to use.

> MySQL Error 1698 (28000)

Learning more about MySQL error 1698, we discover that its main cause isn’t the traditional concept of passwords but a unique authentication method. This method involves the auth_socket or unix_socket plugin, that we touched upon above.

Essentially, this plugin doesn’t bother with passwords at all. Instead, it verifies if the connection is made via a UNIX socket and then checks if the username trying to connect matches the one logged into the system.

This approach to authentication is a shift from password-based security. It shows how the plugin works to let people in based on the user information from the computer system.

How to solve MySQL error 1698?

Let’s discuss solutions that will work against MySQL error 1698 and ensure effective database interactions in the future.

Switch the user to mysql_native_password plugin

Switching to the `mysql_native_password` plugin is a simple fix. This approach means updating the authentication plugin for the root user to `mysql_native_password`. You should modify the root user’s plugin setting and then apply these changes by flushing the MySQL privileges. This method restores access as an immediate resolution to the access denial issue.

1. Access the MySQL shell as a privileged user.
2. Use this command to switch the root user’s authentication method to `mysql_native_password`:

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

3. Flush privileges to apply the changes:

FLUSH PRIVILEGES;

4. Exit the MySQL shell and try to log in as the root user again.

Add system user to MySQL user table

Adding a system user to the MySQL user table and enabling the `auth_socket` plugin enhances security. This method leverages OS-level authentication, permitting database access in a manner that’s more secure than traditional password authentication. This approach minimizes the risk associated with using the root account directly for database operations.

1. Log into the MySQL server as the root user or another user with sufficient privileges.
2. Create a new user and set the authentication to `auth_socket`:

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

3. Grant the necessary privileges to the new user:

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

4. Flush privileges to apply the changes:

FLUSH PRIVILEGES;

5. Log into MySQL with the new system user, using OS credentials for authentication.

Disable unix_socket authentication

If you want a quick workaround, disabling UNIX socket authentication can provide an immediate, though less secure, solution. This method allows the root user to log in using standard password protocols by removing the UNIX socket plugin from the root user’s authentication method. While this resolves the access issue, you should consider the potential security trade-offs involved.

1. Open the MySQL console.
2. Update the root user to disable UNIX socket authentication:

UPDATE mysql.user SET plugin='' WHERE User='root';

3. Flush privileges to save the changes:

FLUSH PRIVILEGES;

Now the root user can authenticate using a password, bypassing the UNIX socket method.

Conclusion

MySQL error 1698 is like a block on your way when trying to access your database. Whether you’re encountering this error for the first time or seeking to prevent future occurrences, understanding the specifics of this error is crucial. To solve the MySQL error 1698 you need a thorough understanding of MySQL’s authentication architecture, especially when it intersects with operating system-level security practices, such as those observed in Ubuntu systems.

The MYSQL error 1698, which denies access to the root user demands a thoughtful configuration of authentication mechanisms within MySQL.

We discussed thorough solution methods ranging from altering the authentication plugin to `mysql_native_password`, integrating system users into MySQL’s user table with the `auth_socket` plugin, or disabling UNIX_socket authentication altogether. Each solution offers a unique approach to bypassing the error, tailored to fit different security postures and administrative preferences. The one you will choose will depend on the level of security you want to have using your database management system.