MySQL Routing Guidelines: Breaking Down the Mechanics in MySQL Router

Welcome to the final post in our MySQL Routing Guidelines series.

In the first two posts (Smarter Query Routing with MySQL Routing Guidelines and MySQL Routing Guidelines: A Practical Guide to Management and Configuration), we introduced the concept of Routing Guidelines and showed how to configure and manage them in MySQL Shell. Now, we’re going under the hood, to explore how MySQL Router applies these rules in real time.

The functionality is built into MySQL Router in both Community and Enterprise editions.
 

Understanding the internals helps you write better Routing Guidelines, debug issues more effectively, and gain confidence that your routing logic is being enforced exactly as intended.

But first, a quick recap: Routing Guidelines give you powerful tools to:

• Customize routing behavior based on connection properties to statically and dynamically adapt to different scenarios.
• Reduce latency by steering traffic based on client location or server availability.
• Implement business logic directly in your routing layer (e.g., tenant-specific routing).
• Enhance resilience by creating custom failover and fallback strategies.

In short, Routing Guidelines give you fine-grained control over query traffic – whether you’re optimizing read distribution, isolating backups, or adapting to topology changes on the fly.

In this final part of the series, we dive deeper into how Routing Guidelines work internally, focusing on the custom expression parser and evaluator that enables complex routing rules.

 

Smarter Routing: How Router Uses Metadata and Routing Guidelines

When building systems that rely on routing decisions – especially in distributed environments – it’s crucial to have fine-grained, adaptive control. MySQL Router solves this using centralized metadata-driven Routing Guidelines that support dynamic updates and multi-level overrides.

 

Metadata: The Backbone of Dynamic Routing in MySQL Router

The purpose of metadata in MySQL Router is to provide dynamic, centralized, and consistent information about the MySQL architectures topology – which allows MySQL Router to make intelligent routing decisions without requiring manual configuration or static knowledge of the environment.

The InnoDB Cluster metadata, coupled with Group Replication dynamic status tables, stores real-time information about the MySQL architectures in the mysql_innodb_cluster_metadata schema.

This includes:

• List of all MySQL server instances
• Role of each cluster and individual server (PRIMARY, SECONDARY, READ REPLICA)
• Hostnames, ports, UUIDs
• Cluster, ClusterSet and ReplicaSet names

 

Why it matters: MySQL Router can automatically adapt to topology changes – e.g., failovers, added nodes, or server removals – without needing to be reconfigured.

You can implement dynamic logic at the routing layer using centralized rules that apply across all Routers in the deployment. MySQL Router reads Routing Guidelines from metadata, allowing:

• Rule-based routing (e.g., send writes to PRIMARY, reads to replicas)
• Fine-grained control over how sessions are routed, based on metadata
• Live updates without restarting the Router

Routing Guidelines can be defined at two levels: per-topology and per individual Router.
The Router uses these definitions hierarchically. Router-specific settings override those at the topology level, giving us the flexibility to apply fine-tuned behavior without rewriting shared logic.

 

Refresh with Intelligence

Routing Guidelines are fetched from metadata, which is refreshed regularly based on a configurable time-to-live (TTL) setting. To ensure performance and accuracy, Routing Guidelines are refreshed only when necessary:

This makes guideline updates safe and atomic – ensuring stability while allowing real-time reconfiguration.

 

Under the Hood: How It Works

When a new Routing Guidelines version is detected, MySQL Router parses it to ensure it follows the expected structure. At the heart of this process is an expression parser – one that understands a domain-specific language designed for routing logic.

In this post, we’ll explore that language: its grammar, the syntax it supports, how it handles arithmetic and logical operations, and the built-in functions it offers to evaluate routing rules. This deep dive will help you understand not just how Routing Guidelines are processed, but also how to write powerful, flexible rules to control routing behavior precisely.

 

The Role of Matching Expressions in Routing Guidelines

Matching Expressions are a core feature of MySQL Router’s Routing Guidelines. They are used to control how incoming connections are routed and how destinations are grouped. Specifically, matching expressions are used in two key places:

1. Destination Grouping – To assign destinations into logical groups called destination classes.
2. Route Matching – To determine which routing rule applies to an incoming connection.

Matching Expressions always evaluate to a boolean (true or false). This outcome decides whether a specific configuration entry is considered during routing:

