ProxySQL backends always online never shunned

Question

I'm trying to setup simple proxy to galera cluster via proxysql. Backend nodes and proxysql all in docker. Proxy works ok, except monitoring. When i stop mariadb on one node, proxysql still shows the node as ONLINE.

I've tried:

Create monitoring user. This seems to work fine.
Load configuration to runtime.
SET mysql-connect_timeout_server_max=20000;
UPDATE mysql_servers SET max_latency_ms=30 WHERE hostname='10.77.250.25';
UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='10.77.250.25';

Output from proxysql admin console:

mysql> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 10.77.250.27 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 10.77.250.25 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 30                  | 0       | 30             |         |
| 20           | 10.77.250.26 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 10.77.250.27 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

mysql> select hostname,from_unixtime(time_start_us/1000000) as last_check ,ping_error as error from monitor.mysql_server_ping_log group by hostname order by time_start_us desc;
+--------------+---------------------+-------------------------------------------------------+
| hostname     | last_check          | error                                                 |
+--------------+---------------------+-------------------------------------------------------+
| 10.77.250.25 | 2021-11-18 21:01:07 | Can't connect to MySQL server on '10.77.250.25' (115) |
| 10.77.250.26 | 2021-11-18 21:01:07 | NULL                                                  |
| 10.77.250.27 | 2021-11-18 21:01:07 | NULL                                                  |
+--------------+---------------------+-------------------------------------------------------+

Please help!

score 1 · Answer 1 · answered Nov 19 '21 at 04:35

I should look in runtime_mysql_servers, not mysql_servers.

mysql> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 10.77.250.27 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 10.77.250.27 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 10.77.250.26 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 10.77.250.25 | 3306 | 0         | SHUNNED | 1      | 0           | 100             | 30                  | 0       | 30             |         |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

ProxySQL backends always online never shunned

1 Answers1