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

so... i'm setting new server , keep running problem.

when try login mysql database root user, "error 1698 (28000): access denied user 'root'@'localhost'" error.

it doesn't matter if connect through terminal(ssh), through phpmyadmin or mysql client, e.g. navicat. fail.

i looked in mysql.user table , 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 can see, root should have access.

the server quite simple, have tried troubleshoot while now..

it's running ubuntu 16.04.1 lts apache, mysql , php, can host websites, , iredmail 0.9.5-1, can host mail.

login in mysql database works fine before install iredmail. tried, installing iredmail, root, doesn't work...

if tell me how fix mysql login problem or how install iredmail, on top of existing mysql install. , yes tried installation tips , can't find variables in config files.

any appreciated :)

the reason recent ubuntu installation (maybe others also), mysql using default unix auth_socket plugin.

basically means that: db_users using it, "auth" the system user credentias. can see if root user set doing following:

$ sudo mysql -u root # had use "sudo" since new installation  mysql> use mysql; mysql> select user, host, plugin mysql.user;  +------------------+-----------------------+ | user             | plugin                | +------------------+-----------------------+ | root             | auth_socket           | | mysql.sys        | mysql_native_password | | debian-sys-maint | mysql_native_password | +------------------+-----------------------+ 

as can see in query, root user using auth_socket plugin

there 2 ways solve this:

1. you can set root user use mysql_native_password plugin
2. you can create new db_user system_user (recommended)

option 1:

$ sudo mysql -u root # had use "sudo" since new installation  mysql> use mysql; mysql> update user set plugin='mysql_native_password' user='root'; mysql> flush privileges; mysql> exit;  $ service mysql restart 

option 2: (replace your_system_user username have)

$ sudo mysql -u root # had use "sudo" since new installation  mysql> use mysql; mysql> create user 'your_system_user'@'localhost' identified ''; mysql> grant privileges on * . * 'your_system_user'@'localhost'; mysql> update user set plugin='auth_socket' user='your_system_user'; mysql> flush privileges; mysql> exit;  $ service mysql restart 

remember if use option #2 you'll have connect mysql system username (mysql -u your_system_user)