Frédéric Descamps Community Manager Oracle MySQL MySQL Workshop Paris - February 2025 MySQL Architectures Deployments Simplifying and Automating Modern MySQL Infrastructures with the AdminAPI

The Journey To Easy MySQL Architectures for your needs Copyright @ 2025 Oracle and/or its affiliates. 2

Who am I ? about. me/ lefred Copyright @ 2025 Oracle and/or its affiliates. 3

@lefred @lefredbe.bsky.social @[email protected] MySQL Evangelist using MySQL since version 3.20 devops believer likes living in h ps://lefred.be h ps://github.com/lefred Frédéric Descamps Copyright @ 2025 Oracle and/or its affiliates. 4

Agenda Exploring the Tools, Architectures, and Best Practices for MySQL Deployments Copyright @ 2025 Oracle and/or its affiliates. 5

. Introduction to MySQL AdminAPI . Data Resilience Metrics . Key Concepts in MySQL Deployments . Types of MySQL Architectures Supported by AdminAPI . Deployment Walkthrough with AdminAPI . Best Practices and Advanced Features . Q&A Agenda Copyright @ 2025 Oracle and/or its affiliates. 6

Data Resilience Metrics Understanding RPO and RTO Copyright @ 2025 Oracle and/or its affiliates. 7

Copyright @ 2025 Oracle and/or its affiliates. 8

Recovery Point Objective (RPO) Purpose: Determines maximum acceptable data loss during disruptions. Calculation: Time from disruptive event to last data backup. Example: RPO of 1 hour means tolerating data loss up to 1 hour. Focus: Data loss risk and impact on customer transactions. Data Resilience Metrics Copyright @ 2025 Oracle and/or its affiliates. 9

Recovery Point Objective (RPO) Purpose: Determines maximum acceptable data loss during disruptions. Calculation: Time from disruptive event to last data backup. Example: RPO of 1 hour means tolerating data loss up to 1 hour. Focus: Data loss risk and impact on customer transactions. Recovery Time Objective (RTO) Purpose: De nes maximum allowable downtime after incidents. Calculation: Measures time to restore application functionality. Example: RTO of 4 hours aims for restoration within 4 hours. Focus: Application and system restoration. Data Resilience Metrics Copyright @ 2025 Oracle and/or its affiliates. 9

RPO : how much data can I loose? Copyright @ 2025 Oracle and/or its affiliates. 10

RTO : how long can I stay down? Copyright @ 2025 Oracle and/or its affiliates. 11

Introduction to AdminAPI MySQL Shell Copyright @ 2025 Oracle and/or its affiliates. 12

MySQL Shell and AdminAPI MySQL Shell: a robust command-line interface supporting SQL, JavaScript and Python modes. enables scripting, querying, and database management allows modern development with X Protocol AdminAPI a powerful API for deploying and managing MySQL InnoDB ReplicaSets, Clusters and ClusterSets simpli es setup, con guration, and maintenance of high-availability and distributed systems. Copyright @ 2025 Oracle and/or its affiliates. 13

Copyright @ 2025 Oracle and/or its affiliates. 14

Copyright @ 2025 Oracle and/or its affiliates. 15

Copyright @ 2025 Oracle and/or its affiliates. 16

Key Concepts in MySQL Deployments InnoDB ReplicaSet, Cluster, ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 17

InnoDB ReplicaSet A simpler alternative for asynchronous replication. Easier to manage with AdminAPI. Data provisioning included (clone) 2 or more nodes Manual Failover RPO: 1 second(*) RTO: minutes Key Concepts in MySQL Deployments Copyright @ 2025 Oracle and/or its affiliates. 18

InnoDB ReplicaSet A simpler alternative for asynchronous replication. Easier to manage with AdminAPI. Data provisioning included (clone) 2 or more nodes Manual Failover RPO: 1 second(*) RTO: minutes InnoDB Cluster A self-healing, highly available cluster solution. Built on Group Replication technology. Automatically manages failover and redundancy (min 3 nodes). Con gurable Consistency Levels RPO: 0 RTO: seconds Key Concepts in MySQL Deployments Copyright @ 2025 Oracle and/or its affiliates. 18

