Second cluster port based routing help

Stuart_Alexander · May 9, 2024, 2:59pm

Hello,

I’m trying to get some help on how to setup port based routing to a secondary cluster.
Admin (6032)
Cluster1 (6033)
Cluster2 (6034)

Current Setup:
I have 2 ProxySQL servers load balanced
I have 1 cluster of 2 DB servers (Read/Write & Read Only) (Hostgroups 10 &11 Port 3306)

What I need
2 ProxySQL servers load balanced
2 clusters with 2 DB servers each (Read/Write & Read Only) Hostgroups 10 & 11 Port 3306 and Hostgroups 20 & 21 on port 3306

The second cluster will have the same users as cluster1 so I believe I cannot use the user to define the cluster to connect to.

Example:
I have ProxySQL setup on cluster 1 (connecting via 6033) working fine with rules for read/write split however I want to add a second cluster and route traffic to it when the incoming port is 6034

The rules I have for cluster 1 do not define a port (proxy_port: NULL) so would these need defined to use 6033 and then duplicate the rules for 6034?

What is the best way to add the second cluster and be able to send queries to it?

Do I need to configure a new listener or does ProxySQL work this out from the hostgroup & rules ports?
if so how do I add a new listener? Would it be something like:

update global_variables set variable_value=‘0.0.0.0.:6033;0.0.0.0:6034’ where variable_name=‘mysql-interfaces’;
save mysql variables to disk;
proxysql stop;
load mysql variables to runtime;
proxysql start;

Thanks in advance for the help.

Ivan_Groenewold · May 13, 2024, 12:05pm

Hi, indeed you have to configure a separate listener like your example above. Next use query rules for routing users to the proper cluster using the proxy_port field, which maps to the listener port of the proxy. See How to set up ProxySQL Read/Write Split - ProxySQL

Stuart_Alexander · May 14, 2024, 3:23pm

I have set the rules and listener and also set the new hostgroups 20/21 but connecting to port 6034 still shows me the databases for Hostgroups 10/11 Any idea what I may have missed?

Rules: New ones are 4,5,6

*************************** 1. row ***************************
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6033
digest: NULL
match_digest: NULL
match_pattern: -- FORCE-WRITER\s*SELECT.*
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 10
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL
*************************** 2. row ***************************
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6033
digest: NULL
match_digest: ^SELECT.*FOR UPDATE$
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 10
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL
*************************** 3. row ***************************
rule_id: 3
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6033
digest: NULL
match_digest: ^SELECT
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 11
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL
*************************** 4. row ***************************
rule_id: 4
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6034
digest: NULL
match_digest: NULL
match_pattern: -- FORCE-WRITER\s*SELECT.*
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 20
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL
*************************** 5. row ***************************
rule_id: 5
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6034
digest: NULL
match_digest: ^SELECT.*FOR UPDATE$
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 20
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL
*************************** 6. row ***************************
rule_id: 6
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6034
digest: NULL
match_digest: ^SELECT
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 21
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: NULL
apply: 1
attributes:
comment: NULL

Hostgroups:

*************************** 1. row ***************************
hostgroup_id: 10
hostname: 172.30.11.167
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 11
hostname: 172.30.10.249
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 11
hostname: 172.30.11.167
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 4. row ***************************
hostgroup_id: 20
hostname: 172.30.10.15
port: 3306
gtid_port: 0
status: Online
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Master A - Writer
*************************** 5. row ***************************
hostgroup_id: 21
hostname: 172.30.10.4
port: 3306
gtid_port: 0
status: Online
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Master B - Reader
*************************** 6. row ***************************
hostgroup_id: 21
hostname: 172.30.10.15
port: 3306
gtid_port: 0
status: Online
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Master A - Reader

Do I need to add a new proxysql_admin.cnf for the second cluster?

Topic		Replies	Views
Multiple Front-End Proxy Listeners with Different Back-End Servers? ProxySQL	12	3070	June 25, 2024
Question about the open ports Percona XtraDB Cluster 5.x community , mysql , percona	3	953	July 30, 2023
Balancing proxysql rules Other MySQL® Questions	1	52	September 5, 2024
Question about backend load balancing within a hostgroup ProxySQL	1	1189	July 7, 2021
Proxysql-admin utility ProxySQL	1	300	March 25, 2024

Second cluster port based routing help

Related topics