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

In this session, we cover the different deployment possibilities of the MySQL architecture depending on the business requirements for the data.
We deploy some architectures and see how to evolve to the next one.
The attendees will learn how to migrate from a single instance to High Availability with Data Recovery across Data Centers with minimal application downtime.

The session covers the new MySQL Solutions like InnoDB ReplicaSet, InnoDB Cluster, and InnoDB ClusterSet.
Attendees will get familiar with the MySQL Shell Admin API and MySQL Router.

Transcript

1. 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
2. The Journey To MySQL InnoDB ClusterSet 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 🇧🇪 https://lefred.be https://github.com/lefred Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 4
5. Best Practices in 2024 configuration parameters Copyright @ 2024 Oracle and/or its affiliates. 5
6. 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
7. Data Recovery Metrics Understanding RPO and RTO Copyright @ 2024 Oracle and/or its affiliates. 7
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 @ 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. 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
10. Evolution to High Availability MySQL 8.x Copyright @ 2024 Oracle and/or its affiliates. 9
11. Copyright @ 2024 Oracle and/or its affiliates. 10
12. 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
13. One single MySQL instance Copyright @ 2024 Oracle and/or its affiliates. / / RPO ? ? RTO 12
14. 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
15. Next Level (level up) The database becomes more important, loosing it might be an issue... Copyright @ 2024 Oracle and/or its affiliates. 14
16. 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
17. 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
18. 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
19. Physical Backups Logical Backups For logical backups, please use MySQL Shell Dump & Load Utility ! Backups Copyright @ 2024 Oracle and/or its affiliates. 15
20. One single MySQL instance with daily backups Copyright @ 2024 Oracle and/or its affiliates. hours 1 day RPO ? ? RTO 16
21. 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
22. 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
23. 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
24. 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
25. 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
26. One single MySQL instance with daily backups and binlogs Copyright @ 2024 Oracle and/or its affiliates. hours minutes RPO ? ? RTO 20
27. 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
28. 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
29. 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
30. 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
31. 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
32. 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
33. 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
34. 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 day 1 BACKUPS pontential recovery points Binlogs day 2 day 3 day 4 .001 .002 .003 .004 .005 .006 .007 .008 .009 .010 } 26
35. 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
36. 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
37. 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
38. 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
39. 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
40. with MySQL restart Enabling GTIDs Copyright @ 2024 Oracle and/or its affiliates. 30
41. with MySQL restart without MySQL restart Enabling GTIDs Copyright @ 2024 Oracle and/or its affiliates. 30
42. Saving Binlogs Copyright @ 2024 Oracle and/or its affiliates. 31
43. 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
44. 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. 32
45. 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
46. One single MySQL instance with daily backups and external binlogs archiving Copyright @ 2024 Oracle and/or its affiliates. hours < 1 second RPO ? ? RTO 33
47. 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
48. 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
49. 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
50. 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
51. 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
52. MySQL InnoDB ReplicaSet Copyright @ 2024 Oracle and/or its affiliates. 37
53. 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
54. MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 39
55. MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 40
56. MySQL InnoDB ReplicaSet - examples Copyright @ 2024 Oracle and/or its affiliates. 41
57. 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
58. MySQL Router As usual, this is also visible in MySQL Shell Copyright @ 2024 Oracle and/or its affiliates. 43
59. MySQL InnoDB ReplicaSet - Manual Failover Copyright @ 2024 Oracle and/or its affiliates. MySQL Shell is connected to MySQL InnoDB ReplicaSet via MySQL Router. 44
60. MySQL InnoDB ReplicaSet - Manual Failover Copyright @ 2024 Oracle and/or its affiliates. 45
61. MySQL RelicaSet (*)with point-in-time recovery Copyright @ 2024 Oracle and/or its affiliates. minutes < 1 second RPO ? ? RTO rto > 46
62. Next Level It's time to not loose data anymore and recover as fast as possible Copyright @ 2024 Oracle and/or its affiliates. 47
63. 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
64. 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
65. 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
66. 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
67. MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 50
68. 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
69. 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
70. MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. 52
71. MySQL InnoDB Cluster Copyright @ 2024 Oracle and/or its affiliates. seconds 0 RPO ? ? RTO rto > 53
72. 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
73. 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
74. 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. 55
75. 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
76. 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
77. Consistency: BEFORE (READ) Copyright @ 2024 Oracle and/or its affiliates. 57
78. 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
79. 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
80. Consistency: AFTER (WRITE) Copyright @ 2024 Oracle and/or its affiliates. 59
81. 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
82. 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
83. Pause !! Copyright @ 2024 Oracle and/or its affiliates. 62
84. 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
85. Next Level We want to survive to a disaster at the datacenter Copyright @ 2024 Oracle and/or its affiliates. 64
86. Next Level (level up) And if something happens to our data center ?? Copyright @ 2024 Oracle and/or its affiliates. 65
87. 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
88. 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
89. 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
90. 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
91. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 68
92. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. More complex architectures can be deployed ! 68
93. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. More complex architectures can be deployed ! 68
94. 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') MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. 69
95. 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
96. MySQL InnoDB ClusterSet Copyright @ 2024 Oracle and/or its affiliates. seconds 0 RPO ? ? RTO possible rpo>0 rto > 70
97. 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
98. 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
99. More with Innovation Releases Copyright @ 2024 Oracle and/or its affiliates. 72
100. 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
101. 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
102. 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
103. 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
104. 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
105. MySQL 8.2 - Transparent Read/Write Splitting 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. 78
106. MySQL 8.4 LTS MySQL Shell & Router - better integration Copyright @ 2024 Oracle and/or its affiliates. 79
107. 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
108. 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. 81
109. 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
110. Evolution Summary Copyright @ 2024 Oracle and/or its affiliates. 82
111. Copyright @ 2024 Oracle and/or its affiliates. 83
112. Copyright @ 2024 Oracle and/or its affiliates. 84
113. Copyright @ 2024 Oracle and/or its affiliates. 85
114. Copyright @ 2024 Oracle and/or its affiliates. 86
115. Copyright @ 2024 Oracle and/or its affiliates. 87
116. Copyright @ 2024 Oracle and/or its affiliates. 88
117. Copyright @ 2024 Oracle and/or its affiliates. 89
118. Copyright @ 2024 Oracle and/or its affiliates. 90
119. Copyright @ 2024 Oracle and/or its affiliates. 91
120. MySQL Shell is now included in Visual Studio Code: Copyright @ 2024 Oracle and/or its affiliates. 92
121. Copyright @ 2024 Oracle and/or its affiliates. 93
122. Share your ❤️ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 94
123. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 95