The Evolution of a MySQL Database Architecture - From single instance to mutli-regions DR with read replicas and transparent read/write splitting

Slide 1

Slide 1 text

Frédéric Descamps Community Manager Oracle MySQL OUGN - April 2024 The Evolution of a MySQL Database Architecture from single instance to mutli-regions DR with read replicas and transparent read/write splitting

Slide 2

Slide 2 text

The Journey To MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 2

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

@lefred MySQL Evangelist using MySQL since version 3.20 devops believer likes 🏀 living in 🇧🇪 https://lefred.be https://github.com/lefred Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 4

Slide 5

Slide 5 text

Best Practices in 2024 configuration parameters Copyright @ 2024 Oracle and/or its affiliates. 5

Slide 6

Slide 6 text

During this presentation I assume that... your system is MySQL 8.0.37 or more recent (LTS 8.4.0) you are using only InnoDB you have the binary logs enabled the binary log format is ROW you are using GTIDs you are using latest MySQL Shell (8.4) Copyright @ 2024 Oracle and/or its affiliates. 6

Slide 7

Slide 7 text

Data Recovery Metrics Understanding RPO and RTO Copyright @ 2024 Oracle and/or its affiliates. 7

Slide 8

Slide 8 text

Slide 9

Slide 9 text

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: Defines 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. 8

Slide 10

Slide 10 text

Evolution to High Availability MySQL 8.x Copyright @ 2024 Oracle and/or its affiliates. 9

Slide 11

Slide 11 text

Slide 12

Slide 12 text

This is where it all begins... reminder: we ONLY use InnoDB we keep the default durability settings One single MySQL instance Copyright @ 2024 Oracle and/or its affiliates. 11

Slide 13

Slide 13 text

One single MySQL instance Copyright @ 2024 Oracle and/or its affiliates. / / RPO ? ? RTO 12

Slide 14

Slide 14 text

Next Level We can't loose all the data (1 days is OK) and we have hours to recover the system Copyright @ 2024 Oracle and/or its affiliates. 13

Slide 15

Slide 15 text

Next Level (level up) The database becomes more important, loosing it might be an issue... Copyright @ 2024 Oracle and/or its affiliates. 14

Slide 16

Slide 16 text

Next Level (level up) The database becomes more important, loosing it might be an issue... RTO → hours Copyright @ 2024 Oracle and/or its affiliates. 14

Slide 17

Slide 17 text

Next Level (level up) The database becomes more important, loosing it might be an issue... RTO → hours RPO → 1 day Copyright @ 2024 Oracle and/or its affiliates. 14

Slide 18

Slide 18 text

Next Level (level up) The database becomes more important, loosing it might be an issue... RTO → hours RPO → 1 day RTO: Recovery Time Objective (how long to recover) RPO: Recovery Point Objective (how much data can be lost) Copyright @ 2024 Oracle and/or its affiliates. 14

Slide 19

Slide 19 text

Physical Backups Logical Backups For logical backups, please use MySQL Shell Dump & Load Utility ! Backups Copyright @ 2024 Oracle and/or its affiliates. 15

Slide 20

Slide 20 text

One single MySQL instance with daily backups Copyright @ 2024 Oracle and/or its affiliates. hours 1 day RPO ? ? RTO 16

Slide 21

Slide 21 text

Next Level We need to loose less data and we still have hours to recover the system Copyright @ 2024 Oracle and/or its affiliates. 17

Slide 22

Slide 22 text

Next Level (level up) 1 day RPO ? Really ?? We want to reduce it to minutes at least ! Copyright @ 2024 Oracle and/or its affiliates. 18

Slide 23

Slide 23 text

Next Level (level up) 1 day RPO ? Really ?? We want to reduce it to minutes at least ! RTO → hours Copyright @ 2024 Oracle and/or its affiliates. 18

Slide 24

Slide 24 text

Next Level (level up) 1 day RPO ? Really ?? We want to reduce it to minutes at least ! RTO → hours RPO → minutes Copyright @ 2024 Oracle and/or its affiliates. 18