What is InnoDB ClusterSet? A disaster recovery (DR) solution that links multiple InnoDB Clusters Provides redundancy across geographically distributed data centers. Ensures data availability during catastrophic failures of the primary cluster. Key Features: Multi-region Failover: Seamless failover between clusters. Group Replication inside each local clusters. Asynchronous Replication: Between primary and secondary clusters. Global Data Distribution: Ideal for distributed applications. Key Concepts in MySQL Deployments (2) InnoDB ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 19

Types of MySQL Architectures Supported Exploring Deployment Models for Scalability, Availability, and Flexibility" Copyright @ 2025 Oracle and/or its affiliates. 20

Single Primary One writable primary node, multiple read-only replicas. Ensures consistent writes. Available with InnoDB ReplicaSet Available with InnoDB Cluster (default) Multi-Primary Multiple nodes accepring writes. Con ict resolution through Group Replication. Available with InnoDB Cluster Not possible with InnoDB ClusterSet Not compatible with all workloads Types of MySQL Architectures Supported Copyright @ 2025 Oracle and/or its affiliates. 21

Types of MySQL Architectures Supported (2) Read Replicas Uses Asynchrous Replication Available with InnoDB ReplicaSet Can be a ached to InnoDB Cluster Cannot change role automatically Used for scaling reads Can be used for backups Copyright @ 2025 Oracle and/or its affiliates. 22

Deployment Walkthrough Using AdminAPI Copyright @ 2025 Oracle and/or its affiliates. 23

Deployment Walkthrough Using AdminAPI Initialize MySQL Shell Launch MySQL Shell in JavaScript (\js) or Python (\py) mode. Copyright @ 2025 Oracle and/or its affiliates. 24

My SQL Inno DB ReplicaSet Copyright @ 2025 Oracle and/or its affiliates. 25

MySQL InnoDB ReplicaSet Copyright @ 2025 Oracle and/or its affiliates. 26

Never create a replica from scratch again: easier safer includes provisioning MySQL InnoDB ReplicaSet Copyright @ 2025 Oracle and/or its affiliates. 26

MySQL InnoDB ReplicaSet (2) Copyright @ 2025 Oracle and/or its affiliates. 27

1. Connect to your MySQL server using MySQL Shell: $ mysqlsh --py mysql://root@my-server 2. Con gure the instance and create your ReplicaSet: Py > dba.configure_replica_set_instance() Py > rs=dba.create_replica_set('myReplicaSet') MySQL InnoDB ReplicaSet (2) Copyright @ 2025 Oracle and/or its affiliates. 27

MySQL InnoDB ReplicaSet (3) Copyright @ 2025 Oracle and/or its affiliates. 28

3. Con gure the second freshly installed instance: Py > dba.configure_replica_set_instance( 'mysql://root@') 4. Add the instance to the ReplicaSet: Py > rs.add_instance() MySQL InnoDB ReplicaSet (3) Copyright @ 2025 Oracle and/or its affiliates. 28

