Unable to access clients’ MySQL databases on Plesk for Windows: Access denied for user ‘admin’@’localhost’ (using password: YES)

Symptoms

• Unable to create a database dump in Plesk > Domains > example.com > Databases > Export Dump:

  mysqldump: Got error: 1045: Access denied for user ‘admin’@’localhost’ (using password: YES) when trying to connect

• Attempts to manage a customer’s MySQL databases or remove mailbox produces errors:

  Error: Connection to the database server has failed because of network problems: Try to establish connection failed

  ---

  Error: Connection to the database server has failed because the supplied account does not possess administrative privileges: Access denied for user ‘admin‘@’localhost’ (using password: YES)

• The MySQL server is marked red on the Tools & Settings > Database Servers screen.

Cause

Plesk stores an incorrect MySQL server administrator password.

Resolution

To solve the issue and reconnect Plesk to the MySQL server do the following:

1. Connect to the server using RDP.

2. Stop the MariaDB server (but not “Plesk SQL Server”) from running on port 3306 using the Plesk Services Monitor, which can be started from the Windows Start menu.

   Note:  Plesk Services Monitor could also be started using the next cmd.exe command:

   C:> “%plesk_dir%adminbintraymonitor.exe”

   

3. Edit the MariaDB configuration file %plesk_dir%DatabasesMySQLmy.ini and add the line skip-grant-tables to the [mysqld] section and save it using text editor:

   [mysqld]
   skip-grant-tables

4. Start the MariaDB server using the Plesk Services Monitor.

5. Log into MySQL without the password:

   “%plesk_dir%MySQLbinmysql.exe” -P3306

6. Reload the grant-tables policies by running the next command:

   FLUSH PRIVILEGES;

7. If there is no user “admin” in MySQL:

   mysql> ALTER USER ‘admin’@’localhost’ IDENTIFIED BY ‘new_password’;
   Query OK, 0 rows affected (0.00 sec) <– No user “admin” found

   Create the admin user and grant all privileges:

   mysql>CREATE USER ‘admin’@’localhost’ IDENTIFIED BY ‘password’;
   mysql>GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’localhost’ WITH GRANT OPTION;

8. Change the password for the MySQL administrator:

   For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command:

   ALTER USER ‘admin’@’localhost’ IDENTIFIED BY ‘new_password’;

   For MySQL 5.7.5 and older as well as MariaDB older than 10.1.20, use:

   SET PASSWORD FOR ‘admin’@’localhost’ = PASSWORD(‘new_password’);

   Note: Make sure to replace ‘new_password’ with a new password of choice.

   Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command;

   FLUSH PRIVILEGES;

9. Remove skip-grant-tables from the MySQL configuration file and restart the service.

10. Log in to the Plesk interface, go to the database server management settings ( Tools & Settings > Database Servers ), and change the password for the corresponding database server to the one set in Step 8. Plesk will re-establish the connection to the server.