Slide 25

Slide 25 text

These are the defaults in MySQL 8.0: Durable Binlogs We enable binary logs allowing to replay the modifications since the last backup: Copyright @ 2024 Oracle and/or its affiliates. 19

Slide 26

Slide 26 text

One single MySQL instance with daily backups and binlogs Copyright @ 2024 Oracle and/or its affiliates. hours minutes RPO ? ? RTO 20

Slide 27

Slide 27 text

Point-in-Time Recovery (PITR) PITR is the technique by which an administrator can restore or recover a data set to a certain point in the past. With MySQL, point-in-time recovery involves restoring a dump of the data and then replaying the binlogs from and to a specific point. This technique is used to: solve an issue perform a live migration Copyright @ 2024 Oracle and/or its affiliates. 21

Slide 28

Slide 28 text

Point-in-Time Recovery : how does it work ? day 1 BACKUPS Binlogs day 2 day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 Copyright @ 2024 Oracle and/or its affiliates. 22

Slide 29

Slide 29 text

Point-in-Time Recovery : how does it work ? day 1 BACKUPS Binlogs day 2 we want to recover up to this point day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 Copyright @ 2024 Oracle and/or its affiliates. 23

Slide 30

Slide 30 text

Point-in-Time Recovery : how does it work ? day 1 BACKUPS Binlogs day 2 we want to recover up to this point day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 1. we restore the dump of day 3 Copyright @ 2024 Oracle and/or its affiliates. 24

Slide 31

Slide 31 text

Point-in-Time Recovery : how does it work ? day 1 BACKUPS Binlogs day 2 day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 2. we replay the binlog (.008) 1. we restore the du,p of day 3 Copyright @ 2024 Oracle and/or its affiliates. 25

Slide 32

Slide 32 text

Point-in-Time Recovery : important concepts Usually, after a backup (and its verification), the binary log files are purged from the MySQL server: Copyright @ 2024 Oracle and/or its affiliates. 26

Slide 33

Slide 33 text

Point-in-Time Recovery : important concepts Usually, after a backup (and its verification), the binary log files are purged from the MySQL server: Copyright @ 2024 Oracle and/or its affiliates. day 1 BACKUPS Binlogs day 2 day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 26

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Next Level We need to loose less data and we still have hours to recover the system Copyright @ 2024 Oracle and/or its affiliates. 27

Slide 36

Slide 36 text

Next Level (level up) My data is very important and I've a heavy workload... I would like to loose less than a second in case of a problem ! Copyright @ 2024 Oracle and/or its affiliates. 28

Slide 37

Slide 37 text

Next Level (level up) My data is very important and I've a heavy workload... I would like to loose less than a second in case of a problem ! RTO → hours Copyright @ 2024 Oracle and/or its affiliates. 28

Slide 38

Slide 38 text

Next Level (level up) My data is very important and I've a heavy workload... I would like to loose less than a second in case of a problem ! RTO → hours RPO → less than a second Copyright @ 2024 Oracle and/or its affiliates. 28

Slide 39

Slide 39 text

Enable GTIDs (they should be already !) Save in realtime the binary logs to a different system (OCI Object Storage, S3, ...) VCN 10.0.0.0/16 Public Subnet 10.0.0.0/24 Private Subnet 10.0.1.0/24 MySQL Shell MySQL Database Service Internet Gateway Oracle Cloud Infrastructure Availability Domain 1 Fault Domain 1 mysql-dump-bucket mysql-binlogs-bucket Object Storage dump instance stream binlogs Improved Point-in-Time Recovery (PITR) Copyright @ 2024 Oracle and/or its affiliates. 29

Slide 40

Slide 40 text

with MySQL restart Enabling GTIDs Copyright @ 2024 Oracle and/or its affiliates. 30

Slide 41

Slide 41 text

with MySQL restart without MySQL restart Enabling GTIDs Copyright @ 2024 Oracle and/or its affiliates. 30

Slide 42

Slide 42 text

