Access denied for user 'admin'@'localhost' (using password: YES)

Symptoms

The Plesk backup of a subscription cannot be executed because the connection to the MySQL server has failed. The following entry appears in the backup log:

Cause

The problem occurs because the MySQL authentication distinguishes between localhost, 127.0.0.1 and ::1 and at the same time the MySQL option skip-name-resolve is active.

Solution

The problem can be solved by creating an additional MySQL user for the Plesk admin user.

Connect to the server via SSH

Open the MySQL shell using the command:

MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -uadmin

First check the existing admin users:

SELECT user,host FROM mysql.user WHERE user = 'admin';

Example output:

MariaDB [(none)]> SELECT user,host FROM mysql.user WHERE user = 'admin';
+-------+-----------+
| user | host |
+-------+-----------+
| admin | localhost |
+-------+-----------+
1 row in set (0.001 sec)

Create a new admin user if the faulty host from the previous command is not included in the output:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '<password customercenter>' WITH GRANT OPTION;

plesk bin admin --set-admin-password -passwd '<password customercenter>'

If you are using a new password, this should also be changed in the customer center afterwards in order to restore the single sign-on function.

More information about Plesk Single-Sign-On

Connection test

The following command can be used to test the connection via the IP address:

MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysqlcheck -c -u admin -h 127.0.0.1 --all-databases

The backup can then be restarted in Plesk.