Let's say I have three Galera nodes:

• 172.17.0.2
• 172.17.0.3
• 172.17.0.4

and we will use the following hostgroups:

• writer_hostgroup=0
• reader_hostgroup=1
• backup_writer_hostgroup=2
• offline_hostgroup=4

I plan to configure mysql_galera_hostgroups to enforce max_writers=1, meaning only one node will act as a writer. Also, all servers will share the default weight=1.

But before doing that I need to add the nodes to mysql_servers table, to initially assign them to hostgroup_id. However,there are different ways to do that:

Option 1 (All as writers initially)

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '172.17.0.2', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '172.17.0.3', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '172.17.0.4', 3306);

Option 2 (Two in writer, one in reader)

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '172.17.0.2', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '172.17.0.3', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.17.0.4', 3306);

Option 3 (All as readers initially)

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.17.0.2', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.17.0.3', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.17.0.4', 3306);

My Questions:

• Does the initial assignment of nodes in mysql_servers affect ProxySQL’s ability to manage writers, backups, and readers correctly? Does it make difference if I initially put them all in writer_hostgroup or all in reader_hostgroup or two in writer_hostgroup and one in reader_hostgroup... ?
• If I initially put two nodes in the writer_hostgroup and one in the reader_hostgroup and then in mysql_galera_hostgroups I set max_writers=1 - will ProxySQL ignore which node is in which hostgroup and just randomly select one to be the writer? (so, the third one that is in the reader_hostgroup can end up being writer, while other two that were initially in the writer_hostgroup end up being readers?
• Is there a best practice for initial assignments of nodes in mysql_servers to avoid potential failover or routing issues?