Unable to run scheduled backups or run Migration on Plesk: Access denied for user ‘admin’@’::1′ (using password: YES)

Symptoms

• The backup log contains MySQL errors that are similar to the following:

  Warning: Database "admin_wp_example"
  Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.
  mysqldump: Got error: 1045: "Access denied for user 'admin'@'::1' (using password: YES)" when trying to connect

• The Plesk Migrator fails to connect to the source database server and issues the following error:

  Failed to fetch basic information about resellers, clients and domains data from source servers
  Cause: Failed to execute SQL query on panel database: Can not connect to the Plesk database. Please, check database server is running.
  That is a critical error, migration was stopped.

• The following warnings can be found for the MariaDB service:

  # journalctl -u mariadb
  Jan 01 00:00:00 host.example.com mariadbd[PID]: 2000-01-01 0:00:00 [Warning] Access denied for user 'admin'@'::1' (using password: YES)
  Jan 01 00:00:00 host.example.com mariadbd[PID]: 2000-01-01 0:00:00 [Warning] Access denied for user 'admin'@'::1' (using password: YES)

• WordPress cloning or copying may fail with the following error:

  Unable to import database. Error message: ERROR 1045 (28000): Access denied for user 'username'@'127.0.0.1' (using password: YES)

Cause

Plesk may use host specifications like -h 127.0.0.1 or -h ::1 to connect to MySQL server.

Normally, these host specifications are covered by 'admin'@'localhost' user definition.

With the skip-name-resolve variable enabled, 'admin'@'localhost' no longer covers specifications like -h 127.0.0.1 or -h ::1 and Plesk connection to MySQL server fails.

Resolution

In order to resolve the issue, use one of the two solutions below:

Option 1

Disable usage of the skip-name-resolve variable for the database server:

1. 1. Log into Plesk

   2. Remove the skip-name-resolve variable to /etc/my.cnf (or /etc/mysql/my.cnf) config file.

   3. Check database connection by using the following commands:

      # MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -uadmin -h ::1
      # MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -uadmin -h 127.0.0.1
      # MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -uadmin -h localhost

Option 2
Define additional MySQL 'admin' user definitions to specifically cover for the -h 127.0.0.1 and -h ::1 host specification cases:

1. 1. Connect to the server via SSH

   2. Login to the MySQL shell by executing the following command:

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

   3. Run the following commands:

      GRANT ALL PRIVILEGES ON *.* TO 'admin'@'::1' IDENTIFIED BY 'password' WITH GRANT OPTION;

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

   4. Log out of mysql shell and run the following command in Linux shell:

      # plesk bin admin --set-admin-password -passwd 'password'

      This command sets the same password for all three 'admin' accounts.

   5. Check that new 'admin' user definitions works by running the following commands:

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