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.
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:
mysql_native_password plugindb_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.