proxy SQL 集群状态异常

mysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers order by hostgroup_id;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname        | port | status |
+--------------+-----------------+------+--------+
| 1            | 192.168.211.129 | 3306 | ONLINE |
| 2            | 192.168.211.133 | 3306 | ONLINE |
| 2            | 192.168.211.134 | 3306 | ONLINE |
+--------------+-----------------+------+--------+

上图可以看出主库缺一个2的状态；查看proxy SQL日志如下：

[root@localhost /]# tail -f /data/proxysql/proxysql.log
2024-08-13 16:41:32 [INFO] New mysql_galera_hostgroups table
2024-08-13 16:41:32 [INFO] New mysql_aws_aurora_hostgroups table
2024-08-13 16:41:32 [INFO] MySQL_HostGroups_Manager::commit() locked for 5ms
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Sun May 17 20:24:24 2020
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Sun May 17 20:24:24 2020
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Sun May 17 20:24:24 2020
2024-08-13 16:41:32 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server 192.168.211.134:3306 is returning "Access denied" for monitoring user
2024-08-13 16:41:33 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server 192.168.211.133:3306 is returning "Access denied" for monitoring user
2024-08-13 16:41:33 MySQL_Monitor.cpp:718:monitor_connect_thread(): [ERROR] Server 192.168.211.129:3306 is returning "Access denied" for monitoring user
2024-08-13 16:41:35 [INFO] Latest ProxySQL version available: 2.6.3-107-gcdfcfdc

解决办法如下：

先检查proxysql.cnf文件的密码和IP地址，然后执行如下命令：

[root@localhost /]# vi /etc/proxysql.cnf 
[root@localhost /]# rm -rf /var/lib/proxysql /data/proxysql
[root@localhost /]# mkdir /var/lib/proxysql /data/proxysql && chown -R proxysql:proxysql  /var/lib/proxysql /data/proxysql
[root@localhost /]# vi /data/proxysql.cnf 
[root@localhost /]# cd data/
[root@localhost data]# \cp -rf proxysql.cnf /etc/proxysql.cnf
[root@localhost data]# systemctl restart proxysql 

完美解决如下

[root@localhost data]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.12 (ProxySQL Admin Module)

Copyright (c) 2000, 2023, 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> select hostgroup_id, hostname, port,status from runtime_mysql_servers order by hostgroup_id;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname        | port | status |
+--------------+-----------------+------+--------+
| 1            | 192.168.211.129 | 3306 | ONLINE |
| 2            | 192.168.211.129 | 3306 | ONLINE |
| 2            | 192.168.211.130 | 3306 | ONLINE |
| 3            | 192.168.211.128 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
4 rows in set (0.04 sec)