- Three node galera cluster (galera-1, galera-2, galera-3)
- galera-2 is master to slave-1 (master-slave replication)
- slave-1 is master to both slave-2 and slave-3
- 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"?
mysql_replication_hostgroups.reader_hostgroup
to 3 (the same asmysql_galera_hostgroups.reader_hostgroup
), the Galera monitor will move them tomysql_galera_hostgroups.offline_hostgroup
. The idea was to have both Galera readers and replication readers in the same hostgroup.