7.8.1 Routing Guidelines JSON Syntax

The top-level properties of a routing guideline document are:

• destinations: groupings of MySQL servers using pattern-matching expressions. The expressions define which servers are included in a destination. Each destination is a candidate pool for routing. Servers can belong to multiple destinations simultaneously. Only online topology members are considered when forming a candidate pool.

• name: the name of the routing guideline.

• routes: expressions which match incoming client sessions with appropriate destination candidates.

• version: the version of the Routing Guidelines document.

Press CTRL+C to copy
 
{
  "destinations": [destinationDefinitions],
  "name": [Name of the routing guidelines document],
  "routes": [routeDefinitions],
  "version": [Version of the routing guidelines document]
}

name and version

• name: the name of the Routing Guideline document.

• version: the version of the routing guideline document. All new Routing Guidelines are created with their version set to 1.0.

destinations

Destination classes enable you to group MySQL instances according to criteria.

The following example shows a default destination class for an InnoDB Cluster:

Press CTRL+C to copy
 
  "destinations": [
      {
          "match": "$.server.memberRole = PRIMARY",
          "name": "Primary"
      },
      {
          "match": "$.server.memberRole = SECONDARY",
          "name": "Secondary"
      },
      {
          "match": "$.server.memberRole = READ_REPLICA",
          "name": "ReadReplica"
      }
  ],

Each of the match expressions evaluate to addresses of the cluster members. The expressions defined in the routes classes define how incoming connections are directed to those addresses.

routes

Each routing class contains the following:

• connectionSharingAllowed: (boolean) Specifies if the route allows connection sharing. This corresponds to the MySQL Router configuration option, connection_sharing. See connection_sharing, for more information.

• destinations: destination groups, ordered by preference.

  • classes: define the individual destination groups, using the names defined in the destinations class.

  • priority: priority of the routing group.

  • strategy: corresponds to the MySQL Router routing_strategy, either first-available or round-robin

    See routing_strategy, for more information.

  • match: matching expression for incoming client sessions.

  • name: the name of the route.

The following example shows a default routes class for a three-member InnoDB Cluster:

Press CTRL+C to copy
 
   "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": [
                    "Secondary"
                ],
                "priority": 0,
                "strategy": "round-robin"
            },
            {
                "classes": [
                    "Primary"
                ],
                "priority": 1,
                "strategy": "round-robin"
            }
        ],
        "enabled": true,
        "match": "$.session.targetPort = $.router.port.ro",
        "name": "ro"
    }
],

These two routes direct read-write traffic to the primary, only, while read-only traffic is directed first to the secondary destination, then to the primary destination if no secondary destination was available. The round-robin routing strategy is used for both, as is connection sharing.

Matching Rules

Routing Guideline matching rules use the following variables, operators, and functions.

• Variables

• Functions and operators

Variables

The following variables are available:

• $.router.bindAddress: (string) matches the IP address on which the router is configured to listen for incoming connections. The address must be a valid IPv4 or IPv6 string.

• $.router.hostname: (string) matches the hostname of the machine on which MySQL Router is running.

• $.router.localCluster: (string) matches the name of the cluster MySQL Router was bootstrapped to, or the name of the cluster set in --conf-target-cluster.

• $.router.name: (string) matches a Router name.

• $.router.port.ro: (integer) matches the port number MySQL Router listens to for incoming, read-only connections. Must be a positive integer in the range 1-65535, inclusive.

• $.router.port.rw: (integer) matches the port number MySQL Router listens to for incoming, read-write connections. Must be a positive integer in the range 1-65535, inclusive.

• $.router.port.rw_split: (integer) matches the port number MySQL Router listens to for incoming, read-write splitting connections. Must be a positive integer in the range 1-65535, inclusive.

• $.router.routeName: matches the name of the Routing plugin used by the Router.

• $.router.tags.tagName: (string) matches the user-defined tagName value defined in the metadata for that router.

• $.server.address: (string) matches the IP address of the server. The address must be a valid IPv4 or IPv6 string.

• $.server.clusterName: (string) matches the name of the InnoDB Cluster to which the server belongs.

• $.server.clusterRole: (enum) matches the cluster role of a Cluster in an InnoDB ClusterSet. Valid values are PRIMARY and REPLICA.

• $.server.clusterSetName: (string) matches the name of the InnoDB ClusterSet to which the server belongs.

• $.server.isClusterInvalidated: (boolean) matches servers which are members of InnoDB Cluster invalidated during a failover.

• $.server.label: (string) matches server label as defined in the metadata.

