The Error – "Access denied for user 'root'@'localhost' (using password: YES)" appears when working with DBMS

Symptoms

When performing actions in the ispmanager panel, an error message appears

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

This error can occur when using any native database server - MySQL or MariaDB, depending on the operating system.

Causes

• The password for the "root" user of the database server (hereinafter, DBMS) was set before the panel was installed;
• The database server was installed from a third-party repository other than the panel;
• The password for the "root" database user (hereinafter referred to as the DB) was changed manually outside the panel, after its installation.

Solution

First, enter the password of the database’s "root" user in the "Database Servers" section.

Path to the section in Lite, Pro, and Host:

"Databases" → "Database Servers" → "Change" → "Password".

Path to the section in Business:

"Cluster Settings" → "Cluster Nodes" → select a node → three dot menu → "Database Servers" → "Change" → "Password"

If this does not resolve the error, ensure that the password in the "/root/.my.cnf" file matches the password for the "root" user in the panel and in the native DBMS database.

If the password has been lost, perform the following steps to reset it:

1. At the end of the DBMS configuration file "my.cnf", add the "[mysqld]" section and two parameters, as follows:

[mysqld]
skip-grant-tables
skip-networking

The path to the file in CentOS 7, CloudLinux, AlmaLinux 8, 9, and RockyLinux 8 is:

/etc/my.cnf

File path in Debian 10, 11, Ubuntu 18, 20, 22:

/etc/mysql/my.cnf

2. To apply the new DBMS parameters, restart the service:

• systemctl restart mysql - for Debian, Ubuntu, RockyLinux 8, and AlmaLinux 9;
• systemctl restart mysqld - for Almalinux 8;
• systemctl restart mariadb - for CentOS 7:

3. Connect to the database server:

mysql

4. Reset privileges:

FLUSH PRIVILEGES;

5. Change the password of the "root" user:

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

, where <password> is the new password.

If this command worked, go to step 6. If the command did not work, run the following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('<password>');

If you get the error "ERROR 1290 (HY000): The MySQL server is running with the -skip-grant-tables option so it cannot execute this statement", flush privileges again:

FLUSH PRIVILEGES;

After that, try changing the password again.

6. Delete the parameters added to the DBMS configuration file in step 1 - it is necessary to close the possibility of unauthorized access to the DBMS.

7. Restart the DBMS service using the corresponding command from step 2.

8. To check it, connect to the database server using the command

mysql -uroot -p<password>

where <password> is the password entered in step 5.

9. In the ispmanager panel, go to the "Database Servers" section and in the "Password" field, enter your new password. The "Set new password" option does not need to be checked.