Mariadb error 1698 28000 access denied for user root localhost

Building a local webserver involves installing all your dependencies either manually or using a preset stack like LAMP or XAMPP. The problem with these stacks is that they’re meant to be used in development environments and while they make the whole process easier, you can’t quite use them in a production environment.

The reason being these stacks aren’t exactly great from a security perspective. For that, you’re going to have to install individual web server components and set them up on your own.  

However, when setting up a database such as MySQL or MariaDB, you might run into the “Error 1698 28000 access denied for user root localhost” issue. In this article, we’re talking about this error, what causes it and how you can fix this.

Also read: Debian vs Ubuntu: Which Linux distro should you pick?

What causes this error?

Usually, when you install a database management system otherwise known as DBMS such as MySQL or MariaDB, you’re asked to set a root password. This password is then used to log in to the database in order to make changes or review entries. 

However, at times you might not be asked for a password in which case you’ll run into this issue. This is caused because, on some systems such as Ubuntu, MySQL uses the UNIX auth_socket plugin by default. 

What this means is that any database user trying to access the database will be authenticated by the system user credentials. 

Also read: RM command in Linux explained with examples

How to resolve the situation?

There are two ways you can tackle this error.

• You can set the root user to use the native MySQL password.
• You can create a new database user with your system user credentials. This is the recommended way.

Setting the root user to use the native MySQL password

Follow these steps if you’re willing to follow through on the first option.

Step 1: Type the following command in the terminal to launch MySQL as root.

sudo mysql -u root

Step 2: Once the MySQL console opens up, type the following commands one after the other pressing enter after each command. 

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE user='root';
FLUSH PRIVIILEGES;
exit:

Step 3: Now restart the MySQL service using this command.

sudo service mysql restart

Try accessing your database again it should work just fine. 

Creating a new database user

In the commands below, replace your_user with your username.

Step 1: Type the following command in the terminal to launch MySQL as root.

sudo mysql -u root

Step 2: Once the MySQL console opens up, type the following commands one after the other pressing enter after each command. 

CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='your_user';
FLUSH PRIVILEGES;
exit;

Step 3: Now restart the MySQL service using this command.

sudo service mysql restart

Try accessing your database again it should work just fine. 

Also read: How to add a user to a group in Linux?

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Introduction

Most MySQL users encountered the ERROR 1698 (28000): Access denied for user ‘root’@’localhost’. This error message usually appears for new installations of MySQL when you try to connect to MySQL with the root user.

This guide will show you how to quickly resolve the access denied for user root on localhost. The outlined instructions apply to both MySQL and MariaDB. There will be no need to modify any tables or to perform complex configuration.

Prerequisites

• Access to a command line or terminal window
• MySQL or MariaDB installed
• User with sudo or root privileges

When you install MySQL and try to access it on the local machine with the root user, the command you use is:

mysql -u root -p

In most cases, you will receive the error message Access denied for user ‘root’@’localhost’.

You can enable access for root using one MySQL command.

Solve Access Denied for User Root Error

To be able to log into MySQL as root, first use sudo to modify the root user:

sudo mysql

Enter your password at the prompt. A MySQL shell loads.

Use the ALTER USER command and change the authentication method to log into MySQL as root:

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

This command changes the password for the user root and sets the authentication method to mysql_native_password. This is a traditional method for authentication, and it is not as secure as auth_plugin. In the example above, we set “root” as the password, but we encourage you to set a stronger password.

Test Root User MySQL Access

After you run the commands listed above, exit the MySQL shell by pressing CTRL + D on your keyboard or type exit; and hit enter. There is no need to restart the mysqld service to log in.

Now try again to access MySQL with root. In the terminal, type in:

mysql -u root -p

Enter the password you used with the ALTER USER command. Do not type in the system password to access MySQL as it will not work. If everything worked fine, you should see the MySQL welcome message.

Conclusion

Now you know how to bypass the MySQL ERROR 1698 (28000): Access denied for user ‘root’@’localhost’.

There are different ways to approach this issue, but we selected the easiest and fastest method. Make sure to enter the commands as listed in the article to avoid errors in SQL syntax.