PDF (US Ltr)
- 2.4Mb
PDF (A4)
- 2.5Mb
This example shows how to create a ClusterSet Routing Guideline that supports failover, ensuring that traffic can be redirected to alternative nodes in case of outages. This example configures a Routing Guideline prioritizing local nodes but includes remote nodes as failover options. It routes read-write traffic to primary nodes and read-only traffic across secondary and scale-out nodes, with multiple fallback levels for maximum availability.
-
Retrieve the ClusterSet object:
Press CTRL+C to copyclusterset = dba.get_cluster_set() -
Create the Routing Guideline:
Press CTRL+C to copyrg = clusterset.create_routing_guideline("HA_DR_Guideline") -
Add the following primary and secondary destinations for local and remote clusters:
-
Primary local:
Press CTRL+C to copyrg.add_destination("Primary_Local", "$.server.clusterRole = PRIMARY AND $.server.memberRole = PRIMARY AND $.server.isClusterInvalidated = false AND $.server.clusterName = $.router.localCluster"); -
Primary remote:
Press CTRL+C to copyrg.add_destination("Primary_Remote", "$.server.clusterRole = PRIMARY AND $.server.memberRole = PRIMARY AND $.server.isClusterInvalidated = false AND $.server.clusterName <> $.router.localCluster"); -
Secondary local:
Press CTRL+C to copyrg.add_destination("Secondary_Local", "$.server.memberRole = SECONDARY AND $.server.isClusterInvalidated = false AND $.server.clusterName = $.router.localCluster"); -
Secondary remote:
Press CTRL+C to copyrg.add_destination("Secondary_Remote","$.server.memberRole = SECONDARY AND $.server.isClusterInvalidated = false AND $.server.clusterName <> $.router.localCluster"); -
Scale-out local:
Press CTRL+C to copyrg.add_destination("Scale_Out_Local","$.server.memberRole = READ_REPLICA AND $.server.isClusterInvalidated = false AND $.server.clusterName = $.router.localCluster"); -
Scale-out remote:
Press CTRL+C to copyrg.add_destination("Scale_Out_Remote","$.server.memberRole = READ_REPLICA AND $.server.isClusterInvalidated = false AND $.server.clusterName <> $.router.localCluster"); -
Read-only, fallback-local:
Press CTRL+C to copyrg.add_destination("Read_Only_Fallback_Local", "$.server.isClusterInvalidated = true AND ($.server.memberRole = SECONDARY OR $.server.memberRole = READ_REPLICA) AND $.server.clusterName = $.router.localCluster"); -
Read-only, fallback remote:
Press CTRL+C to copyrg.add_destination("Read_Only_Fallback_Remote", "$.server.isClusterInvalidated = true AND ($.server.memberRole = SECONDARY OR $.server.memberRole = READ_REPLICA) AND $.server.clusterName <> $.router.localCluster");
_Localand_Remoteversions of a destination refer to the location of each specific Router instance. When bootstrapping MySQL Router,--conf-target-clustercan be used to specify the name of the InnoDB Cluster that is considered local to the Router. That is, located in the same data center, region, or any other criteria relevant to your setup. Local servers are those located in the same data center as the Router, irrespective of whether the Cluster is a PRIMARY or SECONDARY. -
-
Define the following routes for handling read-write and read-only traffic:
Read-Write (rw_traffic): Prioritizes local primary servers with a remote primary as a fallback. Routes read-write traffic to Primary_Local first and falls back to Primary_Remote.
Read-Only (ro_traffic): Balances read-only traffic across local secondary and scale-out nodes, with multiple fallback levels. Routes read-only traffic to local secondary and scale-out nodes first. If those are unavailable, it falls back to remote secondary and scale-out nodes, then to primary nodes, and finally to read-only fallback nodes if needed. The multi-level priority ensures continuous availability.
Press CTRL+C to copyrg.add_route( "rw_traffic", "$.session.targetPort in ($.router.port.rw, $.router.port.rw_split)", ["first-available(Primary_Local, Primary_Remote)"], {"connectionSharingAllowed": True, "enabled": True}); rg.add_route( "ro_traffic", "$.session.targetPort = $.router.port.ro", [ "round-robin(Secondary_Local, Scale_Out_Local)", "round-robin(Secondary_Remote, Scale_Out_Remote)", "round-robin(Primary_Local, Primary_Remote)", "round-robin(Read_Only_Fallback_Local, Read_Only_Fallback_Remote)" ], {"connectionSharingAllowed": True, "enabled": True}); -
The Routing Guideline was created using the default creation method. As as result, a default destination (PrimaryClusterReadReplica) and route (ro), which have no bearing on this example, must be removed. To remove these entries, run the following commands:
Press CTRL+C to copy// Remove destination rg.remove_destination("PrimaryClusterReadReplica") // Remove route rg.remove_route("ro") -
Activate the Routing Guideline, making it the default routing configuration for the Cluster Set. Run the following command:
Press CTRL+C to copyclusterset.set_routing_option("guideline", "HA_DR_Guideline");
The HA_DR_Guideline is activated. This configuration provides redundancy and high availability, ensuring seamless failover across both local and remote clusters. You can test this setup by simulating node outages or session redirection to confirm that traffic is appropriately routed to the designated fallback nodes.
Read-Write Traffic is directed first to local primary nodes, with remote primary fallback.
Read-Only Traffic is distributed across local secondaries and scale-out nodes, with multiple levels of fallback, including remote and primary nodes.
If applications need to route traffic based on geographic location or compliance requirements, geolocation-based routing guidelines are essential. This example shows how to create a Routing Guideline from a predefined JSON file, ensuring traffic is directed based on server regions and compliance tags for regulatory adherence and optimal latency.
-
Create the Routing Guideline as a JSON file:
Press CTRL+C to copyguideline = { "destinations": [ { "match": "$.server.address IN ("us-east-1.example.com", "us-west-2.example.com")", "name": "US_Regions" }, { "match": "$.server.address IN ("eu-central-1.example.com", "eu-west-1.example.com")", "name": "EU_Regions" }, { "match": "$.server.tags.compliance = '\"GDPR\"'", "name": "GDPR_Compliant" } ], "name": "Geo_Based_Guideline", "routes": [ { "connectionSharingAllowed": True, "destinations": [ { "classes": [ "US_Regions", "EU_Regions" ], "strategy": "round-robin", "priority": 0 } ], "enabled": True, "match": "NETWORK($.session.sourceIP, 24) = NETWORK('192.168.1.0', 24) OR NETWORK($.session.sourceIP, 8) = NETWORK('10.0.0.0', 8)", "name": "geo_based" }, { "connectionSharingAllowed": True, "destinations": [ { "classes": [ "GDPR_Compliant" ], "strategy": "round-robin", "priority": 0 } ], "enabled": True, "match": "$.session.connectAttrs.region = 'EU'", "name": "compliance_based" } ], "version": "1.0" } -
Create the Routing Guideline from the JSON file. This is more convenient for initial setup than adding routes and destinations individually to a default guideline.
Press CTRL+C to copyrg = replicaset.create_routing_guideline("Geo_Based_Guideline", guideline);This example defines the guideline programmatically. You can also define it as a local file and import it using
ClusterSet.import_routing_guideline() -
Modifying the routes or destinations to accommodate changes in location or compliance regulation can be done using
set_destination_option()andset_route_option(). For examplePress CTRL+C to copy// Get the guideline object rg = replicaset.get_routing_guideline("Geo_Based_Guideline"); // Modify the match expression for the 'EU_Regions' destination rg.set_destination_option("EU_Regions", "match", "$.server.address IN ('eu-west-1.example.com', 'eu-south-1.example.com')"); // Disable the 'compliance_based' route temporarily rg.set_route_option("compliance_based", "enabled", False);
This setup directs:
Geolocation-Based Traffic: Routes traffic based on IP networks to specific regional destinations, such as US_Regions and EU_Regions.
Compliance-Based Traffic: Directs traffic requiring GDPR compliance to servers tagged with GDPR_Compliant.
This example shows an efficient way to define and reuse Routing Guidelines for similar geolocation and compliance scenarios across multiple deployments using Routing Guidelines imported from a JSON variable. This approach is ideal for scenarios where consistent routing configurations are needed across environments.
Load balancing and resource management guidelines distribute traffic based on server roles and specific user access levels, optimizing resource usage, and providing high availability. This example shows how to import a Routing Guideline from a JSON file stored locally on your computer, allowing for predefined configurations that can be easily reused.
-
Create the JSON file. Save the following JSON as
load_balancing_guideline.json:Press CTRL+C to copy{ "destinations": [ { "match": "$.server.clusterRole = PRIMARY AND $.server.memberRole = READ_REPLICA", "name": "ReadReplica" }, { "match": "$.server.clusterRole = PRIMARY AND $.server.memberRole = SECONDARY", "name": "Secondary" }, { "match": "$.server.clusterRole = PRIMARY AND $.server.memberRole = PRIMARY", "name": "Primary" } ], "name": "Load_Balancing_Guideline", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Secondary", "ReadReplica" ], "strategy": "round-robin", "priority": 0 }, { "classes": [ "Primary" ], "strategy": "round-robin", "priority": 1 } ], "enabled": true, "match": "$.session.targetPort = $.router.port.ro AND $.session.user = 'readonly_user'", "name": "ro" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Primary" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.targetPort in ($.router.port.rw, $.router.port.rw_split) AND $.session.user = 'admin_user'", "name": "rw" } ], "version": "1.0" }This guideline directs read-only traffic to secondary and read replica servers while reserving write operations for the primary server, optimizing both resource usage and load distribution.
-
Import the Routing Guideline using
import_routing_guideline():Press CTRL+C to copyrg2 = clusterset.import_routing_guideline("/path/to/load_balancing_guideline.json");NoteRouting Guidelines can be exported using
export(".filepath") Activate the Routing Guideline.
This setup ensures the following:
Read-Only Traffic: Directs sessions using the readonly_user account to secondary and read replica nodes in a round-robin fashion, falling back to the primary if necessary.
Read-Write Traffic: Routes sessions using the admin_user account to the primary node, ensuring write operations are isolated to the primary server.
This configuration optimizes resource allocation by distributing read-only traffic across available replicas while preserving primary node resources for write operations. Importing guidelines from JSON files enables you to quickly deploy consistent routing configurations across multiple topologies, ensuring effective load balancing, and resource management.
Application-specific and schema-based routing allows routing guidelines to direct traffic to specific clusters or cluster members based on the application schema or other session details. This configuration is especially useful for vertically partitioned setups, where different schemas are managed by separate clusters to optimize performance and organization.
Press CTRL+C to copy{ "destinations": [ { "match": "$.server.clusterRole = REPLICA AND $.server.memberRole = PRIMARY AND $.server.clusterName = 'AppCluster'", "name": "App_ClusterSet_Primary_Replica" }, { "match": "$.server.clusterRole = REPLICA AND $.server.memberRole = SECONDARY AND $.server.clusterName = 'AppCluster'", "name": "App_ClusterSet_Secondary_Replica" }, { "match": "$.server.clusterRole = PRIMARY AND $.server.memberRole = PRIMARY", "name": "Data_ClusterSet_Primary_Primary" }, { "match": "$.server.clusterRole = PRIMARY AND $.server.memberRole = SECONDARY", "name": "Data_ClusterSet_Secondary_Primary" } ], "name": "Vertical_Partitioning_Guideline", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "App_ClusterSet_Primary_Replica", "App_ClusterSet_Secondary_Replica" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.schema = 'app_schema'", "name": "app_schema_routing" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Data_ClusterSet_Primary_Primary", "Data_ClusterSet_Secondary_Primary" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.schema = 'data_schema'", "name": "data_schema_routing" } ], "version": "1.0" }
With Vertical_Partitioning_Guideline in place:
Application Schema Traffic: Sessions using app_schema are directed to the AppCluster, with a round-robin strategy across primary and secondary members.
Data Schema Traffic: Sessions using data_schema are directed to the main data cluster (Primary), distributed round-robin style between primary and secondary members.
This setup enables efficient traffic management across clusters tailored for specific application requirements, improving performance and simplifying database organization for vertically partitioned environments.
MySQL Version Specific Routing is useful when certain applications or sessions need to connect to servers with a specific MySQL version. This can be essential for compatibility or testing purposes. This guideline directs read-write and read-only traffic to servers with specific MySQL versions, allowing precise version-based traffic management.
Press CTRL+C to copy{ "destinations": [ { "match": "$.server.version = 80403", "name": "MySQL_8_4_3" }, { "match": "$.server.version = 80039", "name": "MySQL_8_0_39" } ], "name": "Version_Specific_Guideline", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "MySQL_8_4_3" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.targetPort IN ($.router.port.rw, $.router.port.rw_split)", "name": "rw_traffic_to_8_4_3" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "MySQL_8_0_39" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.targetPort = $.router.port.ro", "name": "ro_traffic_to_8_0_39" } ], "version": "1.0" }
With the Version_Specific_Guideline in place:
Read-Write Traffic: Routes sessions on read-write ports (rw, rw_split) to servers running MySQL version 8.4.3, prioritizing them as first-available.
Read-Only Traffic: Routes sessions on the read-only port to servers running MySQL version 8.0.39 as first-available.
This configuration enables fine-grained traffic routing based on MySQL version, ensuring version-specific compatibility for various applications or testing environments.
Custom tag-based and performance-based routing enables traffic routing based on server tags, such as performance levels or custom compliance attributes. This example uses tags to route critical application traffic to high-performance servers and directs compliance-related sessions based on specific user attributes.
Press CTRL+C to copy{ "destinations": [ { "match": "$.server.tags.performance = '\"high\"'", "name": "High_Performance" }, { "match": "$.server.tags.performance = '\"low\"'", "name": "Low_Performance" }, { "match": "$.server.tags.type = '\"compliance\"'", "name": "Compliance_Tag" } ], "name": "Tag_Performance_Based_Guideline", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "High_Performance", "Low_Performance" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.connectAttrs.app = 'critical'", "name": "app_critical_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Compliance_Tag" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.user in ('admin', 'finance')", "name": "admin_finance_traffic" } ], "version": "1.0" }
With the Tag_Performance_Based_Guideline in place:
Critical Application Traffic: Routes sessions with the app attribute set to "critical" to servers tagged for high or low performance, prioritizing High_Performance nodes using a first-available strategy.
Compliance-Related Traffic: Routes sessions for admin and finance users to servers tagged with Compliance_Tag using a round-robin strategy to balance compliance-related load.
This setup enables performance-based and custom tag-based routing, directing specific applications and user sessions to the most appropriate resources based on performance needs or compliance attributes.
In environments with distinct stages such as testing, staging, and production, Routing Guidelines can direct traffic based on these designations, ensuring that different sessions are routed to the correct environment. Additionally, session affinity routes traffic from specific users to maintain session persistence across requests.
Press CTRL+C to copy{ "destinations": [ { "match": "$.server.tags.environment = '\"production\"'", "name": "Production_Servers" }, { "match": "$.server.tags.environment = '\"staging\"'", "name": "Staging_Servers" }, { "match": "$.server.tags.environment = '\"testing\"'", "name": "Testing_Servers" } ], "name": "Testing_Stage_Session_Guideline", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Testing_Servers" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.randomValue < 0.1", "name": "testing_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Staging_Servers" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.randomValue >= 0.1 AND $.session.randomValue < 0.3", "name": "staging_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Production_Servers" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.randomValue >= 0.3", "name": "production_traffic" }, { "connectionSharingAllowed": false, "destinations": [ { "classes": [ "Production_Servers", "Staging_Servers", "Testing_Servers" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.user = 'persistent_user'", "name": "session_affinity" } ], "version": "1.0" }
With the Testing_Stage_Session_Guideline in place:
Testing Traffic: Routes a small percentage,
randomValue < 0.1for example, of requests to testing servers, allowing specific sessions to be tested without affecting other environmentsStaging Traffic: Routes moderate traffic,
0.1 <= randomValue < 0.3for example, to staging servers for pre-production validation.Production Traffic: Routes remaining sessions to production servers, ensuring stable operation for production workloads.
Session Affinity: Routes all traffic from persistent_user to maintain session continuity across environments, with traffic distributed among all environments using round-robin.
This setup effectively partitions traffic based on environment, supports testing and staging, and provides session persistence where needed.
Client characteristics routing allows traffic to be directed based on specific attributes of the client session, such as operating system, platform, license type, or unique identifiers. This setup is particularly useful for managing resources in environments with diverse client requirements, ensuring sessions are routed to servers optimized for those characteristics.
Press CTRL+C to copy{ "destinations": [ { "match": "$.server.tags.backup = '\"true\"'", "name": "Backup_Servers" }, { "match": "$.server.tags.os = '\"Linux\"'", "name": "Linux_Clients" }, { "match": "$.server.tags.platform = '\"x86_64\"'", "name": "x86_64_Servers" }, { "match": "$.server.tags.license = '\"Commercial\"'", "name": "Commercial_Servers" }, { "match": "$.server.tags.test = '\"true\"'", "name": "Testing_Servers" } ], "name": "Comprehensive_ConnectAttrs_Routing", "routes": [ { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Linux_Servers" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.connectAttrs._os = 'Linux'", "name": "linux_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "x86_64_Servers" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.connectAttrs._platform = 'x86_64'", "name": "x86_64_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Backup_servers" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.connectAttrs.program_name = 'mysqldump'", "name": "backup_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Commercial_Servers" ], "strategy": "round-robin", "priority": 0 } ], "enabled": true, "match": "$.session.schema = 'audit'", "name": "commercial_traffic" }, { "connectionSharingAllowed": true, "destinations": [ { "classes": [ "Testing_Servers" ], "strategy": "first-available", "priority": 0 } ], "enabled": true, "match": "$.session.user = 'test_user'", "name": "testing_traffic" } ], "version": "1.0" }
With the Comprehensive_ConnectAttrs_Routing in place:
Linux Traffic: Routes sessions where the
"_os"connection attribute is"Linux"to servers running on Linux, distributing connections in a round-robin fashion.x86_64_traffic: Routes sessions with the
"_platform"attribute set to"x86_64"to x86_64-servers, using a first-available strategy.backup_traffic: Routes sessions from
mysqldump,connectAttrs.program_name = 'mysqldump',to the backup servers.Commercial Traffic: Routes sessions using the
auditschema to servers designated as Commercial_Servers.Testing Traffic: Routes specific test sessions, such as
$.session.user = 'test_user', to servers tagged as testing resources.
This guideline provides flexibility to route traffic based on client-specific attributes, enabling targeted resource allocation and optimization for diverse client characteristics.