Saving Binlogs Copyright @ 2024 Oracle and/or its affiliates. 31

Slide 43

Slide 43 text

Saving Binlogs On another system: mysqlbinlog --raw --read-from-remote-server --stop-never --host 10.0.0.2 \ --port 3306 -u getbinlog -ppassword \ --ssl-mode='REQUIRED' binlog.xxxxxx Copyright @ 2024 Oracle and/or its affiliates. 31

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Point-in-Time Recovery : important concepts (2) As you may have noticed, we can only recover at the exact time of the backups and we can only do a one-off recovery from the last one! This is why it is recommended to relocate/offload the binlogs (to another server, a NAS, the cloud, ...). This will allow for a point-in-time recovery at any moment in time: Copyright @ 2024 Oracle and/or its affiliates. } day 1 BACKUPS Binlogs day 2 day 3 day 4 .001 .002 .003 .004 .005 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 .006 .007 .008 .009 .010 binlog streaming possible recovery points 32

Slide 46

Slide 46 text

One single MySQL instance with daily backups and external binlogs archiving Copyright @ 2024 Oracle and/or its affiliates. hours < 1 second RPO ? ? RTO 33

Slide 47

Slide 47 text

Next Level We need to loose less than a second of data and reduce to minute the recovering time Copyright @ 2024 Oracle and/or its affiliates. 34

Slide 48

Slide 48 text

Next Level (level up) My service is now very important, I would like my database to be available again within minutes in case of an incident. Copyright @ 2024 Oracle and/or its affiliates. 35

Slide 49

Slide 49 text

Next Level (level up) My service is now very important, I would like my database to be available again within minutes in case of an incident. RTO → minutes Copyright @ 2024 Oracle and/or its affiliates. 35

Slide 50

Slide 50 text

Next Level (level up) My service is now very important, I would like my database to be available again within minutes in case of an incident. RTO → minutes RPO → less than a second* Copyright @ 2024 Oracle and/or its affiliates. 35

Slide 51

Slide 51 text

Based on the well known Asynchronous Replication But easier... easier is always better ! Data provisioning included (clone) 2 or more nodes Manual Failover Transparent/Automatic query routing with MySQL Router MySQL InnoDB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 36

Slide 52

Slide 52 text

MySQL InnoDB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 37

Slide 53

Slide 53 text

MySQL InnoDB ReplicaSet Connected to the actual server (our single instance): JS > dba.configureReplicaSetInstance() JS > rs=dba.createReplicaSet('myreplicaset') On the new extra server where only MySQL is installed (server and shell): JS > dba.configureReplicaSetInstance() And again on the main instance (our single instance): JS > rs.addInstance('mysql-2') Copyright @ 2024 Oracle and/or its affiliates. 38

Slide 54

Slide 54 text

MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 39

Slide 55

Slide 55 text

MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 40

Slide 56

Slide 56 text

MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 41

Slide 57

Slide 57 text

MySQL Router MySQL Router is very easy to configure, you only need to use the bootstrap command ! Copyright @ 2024 Oracle and/or its affiliates. 42

Slide 58

Slide 58 text

MySQL Router As usual, this is also visible in MySQL Shell Copyright @ 2024 Oracle and/or its affiliates. 43

Slide 59

Slide 59 text

MySQL InnoDB ReplicaSet - Manual Failover Copyright @ 2024 Oracle and/or its affiliates. MySQL Shell is connected to MySQL InnoDB ReplicaSet via MySQL Router. 44

Slide 60

Slide 60 text

MySQL InnoDB ReplicaSet - Manual Failover Copyright @ 2024 Oracle and/or its affiliates. 45

Slide 61

Slide 61 text

MySQL RelicaSet (*)with point-in-time recovery Copyright @ 2024 Oracle and/or its affiliates. minutes < 1 second RPO ? ? RTO rto > 46

Slide 62

Slide 62 text

Next Level It's time to not loose data anymore and recover as fast as possible Copyright @ 2024 Oracle and/or its affiliates. 47

Slide 63

Slide 63 text

