Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

iredmail

I'm setting up a new server and keep running into this problem.

When I try to log into the MySQL database with the root user, I get the error:

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

It doesn't matter if I connect through the terminal (SSH), through phpMyAdmin or a MySQL client, e.g., Navicat. They all fail.

I looked in the mysql.user table and get the following:

+------------------+-------------------+ | user             | host              | +------------------+-------------------+ | root             | %                 | | root             | 127.0.0.1         | | amavisd          | localhost         | | debian-sys-maint | localhost         | | iredadmin        | localhost         | | iredapd          | localhost         | | mysql.sys        | localhost         | | phpmyadmin       | localhost         | | root             | localhost         | | roundcube        | localhost         | | vmail            | localhost         | | vmailadmin       | localhost         | | amavisd          | test4.folkmann.it | | iredadmin        | test4.folkmann.it | | iredapd          | test4.folkmann.it | | roundcube        | test4.folkmann.it | | vmail            | test4.folkmann.it | | vmailadmin       | test4.folkmann.it | +------------------+-------------------+ 

As you can see, user root should have access.

The Server is quite simple, as I have tried to troubleshoot this for a while now.

It's running Ubuntu 16.04.1 LTS (Xenial Xerus) with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail.

Log into the MySQL database works fine before I installed iRedMail. I also tried just installing iRedMail, but then root also doesn't work.

How can I fix my MySQL login problem or how can I install iRedMail over an existing MySQL install? And yes, I tried the Installation Tips and I can't find those variables in the configuration files.

like image 816
Folkmann Avatar asked Sep 01 '16 22:09

Folkmann


People also ask

How do I fix MySQL error Access denied for user root localhost?

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.

What is error 1698 in MySQL?

The “ERROR 1698 (28000): Access denied for user 'root'@'localhost'” error is one of the most common errors which occurs during command-line connection to the MySQL server shell. This error can be a bit different according to the command like “Access denied for user 'root'@'localhost' (using password: YES)“.

How do I fix root access denied?

Solution 1: Sudo then Change Password If you get the “access denied” error, one way to solve it is by using sudo to log in to mysql and change the root password. Step 1: Open the command line on your system. Step 3: Enter the password for this account.

How do I fix access denied in MySQL?

To resolve the error, you must create a user with the following command: mysql> GRANT ALL ON *. * to user_name@localhost IDENTIFIED BY 'password'; Replace user_name with the user's username and password with the user's password.


2 Answers

A new version of MySQL does it this way

In the new MySQL client, if the password is left empty while installing then, it is based on the auth_socket plugin.

The correct way is to log in to MySQL with the sudo privilege.

sudo mysql -u root -p 

And then updating the password using:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password'; 

Once this is done, stop and start the MySQL server.

sudo service mysql stop sudo service mysql start 

For complete details, you can refer to this link.

like image 24
Nandesh Avatar answered Sep 22 '22 14:09

Nandesh


On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default.

Basically it means that: db_users using it, will be "authenticated" by the system user credentials. You can see if your root user is set up like this by doing the following:

sudo mysql -u root # I had to use "sudo" since it was a new installation  mysql> USE mysql; mysql> SELECT User, Host, plugin FROM mysql.user;  +------------------+-----------------------+ | User             | plugin                | +------------------+-----------------------+ | root             | auth_socket           | | mysql.sys        | mysql_native_password | | debian-sys-maint | mysql_native_password | +------------------+-----------------------+ 

As you can see in the query, the root user is using the auth_socket plugin.

There are two ways to solve this:

  1. You can set the root user to use the mysql_native_password plugin
  2. You can create a new db_user with you system_user (recommended)

Option 1:

sudo mysql -u root # I had to use "sudo" since it was a new installation  mysql> USE mysql; mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root'; mysql> FLUSH PRIVILEGES; mysql> exit;  sudo service mysql restart 

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

sudo mysql -u root # I had to use "sudo" since it was a new installation  mysql> USE mysql; mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost'; mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER'; mysql> FLUSH PRIVILEGES; mysql> exit;  sudo service mysql restart 

Remember that if you use option #2 you'll have to connect to MySQL as your system username (mysql -u YOUR_SYSTEM_USER).

Note: On some systems (e.g., Debian 9 (Stretch)) the 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

From andy's comment it seems that MySQL 8.x.x updated/replaced the auth_socket for caching_sha2_password. I don't have a system setup with MySQL 8.x.x to test this. However, the steps above should help you to understand the issue. Here's the reply:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this authentication plugin and you can log in from the Bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. There isn’t any need for the "UPDATE user SET plugin" step.

For the 8.0.4 default authentication plugin update, see MySQL 8.0.4: New Default Authentication Plugin: caching_sha2_password.

like image 92
zetacu Avatar answered Sep 20 '22 14:09

zetacu