2

I have the following setup: Galera cluster and master-slave replication 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"?

2
  • If I change the mysql_replication_hostgroups.reader_hostgroup to 3 (the same as mysql_galera_hostgroups.reader_hostgroup), the Galera monitor will move them to mysql_galera_hostgroups.offline_hostgroup. The idea was to have both Galera readers and replication readers in the same hostgroup.
    – supercoco
    Apr 5, 2023 at 15:05
  • I read about having more than one ProxySQL, would this be a solution? ProxySQL 1 to balance between ProxySQL 2 and 3. ProxySQL 2 with a read/write split with the Galera Nodes and PorxySQL 3 with a read/write split between the master (galera-2) and the replicas. Is this an overkill?
    – supercoco
    Apr 10, 2023 at 14:21

1 Answer 1

0

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.

enter image description here

  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.