Troubleshoot connection errors in the MySQL plugin

Troubleshoot connection errors in the MySQL plugin

When configuring the MySQL plugin, you may encounter the following errors:
Error : Connection Error - This error typically indicates that there is a problem establishing a connection to the database.
Sometimes, this message is supplemented with an access denied message, as shown below.
Error: Connection Error: (1045, "Access denied for user 'root'@'localhost' (using password: NO)") - This error typically indicates that the MySQL server is rejecting the login attempt for the root user from the localhost. This can occur because: 
  1. A password is required, but it was not provided (as indicated by "using password: NO").
  2. The user does not have access from the localhost.

Here are some steps you can take to resolve the errors:

1. Ensure that the MySQL server is running.

Check if the MySQL server is running. On Linux, you can use the following command:
sudo systemctl start mysql

2. Troubleshoot the Access denied for user 'root'@'localhost' error.

Create a MySQL user and grant the SELECT ON queries permission to the user.
Note: It is good practice not to use the root user for monitoring MySQL. Instead, create dedicated users with only the necessary privileges and use them. 
  1. To create a MySQL user, use the command below:
    CREATE USER username@hostname IDENTIFIED BY 'password';

  2. Grant SELECT ON permission to the user using the command below:
    GRANT SELECT ON mysql.* TO username@hostname IDENTIFIED BY 'password';

    For example, create a user with the name 'site24x7' and with 'site24x7' as the password. Give SELECT ON permission as well as SUPER and/or REPLICATION CLIENT privilege to the 'site24x7' user and flush the privileges as shown below:

    CREATE USER site24x7@localhost IDENTIFIED BY 'site24x7';
    GRANT SELECT ON mysql.* TO site24x7@localhost IDENTIFIED BY 'site24x7';
    use mysql;
    UPDATE mysql.user SET Super_Priv='Y' WHERE user='site24x7' AND host='localhost'; (or)
    UPDATE mysql.user SET Repl_client_priv='Y' WHERE user='site24x7' AND host='localhost';
    FLUSH PRIVILEGES;

    For MariaDB, use the following command:
    CREATE USER site24x7@localhost IDENTIFIED BY 'site24x7';
    GRANT SUPER ON *.* TO 'site24x7'@'localhost';
    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'site24x7'@'localhost';
    FLUSH PRIVILEGES;
3. Ensure access for the user 'username'@'localhost' using password: YES/NO.
  1. Make sure the username and password are correct.
  2. Reset the password if necessary.
  3. Ensure the user has the appropriate permissions to connect from the given host.

Related documentation: