版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:配置ProxySQL出现“OFFLINE_HARD”的解决办法
这两天在做ProxySQL的一系列实验,不过在实验中遇到一个小问题。当我查询runtime_mysql_servers或者是stats.stats_mysql_connection_pool视图的时候,经常发现配置PXC主机status处于”OFFLINE_HARD”状态。这个状态的含义如下,硬离线”状态,不再接受新的连接,已建立的连接或被强制中断。当后端实例宕机或网络不可达会出现这种状态。但是我通过PXC集群连接进去发现集群的状态都是正常的。
[root@localhost proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' 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 6 Server version: 5.7.26 (ProxySQL Admin Module) Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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. Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+----------------+--------------+----------+-------------+---------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+----------------+--------------+----------+-------------+---------+------------+ | 4 | 192.168.56.161 | ONLINE | 0 | 0 | 0 | 1362 | | 4 | 192.168.56.162 | ONLINE | 0 | 0 | 0 | 1608 | | 2 | 192.168.56.163 | ONLINE | 0 | 0 | 0 | 969 | | 3 | 192.168.56.163 | OFFLINE_HARD | 0 | 0 | 0 | 969 | +-----------+----------------+--------------+----------+-------------+---------+------------+
针对这个问题,我们要做的首先是检查proxysql.log日志,该日志位于/var/lib/proxysql下面。
[root@localhost proxysql]# grep -i OFFLINE proxysql.log | grep because | cut -b 124- | sort | uniq -c 1 setting host 192.168.56.161:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist 1 setting host 192.168.56.162:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist 1 setting host 192.168.56.163:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
这里说明了offline问题的原因,是因为有个表不存在。叫sys.gr_member_routing_candidate_status,这个视图额外提供了一些复制组成员的信息。这些信息需要被ProxySQL使用。这个脚本需要下载。下载地址如下:传送门
[root@pxc1 mysql]# mysql -p < addition_to_sys.sql Enter password:
安装完成之后再次执行相关查询,OFFLINE_HARD的状态已经消失了。不过我发现需要先查询。runtime_mysql_servers状态再次查询stats.stats_mysql_connection_pool才会消失。
Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+----------------+--------+----------+-------------+---------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+----------------+--------+----------+-------------+---------+------------+ | 4 | 192.168.56.161 | ONLINE | 0 | 0 | 0 | 1016 | | 4 | 192.168.56.162 | ONLINE | 0 | 0 | 0 | 1003 | | 2 | 192.168.56.163 | ONLINE | 0 | 0 | 0 | 1041 | +-----------+----------------+--------+----------+-------------+---------+------------+ 3 rows in set (0.00 sec)
Post a Comment