My TAS pxc-mysql/mysql.err.log is flooded with "Access denied for user 'cluster-health-logger'@'localhost'" messages

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

This is an issue with TAS v2.10.20+, TAS v2.11.8+, TAS v2.12.1+. In pxc-release v0.38.0, a change was made to have the cluster-health-logger connect as the "localhost" user, but then later change was reverted.

Resolution

On each TAS mysql instance, there is the file /var/vcap/jobs/cluster-health-logger/config/cluster_health_logger_setup.sql. In this file is the SQL to create the "[email protected]" user. The SQL can be copied from this file, modified to change "127.0.0.1" to "localhost", then executed after connecting to the database using the command "sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf". This will look like:

mysql/37806e03-4dee-48c4-b8dc-b597f4987ae0:~$ sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 266
Server version: 5.7.35-38-log Percona XtraDB Cluster (GPL) 5.7.35-31.53, wsrep_31.53

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SESSION SQL_LOG_BIN = off;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER IF NOT EXISTS 'cluster-health-logger'@'localhost' IDENTIFIED BY 'SGtUVahQfS45WmZGQTc1xgdNWgJd8R';
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER USER IF EXISTS 'cluster-health-logger'@'localhost' IDENTIFIED BY 'SGtUVahQfS45WmZGQTc1xgdNWgJd8R';
Query OK, 0 rows affected (0.02 sec)

mysql> SET SESSION SQL_LOG_BIN = on;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host, User, password_expired, password_last_changed FROM mysql.user WHERE User LIKE 'cluster-health-logger';
+-----------+-----------------------+------------------+-----------------------+
| Host      | User                  | password_expired | password_last_changed |
+-----------+-----------------------+------------------+-----------------------+
| 127.0.0.1 | cluster-health-logger | N                | 2022-03-04 22:41:23   |
| localhost | cluster-health-logger | N                | 2022-03-04 23:35:03   |
+-----------+-----------------------+------------------+-----------------------+
2 rows in set (0.00 sec)

 

Since this is for a Percona XtraDB Cluster, the user created on one node of the cluster will be propagated to the other cluster members.