When I try to login to MySQL database, I get some error message like “error 1698 (28000)”. Please help!

That was a recent support ticket received at our Server Support department where we resolve support queries for web hosts.

Website owners often face this error when they access the MySQL database as root user.

So, what’s the reason behind this? And, how to resolve this?

Today, we’ll discuss the reason for this error and how our Server Supported Engineers fix it.

‘error 1698’ MySQL error – What this means?

Before we move on to the reasons, let’s first get an idea of MySQL error ‘error 1698′.

Website owners face this error when MySQL disallows the root user to access the database.

For instance, users see the complete error message like this:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

In addition to that, website owners will not be able to connect to the database via a terminal, PHPmyadmin or MySQL client.

In our experience managing websites, majority of such complaints are raised by customers on a Ubuntu machine.

‘error 1698’ Mysql error – Causes and solutions

First, let’s see the main cause of this error identified by our Support Engineers during our debugging process.

Some systems like Ubuntu don’t use root password, instead Mysql root account is configured to use the auth_socket or unix_socket plugin for authentication.

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
+------------------+-----------------------+

This plugin allows the user to use Operating System credentials when connecting to the database.

In other words, this plugin doesn’t care for password. It checks if the user is connecting via UNIX socket and then compares the username.

For example, a user authenticate to the Ubuntu system as test, so MySQL won’t trust the user when he login as root user, because this user is different from the system user test.

Result is ‘error 1698‘ Mysql error.

How we fix?

Now, let’s see how our Support Engineers fix this error.

1) Set root user to use ‘mysql_native_password‘ plugin

‘mysql_native_password’ implements authentication against mysql.user table using the native password authentication method.

So, the first solution our Hosting Engineers use, is to update the root user password and change the root user’s authentication plugin to ‘mysql_native_password’.

For example, we use the following command to change the root user’s authentication plugin to mysql_native_password.

user mysql;
update user set plugin='mysql_native_password' where User = 'root';

And, in order for the changes to reflect, and the privileges to be saved, we use the below command.

FLUSH PRIVILEGES;

[Need assistance in fixing MySQL errors in your website? Our MySQL experts can assist you. We are available 24/7.]

2) Add the system user to the MySQL ‘user’ table

Basically, mysql_native_password is the traditional method of authentication, but it isn’t secure, because it uses just a hash of the password.

So, the safe option that our Security Engineers always recommend is to add the system user to the MySQL user table with auth_socket plugin enabled.

For example, we use the following command to add the system user testing to the MySQL user table with the auth_socket plugin.

USE mysql;
CREATE USER 'testing'@'localhost' IDENTIFIED BY '';+
UPDATE user SET plugin='auth_socket' WHERE User='testing';

After that, we grant all privileges to this user, and flush the privileges for the changes to take effect.

Now, the user can login to MySQL with this system user.

3) Disable UNIX_socket authentication

Another simple solution to this problem is to disable UNIX socket authentication.

Our Hosting Engineers disable unix_socket authentication for root user using the following command.

use mysql;
update user set plugin='' where User='root';

Now, the root user can login with standard password authentication.

We’ve discussed 3 different ways to resolve this error.

Considering the security aspect, our MySQL experts always suggest server owners to follow the second step.

Because, it’s always good practice to create a new user and leave the root user there.

[Need a Mysql expert to look at this error? Our Dedicated Support Engineers can fix this for you within minutes.]

Conclusion

In short, ‘error 1698‘ Mysql error occurs due to the incorrect authentication plugin enabled for the root user. Today, we’ve discussed the 3 easy methods to solve this error and the most reliable solution suggested by our Server Support Engineers.