• A destination is included in a destination group if the matching expression associated with that group evaluates to true for the destination. Since destinations can match multiple expressions, they can belong to multiple destination classes.
• A route is selected if its matching expression evaluates to true for the incoming connection. If the expression does not match, Router will continue to evaluate the next route in the list. This means that a connection is always routed to at most one route, the first one whose expression matches.

Each matched route then specifies a set of destination classes, determining which group(s) of servers will handle the connection.

 

Basic syntax

At the core of Routing Guidelines is a custom expression language purpose-built for MySQL Router. This language allows for declarative rule definitions using familiar constructs – variables, operators, function calls, and literals – but is intentionally restricted to ensure safety and predictability.

This section walks through the grammar supported by the parser, covering its syntax and semantics.

Expressions

Expressions can be:

• Literal values (42, "read-only", TRUE, NULL)
• Variable references (e.g., $.router.hostname, $.server.uuid, $.session.user)
• Unary or binary operations (arithmetic, logic, comparison)
• Build-in function calls
• Parenthesized sub-expressions

Literals

The following types are supported:

• Numbers: 123, 3.14
• Strings: “primary”, “mysql”
• Booleans: TRUE, FALSE
• Null: NULL
• Roles: predefined keywords (PRIMARY, SECONDARY, REPLICA, READ_REPLICA)

Precedence and Grouping

Operator precedence is defined in the grammar:

1. Parentheses
2. Unary - , ! 
3. * , / , % 
4. + , - 
5. Comparisons (=, < , <> , etc.)
6. IN, LIKE
7. NOT 
8. AND 
9. OR 

 

Variables set from Metadata

Routing decisions depend on the current state of the topology. To support this, the Router sets variable values in Routing Guideline expressions:

• $.router and $.server: populated based on information from metadata
• $.session: resolved based on connection parameters

This enables you to create expressive, context-aware routing logic.

With metadata-aware routing and smart refresh logic, MySQL Router achieves a great balance between control, flexibility, and safety. It allows for per-Router overrides, live updates, and graceful error handling – all while keeping connections secure and policy-compliant.

 

Arithmetic in Action

The parser supports a wide range of arithmetic operations, including

• addition (+)
• subtraction (-)
• multiplication (*)
• division (/)
• modulo (%).

This means you can easily incorporate mathematical expressions into your routing rules.

Example: Simple Arithmetic Rule

$.session.connectAttrs.response_time / 1000 < 2
    

In this rule, the response time is converted from milliseconds to seconds before being compared. The parser evaluates the division first and then performs the comparison, making the rule both concise and efficient.

Example: Complex Calculation

($.session.connectAttrs.active_connections * 2) + 10 < $.session.connectAttrs.max_connections
    

Here, the number of active connections is doubled and incremented by 10 before comparing it to the maximum allowed connections. This kind of nested arithmetic is fully supported, allowing you to build nuanced conditions.

 

Logical Operators and Conditions

The parser also supports logical operators such as

• AND, OR
• NOT
• TRUE and FALSE

Those keywords allow you to combine multiple conditions within a single rule.

Example: Combining Conditions

$.session.connectAttrs.response_time < 200 AND $.session.connectAttrs.active_connections > 50
    

This rule ensures that both conditions must be met for the routing rule to trigger. The use of logical operators enhances rule flexibility and helps create more targeted routing strategies.

Example: Negating a Condition

NOT ($.server.tags.region = "us-east" OR $.server.tags.region = "us-west")
    

In this case, the rule evaluates to true only if the region is neither “us-east” nor “us-west”, making it useful for defining rules that exclude specific cases.

 

Advanced Matching: IN and LIKE

The MySQL Router Routing Guidelines parser provides advanced support for the IN and LIKE operators, allowing you to define more sophisticated routing rules that involve membership checks and pattern matching. These operators are essential for matching values against a list of possibilities or checking if a value fits a specific pattern, making them indispensable in many real-world routing scenarios.

The IN operator allows you to check if a value is present in a set of possible values. This is particularly useful when you want to route traffic based on a list of predefined values, such as a list of allowed regions, user roles, or other categorical data.

Example: Using IN to Check for Allowed Regions

$.server.tags.region IN ("us-east", "us-west", "eu-central")
    

This rule checks if the region value is one of the allowed regions—either “us-east”, “us-west”, or “eu-central”. If the value matches any of the listed regions, the condition evaluates to true.

Example: Negating the IN Operator with NOT