• $.server.memberRole: (enum) matches the role of a member of an InnoDB Cluster. Valid values are PRIMARY, SECONDARY, and READ_REPLICA.

• $.server.port: (integer) matches the open port used by the server for incoming connections. Must be a positive integer in the range 1-65535, inclusive.

• $.server.tags.tagName: (string) matches the user-defined tagName value defined in the metadata for that server.

• $.server.uuid: (string) matches the server_uuid value defined in the metadata.

• $.server.version: (integer) matches a specific server version. The format must be XXYYZZ, where X is the major version, YY is the minor version and ZZ is the patch version. For example, 8.0.39 is 80039, 8.4.3 is 80403, and 9.2.0 is 90200.

  Note

  The following are not supported:

  • Read Replicas

  • InnoDB ReplicaSet members

  • MySQL versions 8.0.2 or lower.

• $.session.connectAttrs.connectAttributes: (string) matches the session connection attributes.

• $.session.randomValue: (double) matches a random value in the range 0.0 <= x < 1.0.

• $.session.schema: (string) matches the default schema specified at connection time.

• $.session.sourceIP: (string) matches the IP address the session is connecting from. The address must be a valid IPv4 or IPv6 string.

• $.session.targetIP: (string) matches the IP address of the router the session connected to. The address must be a valid IPv4 or IPv6 string.

• $.session.targetPort: (integer) matches the Router port the session is connected to. Must be a positive integer in the range 1-65535, inclusive.

• $.session.user: (string) matches the authenticated session user.

Important

To use $.session.user, $.session.connectAttrs or $.session.schema, MySQL Router must inspect the traffic. This is not possible if the connection is configured to use passthrough mode.

Functions and operators

The following functions and operators are supported:

• Boolean: TRUE | FALSE

• Grouping expressions with parentheses ().

• Logical operators: AND, OR, and NOT.

• Inclusion checks: IN and NOT IN.

• Arithmetic operations: addition (+), subtraction (-), multiplication (*), division (/), and modulo division (%).

• Comparisons: greater than (>), greater or equal (>=), less than (<), less than or equal (<=), equal (=), not equal (<>).

• LIKE: allows pattern matching. Underscore (_) matches any single character and percent (%) matches any character with any number of repetitions.

• CONCAT('str', 'str', ...): enables concatenation of multiple strings, returning a string as result. For example: CONCAT('a', 'b', 'cde') returns abcde.

• SQRT('number'): calculates the square root of a given number, returning a float as a result. For example: SQRT(0.16)returns 0.4.

• NUMBER('str'): converts a string to a number. For example: NUMBER(CONCAT('1', '2')) = 12

• NETWORK('str', 'int'): calculates network address for a given address and bitmask. It returns the network address as a string. For example: NETWORK('192.168.1.33', 24) returns the range 192.168.1.0 to 192.168.1.255.

• IS_IPV4('str'): checks if the given string is a valid IPv4 address. It returns a boolean, true/false.

• IS_IPV6('str'): checks if the given string is a valid IPv6 address. It returns a boolean, true/false.

• SUBSTRING_INDEX('str1', 'str2', 'int'): checks for a substring within a string. str1 is the string, str2 is the delimiter, and int is the delimiter index. If the index is negative, the delimiter is searched from the end of the string.

  For example:

  • SUBSTRING_INDEX("test", "s", 1): returns te.

  • SUBSTRING_INDEX("lorem ipsum", "ip", -1): returns sum.

  • SUBSTRING_INDEX("foo bar baz","a", 2): returns foo bar b

• STARTSWITH('str1', 'str2'): checks if the str2 string is a prefix of str1. Case insensitive. It returns a boolean value, true/false.

• ENDSWITH('str1', 'str2'): checks if the str2 string is a suffix of str1. Case insensitive. It returns a boolean value, true/false.

• CONTAINS('str1', 'str2'): checks if the str2 string is a substring of str1, case insensitive. It returns a boolean value, true/false.

• RESOLVE_V4('str'): resolves hostnames passed as a string parameter in an IPv4 address. It returns a string containing the resolved address. If the hostname can be resolved to multiple addresses, only one of the addresses is returned.

• RESOLVE_V6('str'): resolves hostnames passed as a string parameter in an IPv6 address. It returns a string containing the resolved address. If the hostname can be resolved to multiple addresses, only one of the addresses is returned.

• REGEXP_LIKE('str1', 'str2'): checks if str1 matches the regular expression defined in str2. Case insensitive and uses the Modified ECMAScript regular expression grammar