Buddy Yuan的个人技术博客

版权声明：本文为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)