Question: mysql_replication_hostgroups Topology

[MichalisDBA]

Hello.

May i ask. We have 1 Master and 5 Replicas.

We want to create 3 Hostgroups.

Hostgroup with ID 1 will have the Master node.
Hostgroup with ID 2 will have 2 specific Replicas.
Hostgroup with ID 3 will have the rest 3 Replicas.

In mysql_replication_hostgroups table i can not insert duplicate writer_hostgroup. How to achieve something like this?

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (1, 2, 'read_only', '1 Master / 2 Slaves');
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (1, 3, 'read_only', '1 Master / 3 Slaves');

Is there a specific reason writer_hostgroup is Primary Key and can not have duplicate Hostgroup ID?

[renecannao]

Hi @MichalisDBA .

I am happy to answer this question!
In mysql_replication_hostgroups you define pairs of hostgroups : for example, you define a pair where 1=writer and 2=reader .
This means that:

• hostgroup 1 is the writer hostgroup of hostgroup 2 , and
• hostgroup 2 is the reader hostgroup of hostgroup 1

This creates a 1-to-1 hostgroups mapping .
If you were allowed to create another hostgroup with (1,3) , that hostgroup 1 would be the writer hostgroup of ... two hostgroups? This would break the 1-to-1 mapping.

But then, you can rightfully ask why the 1-to-1 mapping is important.
From a design point of view, in proxysql:

• a hostgroup is a set of servers that have the same logical functionality
• a replication hostgroup is a pair of hostgroup where servers can move from a hostgroup to another hostgroup based on read_only value. In other words, a replication hostgroup is a superset of a hostgroup: a set of servers that have the same logical functionality, but grouped based on read_only.

Hostgroup with ID 2 will have 2 specific Replicas.
Hostgroup with ID 3 will have the rest 3 Replicas.

From your own classification, I would assume that the "2 specific replicas" have a different logical functionality than the "rest 3 replicas" : this is why you put them in the different groups!

Let's assume you were able to create two replication hostgroups, (1,2) and (1,3) , and the server in hostgroup 1 changes from read_only=0 to read_only=1 , thus it needs to be moved to its reader hostgroup: which one, 2 or 3 ?
This is why each writer_hostgroup must have only one reader_hostgroup, it must be deterministic: a server must be moved to its reader_hostgroup, it cannot be moved to more than 1 reader_hostgroup.

Btw:

• nothing stops you from having another hostgroup with with the same server in hostgroup , and create another pair with hostgroup 3
• unless you want any of the servers in hostgroup 3 to be promoted as master, probably you don't want to create a replication_hostgroup with hostgroup 3

[MichalisDBA]

Thank you very much @renecannao. Indeed the servers in Hostgroup with ID 3 would be read_only replicas, they will not be promoted to Master. So i will continue without defining the Hostgroup 3 to mysql_replication_hostgroups.

Thank you again.

[renecannao]

Hi @MichalisDBA .
You are welcome!
Re-opening this issue and tagging it with a documentation label. We should improve our documentation on this topic