I have setup MySQL InnoDB Cluster latest release (8.0.27) with three nodes with single primary. And I have a VB script and connection string for it. Current connection string is like :

jdbc:mysql://node1,node2,node3;db=test?multi_host=true

Assume my primary node1 goes down, R/W access will be passed onto either node2 or node3 becoming primary. During this my connection string won't work as it tries to connect to first node and fails. Is there any other parameter which can be passed in connect string to handle such issues? How does connect string gets to know which node is primary and connect to it.

Thanks.

CodePudding user response：

An InnoDB Cluster usually runs in a single-primary mode, with one primary instance (read-write) and multiple secondary instances (read-only).

In order for client applications to handle failover, they need to be aware of the InnoDB cluster topology. They also need to know which instance is the PRIMARY. While it is possible for applications to implement that logic, MySQL Router can provide this functionality for you.

shell> mysqlrouter --bootstrap root@localhost:3310

MySQL Router connects to the InnoDB cluster, fetches its metadata and configures itself for use. The generated configuration creates 2 TCP ports: one for read-write sessions (which redirect connections to the PRIMARY) and one for read-only sessions (which redirect connections to one of the SECONDARY instances).

Once bootstrapped and configured, start MySQL Router (or set up a service for it to start automatically when the system boots):

shell> mysqlrouter &

You can now connect a MySQL client, such as MySQL Shell to one of the incoming MySQL Router ports and see how the client gets transparently connected to one of the InnoDB cluster instances.

shell> mysqlsh --uri root@localhost:6442

However, when primary node fails, you can just read data and write cannot work. If you want write to work, see High Availability and Multi-Primary cluster for MySql.

See this for more detail.