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
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
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
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
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
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
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
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
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
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
dba.configure_replica_set_instance( 'mysql://root@<ip or name>') 4. Add the instance to the ReplicaSet: Py > rs.add_instance(<ip or name>) MySQL InnoDB ReplicaSet (3) Copyright @ 2025 Oracle and/or its affiliates. 28
host> --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('<user>') 6. Start the MySQL Routers: # systemctl start mysqlrouter MySQL InnoDB ReplicaSet (5) Copyright @ 2025 Oracle and/or its affiliates. 30
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
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
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
or name>') And add it to the cluster: Py > cluster.add_instance('<ip or name>') MySQL InnoDB Cluster (3) Copyright @ 2025 Oracle and/or its affiliates. 39
> dba.configure_instance('<ip or name>') And add it to the cluster: Py > cluster.add_instance('<ip or name>') MySQL InnoDB Cluster (4) Copyright @ 2025 Oracle and/or its affiliates. 41
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
'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
'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
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
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
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
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
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
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" "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
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
the Disaster Recovery site. Py > dba.configure_instance('<remote ip or name>') MySQL InnoDB ClusterSet (3) Copyright @ 2025 Oracle and/or its affiliates. 58
and aynchronous replication setup. Py > dr=cs.create_replica_cluster('<remote ip or name>', 'myDRCluster') MySQL InnoDB ClusterSet (4) Copyright @ 2025 Oracle and/or its affiliates. 59
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
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
exible and declarative query routing Managed in MySQL Shell with the AdminAPI Ready for future architectures Copyright @ 2025 Oracle and/or its affiliates. 77
lefred
tsuemura
araihara
yukiogawa
tattaka
bengo4com
yu4u
74th
biwashi
jacopen
.jpg){kind=avatar}
mdalmijn
panda_program
rist
eileencodes
mraible
csswizardry
trallard
quramy
sachag
keithpitt
colly
speakerdeck
davidbonilla
sugarenia
holman
![@lefred @lefredbe.bsky.social @[email protected] MySQL Evangelist using MySQL since version 3.20](https://files.speakerdeck.com/presentations/73b2a5d40fd2418fabeddc886f0663c0/slide_3.jpg){kind=link}
![New routing policy with transparent read/write spli ing: [routing:bootstrap_rw_split] bind_address=0.0.0.0](https://files.speakerdeck.com/presentations/73b2a5d40fd2418fabeddc886f0663c0/slide_89.jpg){kind=link}