$.server.tags.region NOT IN ("us-east", "us-west")
    

Here, the rule evaluates to true if the region is not “us-east” or “us-west”, providing a way to exclude specific values from being routed to certain resources. This negation makes it easy to define exclusionary conditions.

The LIKE operator is used for pattern matching. It enables you to match values against patterns using wildcards like % (for zero or more characters) and _ (for exactly one character). This is particularly useful when working with string data, such as matching user roles or resource names that follow a naming convention.

Example: Using LIKE for Pattern Matching

$.session.user LIKE "admin_%"
    

In this example, the rule matches any user that starts with “admin_”, such as “admin_user1”, “admin_supervisor”, or “admin_support”. The % wildcard allows for flexibility, matching any characters that follow the “admin_” prefix.

Example: Negating the LIKE Operator

$.session.user NOT LIKE "guest_%"

This rule checks if the user does not match the pattern “guest_%” – meaning it excludes users that start with “guest_”. It’s a great way to define routing behavior based on role prefixes or naming conventions.

 

Type-Safe Comparisons

Another of the powerful features of the MySQL Router Routing Guidelines parser is its ability to handle comparisons intelligently based on the types of the compared values. This feature ensures that comparisons are both flexible and accurate without sacrificing safety or correctness.

In a routing environment, rules often involve comparisons between various types of data like numbers, strings, booleans, or roles. Without proper handling, comparing incompatible types (like a number with a string) could lead to unexpected behavior or even crashes.

The Routing Guidelines parser prevents such issues by integrating type awareness into comparison operations. This means the parser:

• Validates types: It checks whether the compared values are of compatible types before proceeding.
• Handles type mismatches gracefully: If the types don’t match, it stops the evaluation and reports an informative error.
• Executes valid comparisons efficiently: When the types do match, the parser evaluates the comparison safely and correctly.

Expressions in the parser are not just parsed—they are type-checked and reduced when possible. The parser attempts to evaluate binary operations eagerly when operand types allow it (e.g., constant folding). For example, for a matching condition containing the following operation “3 + 5 > 6”, instead of evaluating it every time a request comes in, the parser can precompute the expression and directly use the result.

How Does It Work?

During Parsing:

• The parser identifies an expression involving only constants.
• It performs the arithmetic operation during the parsing phase.
• The result (8 > 6 → true) is stored directly.

Constant folding can improve the performance of routing decisions, especially when rules involve simple arithmetic or comparisons. Instead of evaluating the expression repeatedly, the precomputed result is used directly, minimizing the processing time for each request.

 

Topology Role Matching

The Routing Guidelines parser supports the use of constant values for role matching, such as:

• $.server.memberRole: matches PRIMARY, SECONDARY, or READ_REPLICA

• $.server.clusterRole: matches PRIMARY or REPLICA

 This flexibility allows you to create precise routing rules based on the server’s role within the database cluster.

 

Built-in Functions in Routing Guidelines

Routing Guidelines in MySQL Router are powerful – but to unlock their full potential, you need to speak their language. That language includes a set of built-in functions that allow you to write smarter, more dynamic routing rules. Each of these functions lets you add nuance to your Routing Guidelines – whether you’re targeting users, routing by region, adapting to load, or shaping traffic by environment.

These aren’t just passive checks. They let you actively control how and where connections are routed across your MySQL deployment, all without changing Router config files or restarting services.

 

String Handling Functions

These are perfect when you want to make routing decisions based on usernames, hostnames, schema names, or any string-based values.

CONCAT

Combine multiple strings into one. Great for creating dynamic hostnames or keys.

"match": "$.router.hostname = CONCAT($.session.user, '_mac')"
    

✔ Routes users to machines with hostnames that match their username pattern.

Let’s break it down:

• $.session.user contains the username of the authenticated session.
• CONCAT($.session.user, ‘_mac’) appends “_mac” to the username.
• The match checks whether the router’s hostname matches the generated value.

SUBSTRING_INDEX

Extract parts of a string based on a delimiter.

Combine multiple strings into one. Great for creating dynamic hostnames or keys.

"match": "SUBSTRING_INDEX($.router.hostname, '.', -2) = 'example.com'"
    

✔ Targets routers with hostnames ending in example.com.

Let’s break it down:

• SUBSTRING_INDEX($.router.hostname, '.', -2) extracts the last two segments of the hostname.
• This matches routers like router1.example.com, test.example.com, etc.