Next Level (level up) My service is very important, I'd like to be operational almost all the time (automatic failover) and never lose any data! Copyright @ 2024 Oracle and/or its affiliates. 48

Slide 64

Slide 64 text

Next Level (level up) My service is very important, I'd like to be operational almost all the time (automatic failover) and never lose any data! RTO → seconds Copyright @ 2024 Oracle and/or its affiliates. 48

Slide 65

Slide 65 text

Next Level (level up) My service is very important, I'd like to be operational almost all the time (automatic failover) and never lose any data! RTO → seconds RPO → 0 Copyright @ 2024 Oracle and/or its affiliates. 48

Slide 66

Slide 66 text

Based on native Group Replication But easier.... easier is always better ! Data provisioning included (clone) 3 or more nodes (odd number) Automatic Failover Uses MySQL Router Configurable Consistency Levels MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 49

Slide 67

Slide 67 text

MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 50

Slide 68

Slide 68 text

On the ReplicaSet's Primary Instance: JS > rs=dba.getReplicaSet() JS > rs.dissolve() JS > cluster=dba.createCluster('myCluster') JS > cluster.addInstance('mysql-2') MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 51

Slide 69

Slide 69 text

On the ReplicaSet's Primary Instance: JS > rs=dba.getReplicaSet() JS > rs.dissolve() JS > cluster=dba.createCluster('myCluster') JS > cluster.addInstance('mysql-2') JS > dba.configureInstance() From any member of the cluster: JS > cluster=dba.getCluster() JS > cluster.addInstance('mysql-3') Finally, we can configure MySQL Router again: # mysqlrouter --bootstrap \ clusteradmin@single-mysql:3306 \ --conf-use-gr-notifications \ --user mysqlrouter --force # systemctl restart mysqlrouter MySQL InnoDB Cluster We can now add a third instance to benefit from the automatic failover: Copyright @ 2024 Oracle and/or its affiliates. 51

Slide 70

Slide 70 text

MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 52

Slide 71

Slide 71 text

MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. seconds 0 RPO ? ? RTO rto > 53

Slide 72

Slide 72 text

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. 54

Slide 73

Slide 73 text

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. 55

Slide 74

Slide 74 text

Slide 75

Slide 75 text

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. 56

Slide 76

Slide 76 text

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. 56

Slide 77

Slide 77 text

Consistency: BEFORE (READ) Copyright @ 2024 Oracle and/or its affiliates. 57

Slide 78

Slide 78 text

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. 58

Slide 79

Slide 79 text

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. 58

Slide 80

Slide 80 text

Consistency: AFTER (WRITE) Copyright @ 2024 Oracle and/or its affiliates. 59

Slide 81

Slide 81 text

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 ! 60

Slide 82

Slide 82 text

Consistency: BEFORE_AND_AFTER It's also possible and recommended for the most restrictive levels to define it at statement session level: Copyright @ 2024 Oracle and/or its affiliates. 61

Slide 83

Slide 83 text

Pause !! Copyright @ 2024 Oracle and/or its affiliates. 62

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

Next Level We want to survive to a disaster at the datacenter Copyright @ 2024 Oracle and/or its affiliates. 64

Slide 86

Slide 86 text

Next Level (level up) And if something happens to our data center ?? Copyright @ 2024 Oracle and/or its affiliates. 65

Slide 87

Slide 87 text

Next Level (level up) And if something happens to our data center ?? How can we deploy a Disaster Recovery solution ? Copyright @ 2024 Oracle and/or its affiliates. 65

Slide 88

Slide 88 text

Next Level (level up) And if something happens to our data center ?? How can we deploy a Disaster Recovery solution ? Copyright @ 2024 Oracle and/or its affiliates. MySQL InnoDB ClusterSet !! 65

Slide 89

Slide 89 text

The Disaster Recovery Evolution ! available since MySQL 8.0.27 fencing support since 8.0.28 MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 66

Slide 90

Slide 90 text

