MySQL Router Working Principles

MySQL MGR implements data replication between multiple databases and ensures data consistency in multi-primary mode. In single-primary mode, if the primary database becomes unavailable, MGR automatically selects a new primary. The IP address or port number of the new primary database may change. However, front-end applications cannot identify the change and still connect to the original primary database, so the connection fails.

MySQL InnoDB Cluster is a complete HA solution for MySQL databases based on MySQL MGR, MySQL Router, and MySQL Shell. MySQL Router is part of InnoDB Cluster. It is lightweight middleware that provides transparent routing between applications and back-end MySQL servers.

MySQL Shell is a management tool for MySQL. It obtains MySQL InnoDB Cluster information and generates a JSON file. According to this JSON file, MySQL Router connects to a server in MySQL InnoDB Cluster and reads cluster status in real time, for example, the role is switched or a node is removed from the cluster. The status information is cached in the server memory of MySQL Router, according to which MySQL Router routes connections from applications to an available MySQL database server. In this way, applications only need to connect to MySQL Router without caring about the IP addresses or ports where the available MySQL servers are running.

HA for MySQL Router is required if only one MySQL Router instance is deployed in a MySQL HA solution. HA for MySQL Router is achieved by using Keepalived. In addition to HA, MySQL Router Cluster nodes support read/write splitting. That is, write operations are routed to the primary node, and read operations are routed to the secondary node. If there are multiple secondary databases, MySQL Router can route read operations to different secondary nodes to implement load balancing.

For non-MGR primary/secondary solutions, MySQL Router also provides the load balancing function. However, it cannot identify the primary/secondary role change in real time. MySQL Router routes an application connection to the available server specified in the configuration file. If the target MySQL server is unavailable, MySQL Router returns a connection failure message to the application. If it is a new connection, MySQL Router directly routes it to an available MySQL server.