STARTSWITH / ENDSWITH / CONTAINS

Check if strings start with, end with, or include a certain value.

"match": "STARTSWITH($.session.schema, 'user')"
    "match": "ENDSWITH($.server.clusterName, 'prod')"
    "match": "CONTAINS($.session.connectAttrs._os, 'Linux')"
    

✔ Perfect for tagging traffic based on naming patterns, environments, or OS.

REGEXP_LIKE

Perform powerful regex pattern matching.

"match": "REGEXP_LIKE($.server.tags.region, '^us-.*-[0-9]+$')"
    

✔ Match complex naming conventions like us-east-1, prod_123, etc.

Let’s break it down:

• REGEXP_LIKE($.server.tags.region, '^us-.*-[0-9]+$') matches regions like us-east-1, us-west-42.
• REGEXP_LIKE($.server.clusterName, '^prod_[0-9]+') matches clusters like prod_1, prod_42, etc.

 

Network & IP Functions

These help you route traffic based on IP addresses, subnets, and resolved DNS entries.

NETWORK

Calculate the network part of an IP address.

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

✔ Match clients in a specific subnet.

If a user connects from 192.168.1.33, the rule will match and the route will be used.

IS_IPV4 / IS_IPV6

Detect whether an address is IPv4 or IPv6.

"match": "IS_IPV6($.session.sourceIP)"
    

✔ Differentiate between IPv4 and IPv6 traffic.

Let’s break it down:

• $.session.sourceIP refers to the IP address of the client.
• The function checks if the IP is a valid IPv6 format.

RESOLVE_V4 / RESOLVE_V6

Convert a hostname into an IP address.

"match": "NETWORK(RESOLVE_V4($.router.hostname), 16) = '10.8.0.0'"
    

✔ Route based on resolved IPs rather than static hostnames.

Let’s break it down:

• RESOLVE_V4($.router.hostname) converts the hostname to an IPv4 address.
• NETWORK(..., 16) derives the subnet.
• Useful for dynamically assigning routes to routers within specific networks.

Convert a hostname into an IP address.

"match": "NOT STARTSWITH(RESOLVE_V6($.router.hostname), '2001::0db8')"
    

Exclude routers from a specific IPv6 network.

 

Numeric and Conversion Functions

Useful when your routing logic relies on numbers – like load balancing, router indexes, or dynamic thresholds.

SQRT

Calculate square roots to create smoother probability thresholds.

Convert a hostname into an IP address.

"match": "$.router.tags.loadFactor <= SQRT($.session.randomValue * 100)"
    

✔ Helps distribute traffic more evenly based on load.

Let’s break it down:

• $.session.randomValue * 100 simulates a dynamic threshold.
• SQRT(...) smooths the values to avoid sharp cutoffs.
• The match ensures lighter-loaded routers get more traffic.

NUMBER

Convert a string to a number. Useful when extracting numbers from strings.

"match": "NUMBER(SUBSTRING_INDEX($.router.name, 'Router', -1)) >= 3"
    

✔ Route to routers numbered 3 and above.

Let’s break it down:

• SUBSTRING_INDEX($.router.name, 'Router', -1) extracts the numeric suffix.
• NUMBER(...) converts it to an actual number.
• This rule targets routers like Router3, Router4, etc.

 

Conclusion

With MySQL Router’s integration with MySQL architectures, routing decisions become dynamic, consistent, and responsive to real-time topology changes – all without requiring manual configuration or restarts.

In this post, we explored how metadata plays a central role in powering intelligent routing decisions. We looked at how Routing Guidelines are stored, structured, and applied, enabling rule-based routing that can adapt to complex workloads and deployment topologies. Thanks to a hierarchical rules model, you can apply shared logic across all Routers while customizing behavior for specific nodes when needed.

By embracing a metadata-driven architecture and Routing Guidelines, MySQL Router empowers developers and DBAs to build resilient, scalable, and maintainable systems – with routing logic that evolves in step with the infrastructure. Routing Guidelines are included in both the MySQL Community and Enterprise editions, making them broadly accessible to a wide range of users.

This brings us to the end of our series on MySQL Routing Guidelines. We’ve gone from managing rules with AdminAPI to understanding how they’re evaluated and enforced in Router. With these tools, you now have the flexibility to route MySQL traffic precisely where and how it’s needed.

Thanks for following along – and happy routing!