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

itemprop="text">

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

When I try to login
to 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, 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 with
Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it
can host mail.

Login in to the MySQL database
works fine before I install iRedMail. I also tried, just installing iRedMail, but then
root, also doesn't work...

If someone
could tell me how I fix my MySQL login problem or how I install iRedMail, on top of an
existing MySQL install. And yes I tried the href="https://code.google.com/archive/p/iredmail/wikis/Installation_Tips.wiki"
rel="noreferrer">Installation Tips and I can't find those variables in the
config files.

itemprop="text">

class="normal">Answer

Some
systems like Ubuntu, mysql is using by default the href="https://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/socket-pluggable-authentication.html"
rel="noreferrer">UNIX auth_socket
plugin.

Basically means that:
db_users using it, will be "auth" by the system user
credentias. 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 is 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 2 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 is new
            installation

mysql> USE mysql;
mysql> UPDATE user
            SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH
            PRIVILEGES;
mysql> exit;

$ service mysql
            restart

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

$ sudo mysql -u root # I had
            to use "sudo" since is new installation

mysql> USE
            mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY
            '';
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;

$ 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 stretch) 'auth_socket' plugin is called href="https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/"
rel="noreferrer">'unix_socket', so the corresponding SQL command should be:
UPDATE user SET plugin='unix_socket' WHERE
User='YOUR_SYSTEM_USER';

Update:

from
@andy's comment 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 auth plugin and you can login from the bash shell now
with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the
prompt. No need for the "UPDATE user SET plugin" step. For the 8.0.4 default auth plugin
update see, href="https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/"
rel="noreferrer">https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/