Setup proxysql read-write split with both galera cluster and master slave replication

Question

I have the following setup:

1. Three node galera cluster (galera-1, galera-2, galera-3)
2. galera-2 is master to slave-1 (master-slave replication)
3. slave-1 is master to both slave-2 and slave-3
4. slave-1, slave-2 and slave-3 have read_only = 1

I have the following ProxySQL configuration:

mysql_servers = (
    {   address = "galera-1"   port = 3306   hostgroup = 2   },
    {   address = "galera-2"   port = 3306   hostgroup = 2   },
    {   address = "galera-3"   port = 3306   hostgroup = 2   },
    {   address = "galera-2"   port = 3306   hostgroup = 5
        max_replication_lag = 5   },    
    {   address = "slave-1"    port = 3306   hostgroup = 5
        max_replication_lag = 5   },
    {   address = "slave-2"    port = 3306   hostgroup = 5
        max_replication_lag = 5   },
    {   address = "slave-3"    port = 3306   hostgroup = 5
        max_replication_lag = 5   }
)
mysql_galera_hostgroups = (
    {
        active = 1

        backup_writer_hostgroup = 4
        max_transactions_behind = 100
        max_writers = 1
        offline_hostgroup = 1
        reader_hostgroup = 3 
        writer_hostgroup = 2
        writer_is_also_reader = 0

    }
)
mysql_replication_hostgroups = (
    {
        writer_hostgroup = 5
        reader_hostgroup = 6
    }
)

With this configuration:

• Available servers for writing:
  • hostgroup 2 (I will always be routing to this one)
  • hostgroup 5
• Available servers for reading:
  • hostgroup 3
  • hostgroup 4
  • hostgroup 6

How can I route read queries to hostgoups 3, 4 or 6 in a round-robin fashion?

mysql_query_rules = 
(
    {
        rule_id = 100
        active = 1
        match_pattern = "^SELECT .* FOR UPDATE"
        destination_hostgroup = 2
        apply = 1
    },
    {
        rule_id = 200
        active = 1
        match_pattern = "^SELECT .*"
        destination_hostgroup = 6
        apply = 1
    },
    {
        rule_id = 300
        active = 1
        match_pattern = ".*"
        destination_hostgroup = 2
        apply = 1
    }
)

I've tried mixing groups between Galera and MySQL replication but things get mixed up quite badly.

Is there a way to create a hostgroup of hostgroups so I can route to the "group of groups"?

Answer 1

Investigating a bit, I have found a possible solution, but I find it a bit an overkill. Installing 3 ProxySQL servers in order to read/write split between Galera and master/slave replicas.

1. Configure ProxySQL 1 to balance between ProxySQL 2 and 3.
2. ProxySQL 2 configured with a read/write split between Galera nodes
3. ProxySQL 3 configured with a read/write split between master and replicas

Any comments? It would be great to be able to achieve this with just one ProxySQL server.