Use MySQL Router with MySQL Server in single primary group replication

Question

We use MySQL Server 8 with three nodes in a group replication on single primary mode. The server is running under windows. To communicate with the servers we use MySQL Router 8.

The problem is, that the router doesn't know which of the three nodes is the one which can write. So there ist always a chance the promt returns "The MySQL server is running with the --super-read-only option".

How do I configure the router correctly to send all writing commands to the actual donor server? If this is not possible, is there a way to configure a second router which takes always the primary server?

Edit: Here is the content of mysqlrouter.conf:

[DEFAULT]
user = routing
logging_folder = D:\MySQL Router\logs

max_total_connections = 1500

[routing:primary]
bind_address = db.lan
bind_port = 3306
destinations = db03.lan:3306, db04.lan:3306, db05.lan:3306
routing_strategy = round-robin

Answer 1

I'll give you a reference configuration of MGR single master mode under node 3:

[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

mysql router：192.168.100.21 master：192.168.100.22 slave：192.168.100.23/24

Answer 2

You could also create a metadata cache for the routers. This allows you to assign the roles primary and secondary to a single destination. This way each router knows the current cluster topology, and therefore also knows the current primary node.

You can set it up manually but I usually use the MySQL Shell for that purpose on a cluster node.

For reference, see this router config.