Can Proxy Listen on Multiple Ports and Proxy Connections to Groups of Back-End Servers Based on the Listening Port?

[Eric Robinson]

Newbie question.

Let’s say we have 100 MySQL servers deployed in 50 x 2-node clusters, each serving a separate database. The nodes in each cluster listen on the same port. Cluster1 consists of Server1A and Server1B, both listening on port 5001. Cluster2 consists of Server2A and Server2B, both listening on port 5002, and so on.

We want ProxySQL to listen on ports 5001, 5002, 5003, 5004, etc. When a connection comes in to port 5001, it should route the request to Server1A or Server1B. When a connection comes in to port 5002, it should route the request to Server2A or Server2B. And so on.

I don’t see how to set this up with ProxySQL. It looks like it is only designed to provide 1 front-end listener, and all the back-end servers are serving the same database.

[bpani...@zenefits.com]

Try using `proxy_port` and configure your rules in `mysql_query_rules` for routing use the flag_in and flag_out fields if you have many rules and want to enable chaining of rules. 
Hope this helps!

Thanks, 

Bajrang

[René Cannaò]

ProxySQL can listen on multiple IP:ports , configuring `mysql-interfaces` variable:

https://proxysql.com/Documentation/global-variables/mysql-variables/#mysql-interfaces

As pointed out by Bajrang, you can then route traffic based on proxy_port (see https://proxysql.com/documentation/main-runtime/#mysql_query_rules). If you want to leverage features like read/write split (as each cluster has 2 nodes), you probably need to create similar rules for each cluster.

"each serving a separate database" : here, do you mean schema? If yes, having proxysql listening on multiple ports doesn't seem to be the right approach.

You could let all clients connect to the same port, and use the schemaname to perform the routing.

Table mysql_query_rules_fast_routing is designed for this:

https://proxysql.com/documentation/main-runtime/#mysql_query_rules_fast_routing

https://proxysql.com/blog/new-schemaname-routing-algorithm/

The use of mysql_query_rules_fast_routing will drastically reduce the complexity of your system, and you can potentially only have a few rows in mysql_query_rules: the rules that define read/write split. mysql_query_rules_fast_routing will be responsible for the routing.

I don't know your application so what I am going to write next doesn't have a lot of context, but my first impression is that listening on 50 ports seems like a bad design.

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/14fb0134-b84c-471f-b182-4af40b0b6bb9n%40googlegroups.com.

[Eric Robinson]

Thanks much for the feedback, I will look into those options!