With the 9.2.0 Innovation Release of MySQL Shell and MySQL Router, we are introducing Routing Guidelines - a new feature that makes query routing in MySQL database architectures more dynamic, flexible, and declarative. 

Available in the MySQL Community and Enterprise Editions, this feature enables tailored query routing for specific application needs and ensures seamless query distribution across MySQL InnoDB Cluster, ClusterSet, and ReplicaSet topologies. By leveraging Routing Guidelines, administrators gain greater control over query flow, optimizing performance and availability in a scalable and resilient manner.

The Need For Smarter Query Routing

In modern database architectures, efficient query routing is essential for achieving performance, scalability, resilience, and adaptability.

MySQL Router plays a central role in MySQL architectures, acting as a middleware that dynamically directs client connections to the appropriate MySQL server instances. It fully integrates seamlessly into MySQL deployments, enabling failover, load balancing, and query distribution.

However, as MySQL topologies grow in complexity, the need for more control over connection routing becomes more apparent.

While effective for simpler deployments, MySQL Router’s default routing behavior may no longer be sufficient to accommodate specific requirements needed to handle diverse scenarios.

Where Traditional Routing Falls Short

Geographically Distributed Topologies

Consider a ClusterSet spanning multiple regions, such as a Primary Cluster in one region and a Replica Cluster in another.

With traditional routing, MySQL Router applies only basic read/write rules, leading to suboptimal query routing that results in:

• Higher latency - as queries are not directed to the nearest region.
• Inefficient workload balancing - creating hotspots while underutilizing some nodes.
• Lack of intelligent failover - as traditional routing does not account for workload priorities.

Read Scale-Out and Testing Environments

Now, consider a topology consisting of an InnoDB Cluster with multiple Read Replicas, used both for Read Scale-Out and testing newer MySQL versions. Different applications have distinct requirements:

• App X - Handles frontend traffic, requiring low-latency reads.
• App Y - Performs resource-intensive, read-only queries, which should be offloaded to dedicated replicas.
• App Z - Runs tests on a newer MySQL version (9.2 as shown in the diagram), requiring isolation from production workloads
   

Traditional routing is unable to differentiate between traffic based on specific users, applications, platforms, or query types.

It applies the same read/write classification to all connections, making it impossible to ensure that frontend traffic is routed to the secondaries, resource-heavy queries are directed to appropriate read replicas, and test queries are isolated on specific MySQL versions without interfering with production workloads.

Multi-Tenant Applications and Sharding

Another example is multi-tenant applications, where queries from different customers should be routed to specific databases or schemas.

Without session awareness, MySQL Router lacks the ability to enforce tenant-based query routing without complex application-side logic.

Other Real-World Challenges

Many real-world workloads have specialized routing needs that traditional routing cannot handle, such as:

• Workload prioritization - Directing queries based on CPU, memory usage, or query type.
• Compliance-driven routing - Ensuring sensitive data is accessed only from specific regions (e.g., GDPR, CCPA).
• Network-aware routing - Directing queries based on the client's origin, such as internal vs. external traffic.

These limitations illustrate why a more adaptable and context-aware routing solution is necessary.

Routing Guidelines: A New Paradigm for Query Routing in MySQL

To solve these challenges, MySQL database architectures are introducing Routing Guidelines - a feature designed to dynamically adjust query routing using declarative rules that account for various factors.

Unlike traditional automatic routing, Routing Guidelines allow administrators to define declarative rules that intelligently distribute queries across MySQL instances based on:

• Server properties (e.g., replica vs. primary)
• Client session attributes (e.g., database schema, user name, or client attributes)
• Router specifics (e.g., RW port, hostname)

This provides a customizable, rule-driven approach that can be used for various purposes, such as:

• Minimizing latency by routing queries to the nearest or most efficient database node.
• Enhancing load balancing by dynamically distributing queries.
• Improving failover handling, ensuring queries are always routed to available resources without manual intervention.

By providing fine-grained control, Routing Guidelines allow MySQL Router to make more intelligent decisions tailored to an application's specific needs.

How It Works

Routing Guidelines are defined using the AdminAPI in MySQL Shell, allowing administrators to declaratively create and manage routing rules.

A Routing Guideline consists of two key components:

1. Destinations - Define groups of MySQL instances within the topology using pattern-matching expressions. Destinations act as pools of candidate instances, ensuring that queries are routed only to servers matching specific criteria.
2. Routes - Determine how client sessions are matched to the appropriate destination. Using expressions, Routes classify client sessions and direct them to the most suitable MySQL servers based on factors like schema, user, or workload type.

Example Routing Guideline:

{
    "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"
}

In this Routing Guideline:

• Destinations classify servers into three groups based on their member role:
  • Primary server - $.server.memberRole = PRIMARY AND $.server.clusterRole = PRIMARY
  • Replica Cluster Secondary servers - $.server.memberRole = SECONDARY AND $.server.clusterRole = REPLICA
  • Replica Cluster Read Replicas - $.server.memberRole = READ_REPLICA AND $.server.clusterRole = REPLICA