MySQL InnoDB ReplicaSet (4) Py > rs.status() { "replicaSet": { "name": "myReplicaSet", "primary": "127.0.0.1:3310", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "127.0.0.1:3320": { "address": "127.0.0.1:3320", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", "replicationSslMode": "REQUIRED" }, "status": "ONLINE" } }, "type": "ASYNC" } } Copyright @ 2025 Oracle and/or its affiliates. 29

MySQL InnoDB ReplicaSet (5) Copyright @ 2025 Oracle and/or its affiliates. 30

5. Bootstrap the MySQL Routers: # mysqlrouter --bootstrap @ --user=mysqlrouter Router will generate an account per routers, but you also have the possibility to create a dedicated user: Py > rs.setup_router_account('') 6. Start the MySQL Routers: # systemctl start mysqlrouter MySQL InnoDB ReplicaSet (5) Copyright @ 2025 Oracle and/or its affiliates. 30

MySQL InnoDB ReplicaSet (5) Py > rs.list_routers() { "replicaSetName": "myReplicaSet", "routers": { "dell::system": { "hostname": "dell", "lastCheckIn": "2024-11-26 11:18:10", "localCluster": "myReplicaSet", "roPort": "6447", "roXPort": "6449", "rwPort": "6446", "rwSplitPort": "6450", "rwXPort": "6448", "version": "9.2.0" } } } Copyright @ 2025 Oracle and/or its affiliates. 31

MySQL InnoDB ReplicaSet (6) Copyright @ 2025 Oracle and/or its affiliates. 32

In case of a failure of the Primary, as this architecture is based on asynchronous replication, nothing will be automated. "replicaSet": { "name": "myReplicaSet", "primary": "127.0.0.1:3310", "status": "UNAVAILABLE", "statusText": "PRIMARY instance is not available, but there is at least one SECONDARY that could be force-promoted.", MySQL InnoDB ReplicaSet (6) Copyright @ 2025 Oracle and/or its affiliates. 32

MySQL InnoDB ReplicaSet (7) Copyright @ 2025 Oracle and/or its affiliates. 33

A Manual failover is mandatory: Py > rs.force_primary_instance('') MySQL InnoDB ReplicaSet (7) Copyright @ 2025 Oracle and/or its affiliates. 33

MySQL InnoDB ReplicaSet (8) Copyright @ 2025 Oracle and/or its affiliates. 34

If the old Primary is back online, it will be INVALIDATED: "topology": { "127.0.0.1:3310": { "instanceErrors": [ "WARNING: Instance was INVALIDATED and must be rejoined or removed from the replicaset.", "ERROR: Instance is NOT a PRIMARY but super_read_only option is OFF. Accidental updates to this instance are possible and will cause inconsistencies in the replicaset." ], "instanceRole": null, "mode": null, "status": "INVALIDATED", MySQL InnoDB ReplicaSet (8) Copyright @ 2025 Oracle and/or its affiliates. 34

MySQL InnoDB ReplicaSet (8) Copyright @ 2025 Oracle and/or its affiliates. 35

A Manual failover is mandatory: Py > rc.rejoin_instance('') Py > rc.describe() { "name": "myReplicaSet", "topology": [ { "address": "127.0.0.1:3310", "instanceRole": "REPLICA", "label": "127.0.0.1:3310" }, { "address": "127.0.0.1:3320", "instanceRole": "PRIMARY", "label": "127.0.0.1:3320" } ] } MySQL InnoDB ReplicaSet (8) Copyright @ 2025 Oracle and/or its affiliates. 35

MySQL InnoDB Cluster Copyright @ 2025 Oracle and/or its affiliates. 36

MySQL InnoDB Cluster Copyright @ 2025 Oracle and/or its affiliates. 37

High Availability solution: automatic failover based on Group Replication no data-loss (RPO=0) auto-provisioning MySQL InnoDB Cluster Copyright @ 2025 Oracle and/or its affiliates. 37

MySQL InnoDB Cluster (2) Copyright @ 2025 Oracle and/or its affiliates. 38

1. Connect to your MySQL server using MySQL Shell: $ mysqlsh --py mysql://root@my-server 2. Con gure the instance and create your single node InnoDB Cluster: Py > dba.configure_instance() Py > cluster=dba.create_cluster('myCluster') MySQL InnoDB Cluster (2) Copyright @ 2025 Oracle and/or its affiliates. 38

MySQL InnoDB Cluster (3) Copyright @ 2025 Oracle and/or its affiliates. 39

3. Con gure a freshly installed instance: Py > dba.configure_instance('') And add it to the cluster: Py > cluster.add_instance('') MySQL InnoDB Cluster (3) Copyright @ 2025 Oracle and/or its affiliates. 39

MySQL InnoDB Cluster (4) Py > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "127.0.0.1:3310", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "9.1.0" }, "127.0.0.1:3320": { "address": "127.0.0.1:3320", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "9.1.0" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "127.0.0.1:3310" } Copyright @ 2025 Oracle and/or its affiliates. 40

MySQL InnoDB Cluster (4) Copyright @ 2025 Oracle and/or its affiliates. 41

4. We con gure a second freshly installed instance: Py > dba.configure_instance('') And add it to the cluster: Py > cluster.add_instance('') MySQL InnoDB Cluster (4) Copyright @ 2025 Oracle and/or its affiliates. 41

MySQL InnoDB Cluster (5) Py > cluster.describe() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "topology": [ { "address": "127.0.0.1:3310", "label": "127.0.0.1:3310", "role": "HA" }, { "address": "127.0.0.1:3320", "label": "127.0.0.1:3320", "role": "HA" }, { "address": "127.0.0.1:3330", "label": "127.0.0.1:3330", "role": "HA" } ], "topologyMode": "Single-Primary" } } Copyright @ 2025 Oracle and/or its affiliates. 42

MySQL InnoDB Cluster (6) Copyright @ 2025 Oracle and/or its affiliates. 43

5. Bootstrap the MySQL Routers: # mysqlrouter --bootstrap @ --user=mysqlrouter --conf-use-gr-notifications 6. Start the MySQL Routers: # systemctl start mysqlrouter MySQL InnoDB Cluster (6) Copyright @ 2025 Oracle and/or its affiliates. 43

MySQL InnoDB Cluster (7) Copyright @ 2025 Oracle and/or its affiliates. 44

In case of a failure, the other nodes will elect a new Primary as soon as they con rm that the previous Primary is MISSING. MySQL InnoDB Cluster (7) Copyright @ 2025 Oracle and/or its affiliates. 44

In case of a failure, the other nodes will elect a new Primary as soon as they con rm that the previous Primary is MISSING. MySQL InnoDB Cluster (7) Copyright @ 2025 Oracle and/or its affiliates. 45

MySQL InnoDB Cluster: consistency levels MySQL InnoDB Cluster operates, by default, in single-primary mode: Apps and users write to the primary instance What guarantees do we have that: We always read the most up-to-date data? We do not read stale data of an evicted server? Dirty reads do not happen? Copyright @ 2025 Oracle and/or its affiliates. 46

mysql mysql> > show show variables variables like like 'group_replication_consistency' 'group_replication_consistency'; ; + +-------------------------------+----------+ -------------------------------+----------+ | | Variable_name Variable_name | | Value Value | | + +-------------------------------+----------+ -------------------------------+----------+ | | group_replication_consistency group_replication_consistency | | EVENTUAL EVENTUAL | | + +-------------------------------+----------+ -------------------------------+----------+ MySQL InnoDB Cluster: consistency levels The default consistency level is EVENTUAL. This means that there is no synchronization point for the transactions, when you perform a write on a node, if you immediately read the same data on another node, it is eventually there. Copyright @ 2025 Oracle and/or its affiliates. 47

mysql mysql> > show show variables variables like like 'group_replication_consistency' 'group_replication_consistency'; ; + +-------------------------------+----------+ -------------------------------+----------+ | | Variable_name Variable_name | | Value Value | | + +-------------------------------+----------+ -------------------------------+----------+ | | group_replication_consistency group_replication_consistency | | EVENTUAL EVENTUAL | | + +-------------------------------+----------+ -------------------------------+----------+ MySQL InnoDB Cluster: consistency levels The default consistency level is EVENTUAL. This means that there is no synchronization point for the transactions, when you perform a write on a node, if you immediately read the same data on another node, it is eventually there. Since MySQL 8.0.16, we have the possibility to set the synchronization point at read or at write or both (globally or for a session). Copyright @ 2025 Oracle and/or its affiliates. 47

Consistency: BEFORE (READ) Copyright @ 2025 Oracle and/or its affiliates. As a DBA, I want to load balance my reads without deploying additional restrictions on which server I read from to avoid reading stale data, my group writes are much more than my group reads. 48

Consistency: BEFORE (READ) < > Copyright @ 2025 Oracle and/or its affiliates. As a DBA, I want to load balance my reads without deploying additional restrictions on which server I read from to avoid reading stale data, my group writes are much more than my group reads. As a developer, I want specific transactions in my workload to always read up- to-date data from the group, so that whenever that sensitive data is updated, I will enforce that reads shall read the most up to date value. 48

Consistency: BEFORE (READ) Py Py > > cluster cluster. .set_option set_option( ("consistency" "consistency", , "BEFORE" "BEFORE") ) Setting the value of Setting the value of 'consistency' 'consistency' to to 'BEFORE' 'BEFORE' in in all all cluster members cluster members . .. .. . Successfully Successfully set set the value of the value of 'consistency' 'consistency' to to 'BEFORE' 'BEFORE' in in the the 'myCluster' 'myCluster' cluster cluster. . Copyright @ 2025 Oracle and/or its affiliates. 49

Consistency: AFTER (WRITE) Copyright @ 2025 Oracle and/or its affiliates. I want to load balance my reads without deploying additional restrictions on which server I read from to avoid reading stale data, my group writes are much less than my group reads. 50

Consistency: AFTER (WRITE) < > Copyright @ 2025 Oracle and/or its affiliates. I want to load balance my reads without deploying additional restrictions on which server I read from to avoid reading stale data, my group writes are much less than my group reads. I have a group that mostly does reads-only, I want my read-write transactions to be applied everywhere once they commit, so that subsequent reads are done on up-to-date data that includes my latest write. Without paying at reads. 50

Consistency: AFTER (WRITE) Py Py > > cluster cluster. .set_option set_option( ("consistency" "consistency", , "AFTER" "AFTER") ) Setting the value of Setting the value of 'consistency' 'consistency' to to 'AFTER' 'AFTER' in in all all cluster members cluster members . .. .. . Successfully Successfully set set the value of the value of 'consistency' 'consistency' to to 'AFTER' 'AFTER' in in the the 'myCluster' 'myCluster' cluster cluster. . Copyright @ 2025 Oracle and/or its affiliates. 51

Consistency: BEFORE_AND_AFTER < > Copyright @ 2025 Oracle and/or its affiliates. I want that my application to replicate data as close as possible to synchronous. And of course I'm OK to pay the required price ! 52

Consistency: BEFORE_AND_AFTER Py Py > > cluster cluster. .set_option set_option( ("consistency" "consistency", , "BEFORE_AND_AFTER" "BEFORE_AND_AFTER") ) Setting the value of Setting the value of 'consistency' 'consistency' to to 'BEFORE_AND_AFTER' 'BEFORE_AND_AFTER' in in all all cluster members cluster members . .. .. . Successfully Successfully set set the value of the value of 'consistency' 'consistency' to to 'BEFORE_AND_AFTER' 'BEFORE_AND_AFTER' in in the the 'myCluster' 'myCluster' cluster cluster. . It's also possible and recommended for the most restrictive levels to de ne it at statement session level: SQL SQL > > SET SET SESSION SESSION group_replication_consistency group_replication_consistency= ="BEFORE_AND_AFTER" "BEFORE_AND_AFTER"; ; SQL SQL > > SELECT SELECT * * FROM FROM t1 t1; ; Copyright @ 2025 Oracle and/or its affiliates. 53

MySQL InnoDB ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 54

MySQL InnoDB ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 55

MySQL InnoDB ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 56

1. We tell the cluster that it's the origin of a new ClusterSet: Py > cluster=dba.get_cluster(); Py > cs=cluster.create_cluster_set('myClusterSet') MySQL InnoDB ClusterSet Copyright @ 2025 Oracle and/or its affiliates. 56

We need to initialize a ClusterSet from an existing cluster. Here is the actual status: MySQL InnoDB ClusterSet (2) Copyright @ 2025 Oracle and/or its affiliates. 57

We need to initialize a ClusterSet from an existing cluster. Here is the actual status: Py > cs.status() { "clusters": { "myCluster": { "clusterRole": "PRIMARY", "globalStatus": "OK", "primary": "127.0.0.1:3330" } }, "domainName": "myClusterSet", "globalPrimaryInstance": "127.0.0.1:3330", "primaryCluster": "myCluster", "status": "HEALTHY", "statusText": "All Clusters available." } MySQL InnoDB ClusterSet (2) Copyright @ 2025 Oracle and/or its affiliates. 57

MySQL InnoDB ClusterSet (3) Copyright @ 2025 Oracle and/or its affiliates. 58

2. We con gure a new freshly installed instance on the Disaster Recovery site. Py > dba.configure_instance('') MySQL InnoDB ClusterSet (3) Copyright @ 2025 Oracle and/or its affiliates. 58

MySQL InnoDB ClusterSet (4) Copyright @ 2025 Oracle and/or its affiliates. 59

3. We create the Replica Cluster. This includes data provisioning and aynchronous replication setup. Py > dr=cs.create_replica_cluster('', 'myDRCluster') MySQL InnoDB ClusterSet (4) Copyright @ 2025 Oracle and/or its affiliates. 59

MySQL InnoDB ClusterSet (5) Copyright @ 2025 Oracle and/or its affiliates. 60

4. We con gure and add new instances if needed: Py > dr=dba.get_cluster('myDRCluster') Py > dr.add_instance('') Py > dr.add_instance('') MySQL InnoDB ClusterSet (5) Copyright @ 2025 Oracle and/or its affiliates. 60

MySQL InnoDB ClusterSet (6) Py > cs.status() { "clusters": { "myCluster": { "clusterRole": "PRIMARY", "globalStatus": "OK", "primary": "127.0.0.1:3330" }, "myDRCluster": { "clusterRole": "REPLICA", "clusterSetReplicationStatus": "OK", "globalStatus": "OK" } }, "domainName": "myClusterSet", "globalPrimaryInstance": "127.0.0.1:3330", "primaryCluster": "myCluster", "status": "HEALTHY", "statusText": "All Clusters available." } Copyright @ 2025 Oracle and/or its affiliates. 61

These architectures are also available with the MySQL Operator for Kubernetes. See: h ps://dev.mysql.com/doc/mysql- operator/en/mysql-operator- properties.html Kubernetes Copyright @ 2025 Oracle and/or its affiliates. 62

My SQL Inno DB Read Replicas Copyright @ 2025 Oracle and/or its affiliates. 63

Added support for Read Replicas with MySQL InnoDB Cluster and MySQL InnoDB ClusterSet: MySQL InnoDB Read Replicas Copyright @ 2025 Oracle and/or its affiliates. 64

Adding Read Replicas is easy: Py > cluster.add_replica_instance( '') Py > cluster.add_replica_instance( ', {'replicationSources': 'Secondary'}) MySQL InnoDB Read Replicas Copyright @ 2025 Oracle and/or its affiliates. 65

New routing policy with transparent read/write spli ing: [routing:bootstrap_rw_split] bind_address=0.0.0.0 bind_port=6450 destinations= metadata-cache://myClusterSet/?role=PRIMARY_AND_SECONDARY routing_strategy=round-robin protocol=classic connection_sharing=1 client_ssl_mode=PREFERRED server_ssl_mode=PREFERRED access_mode=auto Transparent Read/Write Spli ing Copyright @ 2025 Oracle and/or its affiliates. 66

Py Py > > cs cs. .list_routers list_routers( () ) { { "domainName" "domainName": : "myClusterSet" "myClusterSet", , "routers" "routers": : { { "dell::system" "dell::system": : { { "hostname" "hostname": : "dell" "dell", , "lastCheckIn" "lastCheckIn": : "2023-09-17 11:40:36" "2023-09-17 11:40:36", , "roPort" "roPort": : "6447" "6447", , "roXPort" "roXPort": : "6449" "6449", , "rwPort" "rwPort": : "6446" "6446", , "rwSplitPort" "rwSplitPort": : "6450" "6450", , "rwXPort" "rwXPort": : "6448" "6448", , "targetCluster" "targetCluster": : null null, , "version" "version": : "8.2.0" "8.2.0" } } } } } } Transparent Read/Write Spli ing Copyright @ 2025 Oracle and/or its affiliates. 67

Transparent Read/Write Spli ing MySQL MySQL> > \c root \c [email protected] @127.0.0.1: :6450 6450 MySQL MySQL> > 127.0 127.0.0 .0.1 .1: :6450 6450 > > select select @ @@port @port, , member_role member_role from from performance_schema performance_schema. .replication_group_members replication_group_members where where member_id member_id= =@ @@server_uuid @server_uuid; ; + +--------+-------------+ --------+-------------+ | | @ @@port @port | | member_role member_role | | + +--------+-------------+ --------+-------------+ | | 3330 3330 | | SECONDARY SECONDARY | | + +--------+-------------+ --------+-------------+ 1 1 row row in in set set ( (0.0034 0.0034 sec sec) ) MySQL MySQL> > 127.0 127.0.0 .0.1 .1: :6450 6450 > > start start transaction transaction; ; MySQL MySQL> > 127.0 127.0.0 .0.1 .1: :6450 6450 > > select select @ @@port @port, , member_role member_role from from performance_schema performance_schema. .replication_group_members replication_group_members where where member_id member_id= =@ @@server_uuid @server_uuid; ; + +--------+-------------+ --------+-------------+ | | @ @@port @port | | member_role member_role | | + +--------+-------------+ --------+-------------+ | | 3310 3310 | | PRIMARY PRIMARY | | + +--------+-------------+ --------+-------------+ 1 1 row row in in set set ( (0.0016 0.0016 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 68

MySQL 8.4 LTS MySQL Shell & Router - be er integration Copyright @ 2025 Oracle and/or its affiliates. 69

MySQL Shell & MySQL Router MySQL Shell can list and set MySQL Router options: Py Py > > cluster cluster. .router_options router_options( () ) { { "clusterName" "clusterName": : "myCluster" "myCluster", , "configuration" "configuration": : { { "routing_rules" "routing_rules": : { { "invalidated_cluster_policy" "invalidated_cluster_policy": : "drop_all" "drop_all", , "read_only_targets" "read_only_targets": : "secondaries" "secondaries", , "stats_updates_frequency" "stats_updates_frequency": : - -1 1, , "tags" "tags": : { {} }, , "unreachable_quorum_allowed_traffic" "unreachable_quorum_allowed_traffic": : "none" "none", , "use_replica_primary_as_rw" "use_replica_primary_as_rw": : false false } } } }, , "routers" "routers": : { { "dell::system" "dell::system": : { { "configuration" "configuration": : { {} } } } } } } } Copyright @ 2025 Oracle and/or its affiliates. 70

MySQL Shell & MySQL Router (2) Extended output is supported and allows to list all options: Py Py > > cluster cluster. .router_options router_options( ({ {'extended' 'extended': : 1 1} }) ) Py Py > > cluster cluster. .router_options router_options( ({ {'extended' 'extended': : 2 2} }) ) Copyright @ 2025 Oracle and/or its affiliates. 71

MySQL Shell & MySQL Router (2) Extended output is supported and allows to list all options: Py Py > > cluster cluster. .router_options router_options( ({ {'extended' 'extended': : 1 1} }) ) Py Py > > cluster cluster. .router_options router_options( ({ {'extended' 'extended': : 2 2} }) ) And the options can be set for replicaset, cluster and clusterset: Py Py > > replicaset replicaset. .set_routing_option set_routing_option( ("stats_updates_frequency" "stats_updates_frequency", , 10 10) ) Routing option Routing option 'stats_updates_frequency' 'stats_updates_frequency' successfully updated successfully updated. . Py Py > > cluster cluster. .set_routing_option set_routing_option( ("read_only_targets" "read_only_targets", , "read_replicas" "read_replicas") ) Routing option Routing option 'read_only_targets' 'read_only_targets' successfully updated successfully updated. . Py Py > > clusterset clusterset. .set_routing_option set_routing_option( ("invalidated_cluster_policy" "invalidated_cluster_policy", , "drop_all" "drop_all") ); ; Routing option Routing option 'invalidated_cluster_policy' 'invalidated_cluster_policy' successfully updated successfully updated. . Copyright @ 2025 Oracle and/or its affiliates. 71

MySQL Shell & MySQL Router (3) The accepted options are: target_cluster: Target Cluster for Router routing operations. Default value is 'primary'. invalidated_cluster_policy: Routing policy to be taken when the target cluster is detected as being invalidated. Default value is 'drop_all'. stats_updates_frequency: Number of seconds between updates that the Router is to make to its statistics in the InnoDB Cluster metadata. use_replica_primary_as_rw: Enable/Disable the RW Port in Replica Clusters. Disabled by default. tags: Associates an arbitrary JSON object with custom key/value pairs with the ClusterSet metadata. read_only_targets: Routing policy to de ne Router's usage of Read Replicas. Default is 'append' Copyright @ 2025 Oracle and/or its affiliates. 72

Best Practices and Advanced Features Copyright @ 2025 Oracle and/or its affiliates. 73

Group Replication, Asynchronous - Best Practices Use a low latency network within a Group (MySQL InnoDB Cluster) Tune, probably increase, the value of group_replication_member_expel_timeout, default to 5 Use group_replication_poll_spin_loops to ne-tune polling loops for responsiveness on high-performance networks. Tune compression with group_replication_compression_threshold to reduce network overhead if you have signi cant write activity. Increase the GCS consensus messages window select group_replication_set_write_concurrency(100); Copyright @ 2025 Oracle and/or its affiliates. 74

Group Replication, Asynchronous - Best Practices (2) Use a Single Leader Paxos group_replication_paxos_single_leader Optimize binary logs: binlog_row_image = MINIMAL binlog_row_value_options = PARTIAL_JSON binlog_transaction_compression = ON Relax on durability when possible: sync_binlog = 0 innodb_ ush_log_at_trx_commit = 2 Copyright @ 2025 Oracle and/or its affiliates. 75

NEW, COOL and POWERFUL Routing Guidelines Copyright @ 2025 Oracle and/or its affiliates. 76

Routing Guidelines Available since MySQL 9.2 Smarter Routing: enables dynamic, exible and declarative query routing Managed in MySQL Shell with the AdminAPI Ready for future architectures Copyright @ 2025 Oracle and/or its affiliates. 77

Power of Routing Guidelines We can create an in nite number of routing rules, matching a lot of criteria, based on predi ned variables like: $.server.* Copyright @ 2025 Oracle and/or its affiliates. 78

Power of Routing Guidelines (2) $.session.* Copyright @ 2025 Oracle and/or its affiliates. 79

Power of Routing Guidelines (3) $.router.* Copyright @ 2025 Oracle and/or its affiliates. 80

Power of Routing Guidelines (4) - functions Copyright @ 2025 Oracle and/or its affiliates. 81

Routing Guidelines - Example Copyright @ 2025 Oracle and/or its affiliates. 82

Routing Guidelines - Example Copyright @ 2025 Oracle and/or its affiliates. 82

Routing Guidelines - Documentation Documentation: h ps://dev.mysql.com/doc/mysql-shell/en/admin-api-routing- guidelines.html Cookbook: h ps://github.com/mysql/mysql- shell/blob/master/ROUTING_GUIDELINES.md Copyright @ 2025 Oracle and/or its affiliates. 83

Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2025 Oracle and/or its affiliates. 84

Questions ? Copyright @ 2025 Oracle and/or its affiliates. 85