MySQL Architectures - Easy deployments with the Admin API

Simplifying and Automating Modern MySQL Infrastructures with the AdminAPI.
This session was delivered at the MySQL Meetups in London and Manchester.
MySQL InnoDB ReplicaSet, InnoDB Cluster and InnoDB Cluster are covered. We also discuss consistency levels and transparent read/write splitting with MySQL Router.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL MySQL Meetups UK - December 2024 MySQL Architecture Deployments Symplifying and Automating Modern MySQL Infrastructures with the AdminAPI
2. The Journey To Easy MySQL Architectures for your needs Copyright @ 2024 Oracle and/or its affiliates. 2
3. Who am I ? about. me/ lefred Copyright @ 2024 Oracle and/or its affiliates. 3
4. @lefred 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 @ 2024 Oracle and/or its affiliates. 4
5. Agenda Exploring the Tools, Architectures, and Best Practices for MySQL Deployments Copyright @ 2024 Oracle and/or its affiliates. 5
6. . 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 @ 2024 Oracle and/or its affiliates. 6
7. Data Resilience Metrics Understanding RPO and RTO Copyright @ 2024 Oracle and/or its affiliates. 7
8. Copyright @ 2024 Oracle and/or its affiliates. 8
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. Data Resilience Metrics Copyright @ 2024 Oracle and/or its affiliates. 9
10. 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 @ 2024 Oracle and/or its affiliates. 9
11. RPO : how much data can I loose? Copyright @ 2024 Oracle and/or its affiliates. 10
12. RTO : how long can I stay down? Copyright @ 2024 Oracle and/or its affiliates. 11
13. Introduction to AdminAPI MySQL Shell Copyright @ 2024 Oracle and/or its affiliates. 12
14. 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 @ 2024 Oracle and/or its affiliates. 13
15. Copyright @ 2024 Oracle and/or its affiliates. 14
16. Copyright @ 2024 Oracle and/or its affiliates. 15
17. Copyright @ 2024 Oracle and/or its affiliates. 16
18. Key Concepts in MySQL Deployments InnoDB ReplicaSet, Cluster, ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 17
19. 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 @ 2024 Oracle and/or its affiliates. 18
20. 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 @ 2024 Oracle and/or its affiliates. 18
21. 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 @ 2024 Oracle and/or its affiliates. 19
22. Types of MySQL Architectures Supported Exploring Deployment Models for Scalability, Availability, and Flexibility" Copyright @ 2024 Oracle and/or its affiliates. 20
23. 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 Replicat ion. Available with InnoDB Cluster Not possible with InnoDB ClusterSet Not compatible with all workloads Types of MySQL Architectures Supported Copyright @ 2024 Oracle and/or its affiliates. 21
24. 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 @ 2024 Oracle and/or its affiliates. 22
25. Deployment Walkthrough Using AdminAPI Copyright @ 2024 Oracle and/or its affiliates. 23
26. Deployment Walkthrough Using AdminAPI Initialize MySQL Shell Launch MySQL Shell in JavaScript (\js) or Python (\py) mode. Copyright @ 2024 Oracle and/or its affiliates. 24
27. My SQL Inno DB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 25
28. MySQL InnoDB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 26
29. Never create a replica from scratch again: easier safer includes provisioning MySQL InnoDB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 26
30. MySQL InnoDB ReplicaSet (2) Copyright @ 2024 Oracle and/or its affiliates. 27
31. 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 @ 2024 Oracle and/or its affiliates. 27
32. MySQL InnoDB ReplicaSet (3) Copyright @ 2024 Oracle and/or its affiliates. 28
33. 3. Con gure the second freshly installed instance: Py > dba.configure_replica_set_instance( 'mysql://root@<ip or name>') 4. Add the instance to the ReplicaSet: Py > rs.addInstance(<ip or name>) MySQL InnoDB ReplicaSet (3) Copyright @ 2024 Oracle and/or its affiliates. 28
34. 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 @ 2024 Oracle and/or its affiliates. 29
35. MySQL InnoDB ReplicaSet (5) Copyright @ 2024 Oracle and/or its affiliates. 30
36. 5. Bootstrap the MySQL Routers: # mysqlrouter --bootstrap <user>@<ip or host> --user=mysqlrouter 6. Start the MySQL Routers: # systemctl start mysqlrouter MySQL InnoDB ReplicaSet (5) Copyright @ 2024 Oracle and/or its affiliates. 30
37. 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 @ 2024 Oracle and/or its affiliates. 31
38. MySQL InnoDB ReplicaSet (6) Copyright @ 2024 Oracle and/or its affiliates. 32
39. 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 @ 2024 Oracle and/or its affiliates. 32
40. MySQL InnoDB ReplicaSet (7) Copyright @ 2024 Oracle and/or its affiliates. 33
41. A Manual failover is mandatory: Py > rs.force_primary_instance('<secondary>') MySQL InnoDB ReplicaSet (7) Copyright @ 2024 Oracle and/or its affiliates. 33
42. MySQL InnoDB ReplicaSet (8) Copyright @ 2024 Oracle and/or its affiliates. 34
43. 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 @ 2024 Oracle and/or its affiliates. 34
44. MySQL InnoDB ReplicaSet (8) Copyright @ 2024 Oracle and/or its affiliates. 35
45. A Manual failover is mandatory: Py > rc.rejoin_instance('<previous primary>') 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 @ 2024 Oracle and/or its affiliates. 35
46. MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 36
47. MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 37
48. High Availability solution: based on Group Replication no data-loss (RPO=0) auto-provisioning MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 37
49. MySQL InnoDB Cluster (2) Copyright @ 2024 Oracle and/or its affiliates. 38
50. 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 @ 2024 Oracle and/or its affiliates. 38
51. MySQL InnoDB Cluster (3) Copyright @ 2024 Oracle and/or its affiliates. 39
52. 3. Con gure a freshly installed instance: Py > dba.configure_instance('<ip or name>') And add it to the cluster: Py > cluster.add_instance('<ip or name>') MySQL InnoDB Cluster (3) Copyright @ 2024 Oracle and/or its affiliates. 39
53. 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 @ 2024 Oracle and/or its affiliates. 40
54. MySQL InnoDB Cluster (4) Copyright @ 2024 Oracle and/or its affiliates. 41
55. 4. We con gure a second freshly installed instance: Py > dba.configure_instance('<ip or name>') And add it to the cluster: Py > cluster.add_instance('<ip or name>') MySQL InnoDB Cluster (4) Copyright @ 2024 Oracle and/or its affiliates. 41
56. 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 @ 2024 Oracle and/or its affiliates. 42
57. MySQL InnoDB Cluster (6) Copyright @ 2024 Oracle and/or its affiliates. 43
58. 5. Bootstrap the MySQL Routers: # mysqlrouter --bootstrap <user>@<ip or host> --user=mysqlrouter --conf-use-gr-notifications 6. Start the MySQL Routers: # systemctl start mysqlrouter MySQL InnoDB Cluster (6) Copyright @ 2024 Oracle and/or its affiliates. 43
59. MySQL InnoDB Cluster (7) Copyright @ 2024 Oracle and/or its affiliates. 44
60. 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 @ 2024 Oracle and/or its affiliates. 44
61. 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 @ 2024 Oracle and/or its affiliates. 45
62. 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 @ 2024 Oracle and/or its affiliates. 46
63. 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 @ 2024 Oracle and/or its affiliates. 47
64. 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 @ 2024 Oracle and/or its affiliates. 47
65. Consistency: BEFORE (READ) Copyright @ 2024 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
66. Consistency: BEFORE (READ) < > Copyright @ 2024 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
67. 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 @ 2024 Oracle and/or its affiliates. 49
68. Consistency: AFTER (WRITE) Copyright @ 2024 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
69. Consistency: AFTER (WRITE) < > Copyright @ 2024 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
70. 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 @ 2024 Oracle and/or its affiliates. 51
71. Consistency: BEFORE_AND_AFTER < > Copyright @ 2024 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
72. 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 @ 2024 Oracle and/or its affiliates. 53
73. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 54
74. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 55
75. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 56
76. 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 @ 2024 Oracle and/or its affiliates. 56
77. We need to initialize a ClusterSet from an existing cluster. Here is the actual status: MySQL InnoDB ClusterSet (2) Copyright @ 2024 Oracle and/or its affiliates. 57
78. 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 @ 2024 Oracle and/or its affiliates. 57
79. MySQL InnoDB ClusterSet (3) Copyright @ 2024 Oracle and/or its affiliates. 58
80. 2. We con gure a new freshly installed instance on the Disaster Recovery site. Py > dba.configure_instance('<remote ip or name>') MySQL InnoDB ClusterSet (3) Copyright @ 2024 Oracle and/or its affiliates. 58
81. MySQL InnoDB ClusterSet (4) Copyright @ 2024 Oracle and/or its affiliates. 59
82. 3. We create the Replica Cluster. This includes data provisioning and aynchronous replication setup. Py > dr=cs.create_replica_cluster('<remote ip or name>', 'myDRCluster') MySQL InnoDB ClusterSet (4) Copyright @ 2024 Oracle and/or its affiliates. 59
83. MySQL InnoDB ClusterSet (5) Copyright @ 2024 Oracle and/or its affiliates. 60
84. 4. We con gure and add new instances if needed: Py > dr=dba.get_cluster('myDRCluster') Py > dr.add_instance('<remote node 2>') Py > dr.add_instance('<remote node 3>') MySQL InnoDB ClusterSet (5) Copyright @ 2024 Oracle and/or its affiliates. 60
85. 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 @ 2024 Oracle and/or its affiliates. 61
86. 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 @ 2024 Oracle and/or its affiliates. 62
87. My SQL Inno DB Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 63
88. Added support for Read Replicas with MySQL InnoDB Cluster and MySQL InnoDB ClusterSet: MySQL InnoDB Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 64
89. Adding Read Replicas is easy: Py > cluster.add_replica_instance( '<replica instance>') Py > cluster.add_replica_instance( '<replica instance>, {'replicationSources': 'Secondary'}) MySQL InnoDB Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 65
90. 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 @ 2024 Oracle and/or its affiliates. 66
91. JS JS > > cs cs. .listRouters listRouters( () ) { { "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 @ 2024 Oracle and/or its affiliates. 67
92. Transparent Read/Write Spli ing MySQL MySQL> > \c root \c root@127.0.0.1 @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 @ 2024 Oracle and/or its affiliates. 68
93. MySQL 8.4 LTS MySQL Shell & Router - be er integration Copyright @ 2024 Oracle and/or its affiliates. 69
94. MySQL Shell & MySQL Router With MySQL 8.4 , MySQL Shell is now able to con gure MySQL Router: JS JS > > cluster cluster. .routerOptions routerOptions( () ) { { "clusterName" "clusterName": : "tst" "tst", , "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": : { { "domus::router_test" "domus::router_test": : { { "configuration" "configuration": : { {} } } } } } } } Copyright @ 2024 Oracle and/or its affiliates. 70
95. MySQL Shell & MySQL Router (2) Extended output is supported and allows to list all options: JS JS > > cluster cluster. .routerOptions routerOptions( ({ {'extended' 'extended': : 1 1} }) ) JS JS > > cluster cluster. .routerOptions routerOptions( ({ {'extended' 'extended': : 2 2} }) ) Copyright @ 2024 Oracle and/or its affiliates. 71
96. MySQL Shell & MySQL Router (2) Extended output is supported and allows to list all options: JS JS > > cluster cluster. .routerOptions routerOptions( ({ {'extended' 'extended': : 1 1} }) ) JS JS > > cluster cluster. .routerOptions routerOptions( ({ {'extended' 'extended': : 2 2} }) ) And the options can be set for replicaset, cluster and clusterset: JS JS > > replicaset replicaset. .setRoutingOption setRoutingOption( ("stats_updates_frequency" "stats_updates_frequency", , 10 10) ) Routing option Routing option 'stats_updates_frequency' 'stats_updates_frequency' successfully updated successfully updated. . JS JS > > cluster cluster. .setRoutingOption setRoutingOption( ("read_only_targets" "read_only_targets", , "read_replicas" "read_replicas") ) Routing option Routing option 'read_only_targets' 'read_only_targets' successfully updated successfully updated. . JS JS > > clusterset clusterset. .setRoutingOption setRoutingOption( ("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 @ 2024 Oracle and/or its affiliates. 71
97. Best Practices and Advanced Features Copyright @ 2024 Oracle and/or its affiliates. 72
98. 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_timout, 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 @ 2024 Oracle and/or its affiliates. 73
99. 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 @ 2024 Oracle and/or its affiliates. 74
100. Benefits of Using AdminAPI Copyright @ 2024 Oracle and/or its affiliates. 75
101. Bene ts of Using AdminAPI Ease of Use: Simpli es complex con gurations. Automation: Supports scripting for repetitive tasks. Resilience: Ensures robust HA and disaster recovery mechanisms. Scalability: Easily add/remove nodes as needed. Copyright @ 2024 Oracle and/or its affiliates. 76
102. Best Practices for DR and RPO/RTO Optimization Copyright @ 2024 Oracle and/or its affiliates. 77
103. High Level Best Practices Minimizing RPO: Optimize replication se ings for speed. Monitor replication lag with AdminAPI. Use fast and reliable network connections between clusters. Reducing RTO: Enable automatic failover for critical workloads. Use MySQL Router for seamless application connection. Regularly test failover scenarios to ensure readiness. Copyright @ 2024 Oracle and/or its affiliates. 78
104. High Level Best Practices (2) General Recommendations: Use separate data centers or cloud regions for primary and replica clusters. Regularly back up all clusters to ensure recoverability. Save your binlogs (stream them on a dedicated server) for Point-in-Time Recovery Implement monitoring and alerting tools for proactive issue detection. Copyright @ 2024 Oracle and/or its affiliates. 79
105. Conclusion MySQL Shell Copyright @ 2024 Oracle and/or its affiliates. 80
106. Conlusion AdminAPI in MySQL Shell empowers developers and DBAs to deploy and manage high-availability MySQL architectures with ease. It simpli es deployment, improves resilience, and enhances scalability. With continued adoption, MySQL AdminAPI is a cornerstone for modern database infrastructure. InnoDB ClusterSet enhances disaster recovery capabilities for MySQL environments. Clear RPO/RTO goals and proper con guration ensure robust protection against failures. Copyright @ 2024 Oracle and/or its affiliates. 81
107. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 82
108. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 83