• Routes define how client sessions are matched to these destinations:
  • The "rw" route directs read/write traffic to the Primary server.
  • The "ro" route prioritizes the Replica Cluster's Secondary and Read Replica servers while balancing traffic in a round-robin fashion. If none of those nodes are available, it falls back to the Primary server as a last resort.

This example demonstrates how Routing Guidelines provide fine-grained control over query routing, enabling efficient load balancing and failover management across the topology.

Matching Expressions: The Core of Routing Guidelines

Routing Guidelines rely on Matching Expressions, which serve as the rules that govern query routing. These expressions allow administrators to:

• Identify servers, sessions, or routers using logical conditions.
• Define matches with variables, operators, functions and values.
• Combine conditions using AND, OR, and NOT for advanced flexibility.

By leveraging Matching Expressions, Routing Guidelines offer precise and dynamic query routing, ensuring that each query is handled by the most appropriate MySQL instance.

Common Matching Expressions and Their Usage

In the example above, we used Matching Expressions with the following predefined variables:

However, many other predefined variables and functions are available.
 

In addition to predefined variables, Routing Guidelines support various built-in functions that allow for more advanced routing logic.

These functions enable operations such as matching network subnets, string manipulation, and evaluating regexes.

Here are a few examples:

These are just a few examples of the functions available in Routing Guidelines. The full list includes many more.

Practical Examples of Matching Expressions

Below are some examples of Matching Expressions with their explanations:

Route Queries Based on Schema

"match": "$.session.schema = 'analytics' AND $.session.user = 'reporting_user'"

This rule ensures that all queries targeting the "analytics" schema coming from the user "reporting_user" are routed according to the corresponding Routing Guideline.

Route Queries Based on Network Subnets

"match": "NETWORK($.session.sourceIP, 24) = NETWORK('192.168.1.0', 24) OR
          NETWORK($.session.sourceIP, 8) = NETWORK('10.0.0.0', 8)"

This rule matches client IP addresses based on their network subnets:

• The first condition checks if the client's IP belongs to the 192.168.1.0/24 subnet (matching clients in a local office or data center).
• The second condition checks if the client belongs to the 10.0.0.0/8 subnet (matching corporate VPN or internal network).

If either condition is true, the query is routed to a specific set of servers.

Route Backup Operations to a Dedicated Server

"match": "$.session.connectAttrs.program_name = 'mysqldump'"

This rule detects when MySQL Dump (mysqldump) is being used as a client. Queries from mysqldump will be routed to a specific server (e.g., a backup server) to avoid impacting the performance of primary database nodes.

Define a Destination for Staging Servers Running MySQL 9.2.0

"match": "$.server.tags.environment = 'staging' AND $.server.version = 90200"

This rule creates a Destination that includes only MySQL servers running in the staging environment (tagged) AND specifically on MySQL version 9.2.0 (90200).

Workflow

Routing Guidelines operate through a structured workflow that ensures queries are dynamically routed to the most suitable MySQL instances based on real-time conditions. The process consists of the following generic steps:

1. Classify Destinations
   • Groups MySQL servers into destination classes based on pattern-matching expressions.
     • A server can belong to multiple destination classes (a one-to-many relationship).
2. Match Route Rules
   • Classifies incoming client requests and maps them to predefined routes.
     • A route can have multiple candidate destination classes (a one-to-many relationship).
3. Apply Routing Strategy
   • Determines how queries are distributed among the selected destinations.
   • Supports two routing strategies:
     • first-available - Directs queries to the first available server.
     • round-robin - Distributes queries evenly across available servers.
4. Monitor Topology
   • Continuously reclassifies servers based on real-time topology changes.
   • Updates routes dynamically to reflect changes in server availability.
   • Disconnects invalid connections if a destination is no longer valid.

Key Benefits

Routing Guidelines offer a powerful and flexible approach to query routing, giving administrators precise control over how queries are directed across MySQL architectures. Here are some of the key advantages:

• Declarative Routing - Define routing rules in a structured way without modifying applications.
• Dynamic Adaptability - Routes can be based on session attributes, enabling intelligent query distribution.
• Granular Control - Allows fine-tuned control over query routing behavior.
• Integration with MySQL Shell and AdminAPI - Easily manage Routing Guidelines via MySQL Shell.
• Support for Cluster, ClusterSet, and ReplicaSet - Fully integrated with the supported MySQL Database Architectures.

Deployment Considerations & Requirements

MySQL Router is designed to be used as a stateless connection extension since all configurations are stored directly in MySQL.

This also ensures that Routing Guidelines remain flexible and centralized, avoiding unnecessary complexities in Router configuration files.

Additionally, Routing Guidelines require MySQL Shell and MySQL Router 9.2.0 or later, but they do not depend on a specific MySQL Server version, making them compatible across different deployments.

Conclusion

Available in both MySQL Community and Enterprise editions, Routing Guidelines unlock a new level of flexibility in MySQL query routing, making it easier than ever to optimize performance, scalability, and resilience across different MySQL deployments.

This introduction lays the foundation for understanding Routing Guidelines. In the next post, we'll explore how to configure and manage them using MySQL Shell's AdminAPI, with practical examples and use cases. Stay tuned!

For more details, check out the official MySQL documentation or try Routing Guidelines with MySQL Shell and MySQL Router today.