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

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:<

 · 3 min read

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 Installation Tips and I can't find those variables in the config files.














-------------------------------xxxxxx0--------------------

Some systems like Ubuntu, mysql is using by default the 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;

$ 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 is 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 stretch) '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';

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, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/



No comments yet

No comments yet. Start a new discussion.

Add Comment