Hostgroup id different on mysql_servers and runtime_mysql_servers

Hello,

I am exploring ProxySQL and I’ve installed it on Centos 7, My ProxySQL version is 2.0.6-73-gc746bf7.

I’ve mysql galera setup already there in 3 different VMs. [192.168.43.50, 192.168.43.51, 192.168.43.52]

I’ve installed ProxySQL on 192.168.43.50 server, and i want to configure split read/write operation between these nodes. so I’ve added these two nodes [.50 & .52] to the hostgroup ID - 3 [reader_hostgroup] & one node [51] to the hostgroup id -2 [writer_hostgroup]

so i’ve added mysql server as below.

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (3,‘192.168.43.50’,3306);

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,‘192.168.43.51’,3306);

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (3,‘192.168.43.52’,3306);

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

after this whenver i am checking mysql server its showing different hostgroup id for .50 node. in the runtime its showing that .50’s hostgroup id is 0, but i’ve added that node under hostgeoup_id 3.

ProxySQL> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;

±-------------±--------------±-----±-------±-------+

| hostgroup_id | hostname | port | status | weight |

±-------------±--------------±-----±-------±-------+

| 2 | 192.168.43.51 | 3306 | ONLINE | 1 |

| 3 | 192.168.43.52 | 3306 | ONLINE | 1 |

| 3 | 192.168.43.50 | 3306 | ONLINE | 1 |

±-------------±--------------±-----±-------±-------+

ProxySQL> SELECT hostgroup_id,hostname,port,status,weight FROM runtime_mysql_servers;

±-------------±--------------±-----±--------±-------+

| hostgroup_id | hostname | port | status | weight |

±-------------±--------------±-----±--------±-------+

| 3 | 192.168.43.52 | 3306 | SHUNNED | 1 |

| 0 | 192.168.43.50 | 3306 | ONLINE | 1 |

| 2 | 192.168.43.51 | 3306 | SHUNNED | 1 |

±-------------±--------------±-----±--------±-------+

ProxySQL> select * from mysql_galera_hostgroups;

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±----------------

--------±--------+

| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions

_behind | comment |

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±----------------

--------±--------+

| 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100

   |        |

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±----------------

--------±--------+

Could you please help me here, what’s wrong here ? or i missed something here ? please guide.

Advance Thanks for the help.

1 Like

Hi @abhi_rathod, it looks like proxysql is unable to connect to your PXC nodes. Did you properly configure the ‘monitor’ user within proxysql and on your PXC? Have you looked at proxysql’s error log in /var/lib/proxysql/ ?

Any errors here?

SELECT hostname, connect_error FROM mysql_server_connect_log ORDER BY time_start_us DESC
1 Like