Smarter Query Routing with MySQL Routing Guidelines

With the release of MySQL Shell 9.2 and MySQL Router 9.2, MySQL adds Routing Guidelines , a new declarative feature that makes query routing in MySQL architectures more dynamic, flexible, and context‑aware.

Both the Community and Enterprise editions provide this capability, allowing administrators to customize routing based on application needs, ensuring scalable and resilient performance across InnoDB Cluster, ClusterSet, and ReplicaSet topologies.

1. Need for Smarter Query Routing

Modern database deployments require efficient routing to achieve performance, scalability, elasticity, and adaptability. MySQL Router acts as middleware, directing client connections to appropriate MySQL instances, but as topologies grow, the default routing behavior becomes insufficient for diverse workloads.

2. Limitations of Traditional Routing

Traditional routing applies a single read/write rule to all connections, leading to higher latency, uneven load distribution, and lack of intelligent fail‑over. It cannot differentiate traffic by user, application, platform, or query type, which is essential for multi‑region clusters, read‑scale workloads, testing environments, multi‑tenant applications, and compliance‑driven routing.

3. Routing Guidelines: A New Paradigm

Routing Guidelines let administrators define declarative rules that consider server attributes, client session properties, and router information. This enables fine‑grained control such as routing to the nearest node, balancing load dynamically, and handling fail‑over without manual intervention.

4. How It Works

Routing Guidelines are defined via the MySQL Shell AdminAPI and consist of two key components:

Destinations – pattern‑matched groups of MySQL instances (e.g., primary, secondary, read replica).

Routes – rules that match client sessions to destinations based on schema, user, source IP, target port, etc.

Example JSON definition (wrapped in tags):

{
  "destinations": [
    {"match": "$.server.memberRole = PRIMARY AND $.server.clusterRole = PRIMARY", "name": "Primary"},
    {"match": "$.server.memberRole = SECONDARY AND $.server.clusterRole = REPLICA", "name": "ReplicaClusterSecondary"},
    {"match": "$.server.memberRole = READ_REPLICA AND $.server.clusterRole = REPLICA", "name": "ReplicaClusterReadReplica"}
  ],
  "name": "default",
  "routes": [
    {
      "connectionSharingAllowed": true,
      "destinations": [{"classes": ["Primary"], "priority": 0, "strategy": "round-robin"}],
      "enabled": true,
      "match": "$.session.targetPort = $.router.port.rw",
      "name": "rw"
    },
    {
      "connectionSharingAllowed": true,
      "destinations": [
        {"classes": ["ReplicaClusterSecondary", "ReplicaClusterReadReplica"], "priority": 0, "strategy": "round-robin"},
        {"classes": ["Primary"], "priority": 1, "strategy": "round-robin"}
      ],
      "enabled": true,
      "match": "$.session.targetPort = $.router.port.ro",
      "name": "ro"
    }
  ],
  "version": "1.0"
}

The destinations classify servers by role, while the routes direct read/write traffic (rw) to primaries and read‑only traffic (ro) to secondaries or read replicas, falling back to the primary if needed.

5. Matching Expressions

Routing Guidelines rely on matching expressions that can use logical operators (AND, OR, NOT), variables (e.g., $.server.memberRole , $.session.sourceIP ), and built‑in functions such as NETWORK() , CONTAINS() , and REGEXP_LIKE() to create sophisticated routing logic.

6. Workflow

Classify destinations based on server attributes.

Match incoming client sessions to routes using expressions.

Apply routing strategies (first‑available, round‑robin) to selected destinations.

Continuously monitor topology changes and update routing dynamically.

7. Advantages

Declarative routing without application changes.

Dynamic adaptability to session attributes.

Fine‑grained control over query distribution.

Seamless integration with MySQL Shell and AdminAPI.

Support for Cluster, ClusterSet, and ReplicaSet deployments.

8. Deployment Considerations

Routing Guidelines require MySQL Shell and MySQL Router 9.2 or later but are independent of the MySQL Server version, making them compatible with a wide range of deployments. All configuration is stored centrally in MySQL, keeping routers stateless.

9. Conclusion

Routing Guidelines unlock a new level of flexibility for MySQL query routing, enabling administrators to optimize performance, scalability, and resilience across diverse MySQL deployments with minimal effort.