The result of several transformations during previous releases: 8.0.22: Automatic Connection Failover (Async Replication Channels) 8.0.23: Automatic Connection Failover (Async Replication Channels using Group Replication) 8.0.24: transformation of skip-replica-start en global, persistent and read-only variable 8.0.26: Actions added for Group Replication's members (ex: super_read_only configurable) 8.0.26: Specific UUID added for structural events change of the Group (View_change_log_event) 8.0.27: Asynchronous Replication Channel automatically follows the Primary. Copyright @ 2024 Oracle and/or its affiliates. 67

Slide 91

Slide 91 text

Slide 92

Slide 92 text

MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. More complex architectures can be deployed ! 68

Slide 93

Slide 93 text

MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. More complex architectures can be deployed ! 68

Slide 94

Slide 94 text

Slide 95

Slide 95 text

On the Cluster's Primary Instance: JS > cluster=dba.getCluster() JS > cs=cluster.createClusterSet('mydomain') We can already check the status: JS > cs.status() Now let's create the Replica Cluster: JS > cluster2=cs.createReplicaCluster( 'dc2-mysql-4', 'mycluster2') And we add the other 2 instances: JS > cluster2.addInstance( 'dc2-mysql-5') JS > cluster2.addInstance( 'dc2-mysql-6') Finally, we need to bootstrap another time MySQL Router: # mysqlrouter --bootstrap \ clusteradmin@single-mysql:3306 \ --conf-use-gr-notifications \ --user mysqlrouter --force # systemctl restart mysqlrouter MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 69

Slide 96

Slide 96 text

MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. seconds 0 RPO ? ? RTO possible rpo>0 rto > 70

Slide 97

Slide 97 text

Extra Since MySQL 8.0.19, the connectors also support dns-srv which, together with a discovery service such as Consul, can replace MySQL Router when it is impossible to install it on the application servers: Copyright @ 2024 Oracle and/or its affiliates. 71

Slide 98

Slide 98 text

Slide 99

Slide 99 text

Slide 100

Slide 100 text

Scaling Reads Evolution ! available since MySQL 8.1.0 single or multiple replicas replicate from Primary or not MySQL InnoDB Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 73

Slide 101

Slide 101 text

Added support for Read Replicas with MySQL InnoDB Cluster and MySQL InnoDB ClusterSet: MySQL 8.1 - Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 74

Slide 102

Slide 102 text

Read / Write Split Evolution ! available since MySQL 8.2.0 long expected feature totally transparent MySQL Transparent Read/Write Splitting Copyright @ 2024 Oracle and/or its affiliates. 75

Slide 103

Slide 103 text

New routing policy with transparent read/write splitting: [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 MySQL 8.2 - Transparent Read/Write Splitting Copyright @ 2024 Oracle and/or its affiliates. 76

Slide 104

Slide 104 text

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" } } } } } } MySQL 8.2 - Transparent Read/Write Splitting Copyright @ 2024 Oracle and/or its affiliates. 77

Slide 105

Slide 105 text

MySQL 8.2 - Transparent Read/Write Splitting 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 @ 2024 Oracle and/or its affiliates. 78

Slide 106

Slide 106 text

MySQL 8.4 LTS MySQL Shell & Router - better integration Copyright @ 2024 Oracle and/or its affiliates. 79

Slide 107

Slide 107 text

MySQL Shell & MySQL Router With MySQL 8.4, MySQL Shell is now able to configure 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. 80

Slide 108

Slide 108 text

Slide 109

Slide 109 text

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. 81

Slide 110

Slide 110 text

Slide 111

Slide 111 text

Slide 112

Slide 112 text

Slide 113

Slide 113 text

Slide 114

Slide 114 text

Slide 115

Slide 115 text

Slide 116

Slide 116 text

Slide 117

Slide 117 text

Slide 118

Slide 118 text

Slide 119

Slide 119 text

Slide 120

Slide 120 text

MySQL Shell is now included in Visual Studio Code: Copyright @ 2024 Oracle and/or its affiliates. 92

Slide 121

Slide 121 text

Slide 122

Slide 122 text

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