Articles in this section

See more

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

Avatar
Nelson Leiva
Updated
Follow
Plesk for Windows kb: technical ABT: Group B

Applicable to:

  • Plesk for Windows

Note: This article is related to MySQL user's databases server on Windows. Not the Plesk SQL one. Plesk interface should be accessible.

Symptoms

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

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

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

    PLESK_ERROR: 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 MariDB 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.

    mceclip0.png

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

    CONFIG_TEXT: [mysqld]
    skip-grant-tables

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

  5. Log into MySQL without the password:

    C:\> "%plesk_dir%\MySQL\bin\mysql.exe" -P3306

  6. Change the password for the MySQL administrator:

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

    MYSQL_WIN: FLUSH PRIVILEGES;

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

    MYSQL_WIN: ALTER USER 'admin'@'localhost' IDENTIFIED BY 'new_password';

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

    MYSQL_WIN: SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('new_password');

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

  7. If there is no user "admin" in MySQL find the actual MySQL administrator and rename the user to "admin":

    MYSQL_WIN: mysql> UPDATE mysql.user SET Password = password('newpassword') WHERE User = 'admin';
    Query OK, 0 rows affected (0.00 sec) <-- No user "admin" found

    MYSQL_WIN: mysql> select host, user from mysql.user;
    +-----------+------------------+
    | host | user |
    +-----------+------------------+
    | localhost | local_mysql_root | <--- MySQL administrator was renamed to "local_mysql_root"
    +-----------+------------------+
    1 row in set (0.00 sec)

    MYSQL_WIN: mysql> update mysql.user set user="admin" where user="local_mysql_root";
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    MYSQL_WIN: mysql> ALTER USER 'admin'@'localhost' IDENTIFIED BY 'new_password';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

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

  9. 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 5. Plesk will re-establish the connection to the server.

    Note: in some rare cases step 6 may not work and the following procedure can be used for the admin user How to Reset the Root Password. Make sure the commands are adapted for the admin user.

Comments

4 comments

Sort by Date Votes
  • Avatar
    Alberto Garcia

    Where can I finde the Plesk Services Monitor in LINUX?

    1
    Comment actions Permalink
  • Avatar
    Nikolay Zhmuk

    @Alberto Garcia for Linux systems check the articles below:

    https://support.plesk.com/hc/en-us/articles/213376789

    https://support.plesk.com/hc/en-us/articles/213364309

    1
    Comment actions Permalink
  • Avatar
    Alberto Garcia

    Thanks I am still having problems.

     

    With the second Link I have this error message

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

    And with the first link the following error:

    [2017-08-28 07:29:11] ERR [panel] SQLSTATE[HY000] [1045] Access denied for user 'admin'@'localhost' (using password: YES):
    0: /opt/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:144
            Zend_Db_Adapter_Pdo_Abstract->_connect()
    ...
    ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [1045] Access denied for user 'admin'@'localhost' (using password: YES) (Abstract.php:144)
    exit status 1

    0
    Comment actions Permalink
  • Avatar
    Nikolay Zhmuk

    @Alberto Garcia Have you tried to add skip-grant-tables directive to my.cnf file, restart the mysql service and reset the password for admin? Also, if there were upgrade of mysql server recently, check error log of mysql service, the message should be self-explanatory. If the issue still persists I recommend to contact Technical Support directly - https://www.plesk.com/contact-support

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles