I’ve doing an POC on ProxySQL with PXC. So far so good, and for now doing very basic testing this works.
We have multiple independant clusters (having the same kinda of data), lets call them DBC01,DBC02,DBC03 etc.
Is it possible having an single ProxySQL instance (or multiple for failover like ProxySQL Cluster) managing these. I cannot see this done, especially since we need to configure the mysql_galera_hostgroups table in ProxySQL.
So the only way I see this is doing, is this true ?
It is extremely important that the data between the databases are not mixed up, even though the structure is similar in them, but not 100% identical. Some schemas might differ a bit e.g.
ProxySQL1→ DBC01
ProxySQL2→ DBC02
ProxySQL3→ DBC03
And not one to manage them all.
ProxySQL1→(DBC01,DBC02,DBC03).
This is the correct way to use ProxySQL for your different clusters: keep a separate ProxySQL for a separate PXC cluster configuration and routing traffic.
@lbmdkap You can use a single proxysql to all 3 clusters. You need to assign different hostgroup ids to each cluster as follows:
DBC01: hostgroup 10 (writer), 11 (reader)
DBC02: hostgroup 20 (writer), 21 (reader)
DBC03: hostgroup 30 (writer), 31 (reader)
Then, add all 9 servers to mysql_servers table, with proper hostgroup ids. Then add all 3 clusters to mysql_galera_hostgroups table, again, using correct hostgroups.
Thanks. With the current setup/POC we are doing, if we have lets say 30 databases, this would require us to have ~30 instances just of running ProxySQL which is a bit steep. Im aware we can deploy it locally on application servers, but that would potentially for now require us to do too many changes. We might look at this later.
Interesting. I got an failure when I tried this, and this was why I posted here. But now it worked. Not sure what went wrong, but for sure I was doing something wrong.
@lbmdkap You might want to also look at query rules based on proxy_port to further isolate the clusters. Modify mysql-interfaces to listen on, for example, 3306, 3307, 3308 and then make rules for each port to route to hg10/11, hg20/21, etc
