Can proxysql have multiple listener?

[Son Nguyen]

I have 3 clusters 

- 1 master - slave

- 2 galera. 

For each cluster I need it to listen in a separate VIP. And for each VIP, I have keepalived to watch and maintain the availability. 

I checked, seem proxysql does not support it. So, I want to ask if you consider to add this feature in the next version?

[René Cannaò]

There is no limit to the number of listeners in ProxySQL. You can have multiple listeners configuring variable mysql-interfaces:
https://github.com/sysown/proxysql/wiki/Global-variables#mysql-interfaces

Although, note that ProxySQL is protocol aware and you can use a single listener for thousands of clusters.

Other proxies like HAProxy or MaxScale require multiple listeners to manage multiple clusters.
In ProxySQL this is not a requirement, as most of the time you can perform routing based on other criteria, like username or  schemaname.

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kevin Heatwole]

In ProxySQL this is not a requirement, as most of the time you can perform routing based on other criteria, like username or  schemaname.

How do you route based on schema/database name? I have 150 shard'd schemas/databases that are assigned to up to 9 servers in 3 clusters. Each cluster is responsible for around 50 schemas. Each server in the cluster is responsible for one third of the schemas (all queries for a schema should go to one specific server unless that server is unavailable and then may be sent to one of the other servers in the cluster).

Currently using HAProxy and listening on 150 different ports (one per schema) and routing to the appropriate backend (with the primary server in the cluster being active and the two other servers being for backup). Seems to work well enough, but keeping an eye on ProxySQL for when I need more scale. I would like to have an easy method to move databases between clusters. Currently, the plan is to double the size of a cluster to 6 servers (operate it as a big cluster for a day or two with the 50 schemas being spread over the 6 servers) and then split the cluster into 2 clusters (deleting half the schemas on each cluster).

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

Kevin,

for simplicity, let's call the 3 clusters HG11, HG21 and HG31.

And always for simplicity, the servers are 10.10.X.Y .

And to add some complexity, we will also enable read/write split , where the readers are HG12, HG22 , HG32 .

INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES
(11,"10.10.10.1"),
(12,"10.10.10.1"), (12,"10.10.10.2"), (12,"10.10.10.3"),
(21,"10.10.20.1"),
(22,"10.10.20.1"), (12,"10.10.20.2"), (12,"10.10.30.3"),
(11,"10.10.30.1"),
(12,"10.10.30.1"), (12,"10.10.30.2"), (12,"10.10.30.3");

## enable replication hostgroups

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES
(11,12),(21,22),(31,32);

## create rules for read/write split

INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT) VALUES

(1,1,'^SELECT.*FOR UPDATE',100),
(2,1,'^SELECT',200),
(3,1,'.*',100);

## sharding, sending traffic to masters

INSERT INTO mysql_query_rules (active, flagIN, schemaname, destination_hostgroup, apply) VALUES

(1,100, "shard001", 11, 1),
(1,100, "shard002", 11, 1),
(1,100, "shard003", 11, 1),
(1,100, "shard004", 11, 1),

...
(1,100, "shard050", 21, 1),

(1,100, "shard051", 21, 1),
(1,100, "shard052", 21, 1),
(1,100, "shard053", 21, 1),
(1,100, "shard054", 21, 1),

...
(1,100, "shard100", 21, 1),
(1,100, "shard101", 31, 1),
...
(1,100, "shard150", 31, 1);

## sharding, sending traffic to slaves

INSERT INTO mysql_query_rules (active, flagIN, schemaname, destination_hostgroup, apply)
SELECT 1, 200, schemaname, destination_hostgroup+1 , 1 FROM mysql_query_rules WHERE flagIN=100;

## load everything to runtime:

LOAD MYSQL SERVERS TO RUNTIME;

LOAD MYSQL QUERY RULES TO RUNTIME;

Now ProxySQL can route traffic using only 1 single port, while for HAProxy you would need 300 different ports.

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[Kevin Heatwole]

## sharding, sending traffic to masters
INSERT INTO mysql_query_rules (active, flagIN, schemaname, destination_hostgroup, apply) VALUES
(1,100, "shard001", 11, 1),
(1,100, "shard002", 11, 1),

Thanks for this example. I had read the docs and only saw examples of proxying by username:

https://github.com/sysown/proxysql/blob/master/doc/configuration_howto.md

Perhaps, a proxy by schema name example should be included in the query rules section of the above HowTo... 

[René Cannaò]

Good point!

Another option is to convert this reply into a wiki page and/or blog post

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[Son Nguyen]

But seem the listener has the same privileges, can I set that which listener can be map to which shema?

Vào 15:44:39 UTC+7 Thứ Ba, ngày 14 tháng 2 năm 2017, René Cannaò đã viết:

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

Yes, you can map a listener to a hostgroup.
Using different criteria in mysql_query_rules you can define that traffic coming to one listener should be sent to a specific hostgroup