Mastering MySQL Database Architecture: Deep Dive into MySQL Shell and MySQL Router

Mastering MySQL Database Architectures MySQL Webinar Series Miguel Araújo Senior Principal Software Engineer MySQL, Oracle April 23, 2024 Deep Dive into MySQL Shell and MySQL Router

The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle's product remains at the sole discretion of Oracle. Safe Harbor Statement Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 2

00:00 Business Requirements & MySQL Architectures 00:15 MySQL Shell: AdminAPI & Features 00:25 MySQL Router: Architecture & Features 00:35 Deployment Strategies 00:40 Advanced Features 00:45 Latest Additions 00:50 Q & A Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 3 ~/mysql_webinar Agenda

Concepts – RTO & RPO • RTO: Recovery Time Objective • How long does it take to recover from a single failure • RPO: Recovery Point Objective • How much data can be lost when a failure occurs Types of Failures • High Availability: • Single Server Failure, Network Partition • Disaster Recovery: • Full Region / Network failure • Human Error: • Little Bobby Tables 5 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Business Requirements

'classic', 'asynchronous' Replication based Solution • Manual failover & switchover • Asynchronous reads • Good write performance 7 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL InnoDB ReplicaSet RPO != 0 RTO = minutes or more (manual failover)

High Availability solution based on Group Replication • Automatic failover / Fault Tolerance • Automatic membership changes • Network partition handling • Consistency 8 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL InnoDB Cluster RPO = 0 RTO = seconds (automatic failover)

Disaster Tolerance Solution for InnoDB Clusters deployments in alternate locations • High Availability (Failure within a Region) • RPO = 0 • RTO = seconds (automatic failover) • Disaster Recovery (Region Failure) • RPO != 0 • RTO = minutes or more (manual failover) • No write performance impact 9 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL InnoDB ClusterSet

Read Scale-out • Add any amount of async read replicas to a Cluster • Replicate/Failover from • PRIMARY • SECONDARIES • LIST of candidates Fully supported on • InnoDB Cluster • InnoDB ClusterSet 10 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. ! New in 8.1.0 MySQL InnoDB Cluster Read Replicas

11 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL InnoDB ClusterSet with Read Replicas Flexible • Add/Remove Read Replicas online • Configure Router behavior dynamically • Choose where to route traffic Failover • Automatic connection failover • List of potential sources automatic or manually populated ! New in 8.1.0

Single Region MySQL InnoDB Cluster • RPO = 0 • RTO = Seconds MySQL InnoDB ReplicaSet • RPO != 0 • RTO = Minutes or more (Manual failover) 13 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. High Availability Best write performance Manual Failover 🔴 🟢 Automatic failover 🟢

Multi Region 14 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. High Availability MySQL InnoDB Cluster: Deployed over multiple regions Multi-Region Multi-Primary 3 DC Requires very stable WAN Write performance affected by latency between DCs • RPO = 0 • RTO = Seconds 🟢 🔴 🔴 🔴

Multi Region 15 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Disaster Recovery MySQL InnoDB ClusterSet RPO = 0 & RTO = seconds within Region (HA) Write performance (no sync to other region required) Higher RTO: Manual failover RPO != 0 when region fails • RPO != 0 • RTO = Minutes or more (Manual Failover) 🔴 🔴 🟢 🟢

16 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Read Scale-Out MySQL InnoDB Cluster Read Replicas Read Intensive Workloads Offload Primary or Secondaries Dedicated instances for other purposes Additional redundancy for the dataset 🟢 🟢 🟢 🟢

22 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL Shell AdminAPI Makes it easy ----------------------------------------------------------------- Action Command # Calls ----------------------------------------------------------------- Configure instances dba.configure_replica_set_instance(…) 3 Create Topology dba.create_replica_set(…) 1 Setup Admin Account rs.setup_admin_account(…) 1 Add instances rs.add_instance(…) 2 ----------------------------------------------------------------- SUM: 7 ----------------------------------------------------------------- - InnoDB ReplicaSet 1 2 3

23 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL Shell AdminAPI Makes it easy ----------------------------------------------------------------- Action Command # Calls ----------------------------------------------------------------- Configure instances dba.configure_instance(…) 3 Create Topology dba.create_cluster(…) 1 Setup Admin Account c.setup_admin_account(…) 1 Add instances c.add_instance(…) 2 ----------------------------------------------------------------- SUM: 7 ----------------------------------------------------------------- - InnoDB Cluster 1 2 3

24 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL Shell AdminAPI Makes it easy -------------------------------------------------------- Action Command # Calls -------------------------------------------------------- Create Topology c.create_cluster_set(…) 1 Create Replica Cluster cs.create_replica_cluster(…) 2 Add instances to Replica rc.add_instance(…) 3 -------------------------------------------------------- SUM: 6 -------------------------------------------------------- InnoDB ClusterSet 1 2 3 4

25 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL Shell AdminAPI Makes it easy ------------------------------------------------------------- Action Command # Calls ------------------------------------------------------------- Add Read Replicas c.add_replica_instance(…) 3 Configure Router behavior c.set_routing_option(…) 1 ------------------------------------------------------------- SUM: 4 ------------------------------------------------------------- InnoDB Cluster Read Replicas 1 2 3

• Sandbox management • Configuration checker & applier • Account management • MySQL Architectures management • Integrated provisioning • Configuration management • MySQL Router management • Follows best practices 26 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. General Features AdminAPI

28 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Architecture Harness Loader routing connection _pool Util Libs metadata_cache http_server F r o n t e n d MySQL Shell ... MySQL MySQL MySQL

29 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Built-in plugins routing Routing endpoints logic destination_status Keep track of the state of the routing destinations connection_pool Connection pool metadata_cache Keep track of the MySQL Architectures state / metadata changes logger Logging utility syslog Unix based OSes logging: syslog eventlog Windows OSes logging: eventlog http_server HTTP server to handle REST API request http_auth_realm Authentication realm for the http_server http_auth_backend Authentication backend for the http_server ~/mysql-server/router/src/

30 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Built-in plugins io Abstraction for IO OS layer rest_api General REST API handler rest_metadata_cache Metadata cache REST API handler rest_router Global Router REST API handler rest_routing Routing endpoint REST API handler router_openssl OpenSSL library integration router_protobuf Protobuf library integration ~/mysql-server/router/src/

32 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Core component of MySQL Architectures Transparent access to Database Architecture • Transparent client connection routing • Load balancing • Application connection failover • Little to no configuration needed • Stateless design • Part of the application stack • Full integration into MySQL Architectures • InnoDB Cluster • InnoDB ReplicaSet • InnoDB ClusterSet • InnoDB Cluster Read Replicas • 3 TCP Ports: • PRIMARY traffic • SECONDARY traffic • RW splitting ! New in 8.1.0 ! New in 8.2.0

• Needed to route queries to the appropriate backend of the topology • Refreshed each [metadata_cache::ttl] • Default: 0.5 sec • ClusterSet: 5 sec 33 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Metadata Cache mysql_innodb_cluster_metadata metadata_cache MySQL Shell GR MySQL MySQL Router

• Push notifications sent via X protocol: • group_replication/membership/quorum_loss • group_replication/membership/view • group_replication_status/role_change • group_replication/status/state_change • Router keeps an open connection to the X Plugin port waiting for push notifications • For every change, if needed, the metadata cache is updated • Allows reducing drastically the TTL 34 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. GR Notifications mysql_innodb_cluster_metadata metadata_cache MySQL Shell GR MySQL MySQL Router

It’s possible to use Router without bootstrapping, however… 36 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Manual configuration [DEFAULT] ... [routing:primary] bind_address = localhost bind_port = 3331 destinations = myserver_xyz:3306 routing_strategy = first-available [routing:secondaries] bind_address = localhost bind_port = 3332 destinations = myserver_foo:3306, myserver_bar:3306 routing_strategy = round-robin-with-fallback ~/testbase/router/my.conf

Auto-configuration for the MySQL Architecture • Fetches the topology Metadata information from one of the servers • Stores it in a dynamic file (data/state.json) • Registers itself in the Metadata schema • Creates a configuration file ready to be used • Creates daemon start/stop scripts 37 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Bootstrapping MySQL Router

38 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Deploying MySQL Router 1. Install MySQL Router 2. Bootstrap 3. Start it! $ mysqlrouter --bootstrap clusteradmin@brussels:3306 --directory my_router --account router_admin --conf-use-gr-notifications $ my_router/start.sh ~/testbase/router

39 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Deploying MySQL Router It’s recommended to deploy Router on the same host as the application and enable GR notifications. That allows: • Using sockets instead of TCP/IP • Decreasing network latency • Fine-grained account access • Scaling-out!

40 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Deploying MySQL Router It’s recommended to deploy Router on the same host as the application and enable GR notifications. That allows: • Using sockets instead of TCP/IP • Decreasing network latency • Fine-grained account access • Scaling-out! Alternatively, it’s possible to deploy multiple Routers in multiple machines under a VIP.

• Based on a Connection Pool • Re-use server-side connections that the client wanted to close, saving the setup costs of establishing new ones • Share server-side connections where the client is idling on an active connection, to reduce the number of open server-side connections freeing up resources bound to those idle connections 42 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Connection Sharing and Reuse MySQL Router

• Configurable with • connection_sharing (disabled by default) • connection_sharing_delay (1 by default) • Seconds to wait before moving an idle connection to the pool • idle_timeout (5 by default) • How many seconds to keep a connection in the pool after the client disconnects • max_idle_server_connections (disabled by default) • How many open connections can be kept in the pool after the client disconnects 43 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Connection Sharing and Reuse MySQL Router

• Built on top of the HTTP Server plugin • Follows the OPENAPI 2.0 spec • Exposes a Swagger file to describe the REST API: ü Metadata cache config ü Metadata cache status ü Metadata cache instances list ü Router status ü Routing plugin status ü Routes config / status / health / destination / connections ü Routes list ü Blocked hosts 44 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. REST API

45 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. $ curl -k -s -u miguel: https://localhost:8443/api/20190715/metadata/bootstrap/config | jq { "clusterName": "myCluster", "timeRefreshInMs": 500, "groupReplicationId": "1e6598b4-baab-11ee-adc4-d08e7912e4ee", "nodes": [ { "hostname": "127.0.0.1", "port": 3310 }, { "hostname": "127.0.0.1", "port": 3320 }, { "hostname": "127.0.0.1 ~

46 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. $ curl -k -s -u miguel: https://localhost:8443/api/20190715/routes | jq { "items": [ { "name": "bootstrap_ro” }, { "name": "bootstrap_rw” }, { "name": "bootstrap_rw_split” }, { "name": "bootstrap_x_ro” }, ~

MySQL REST Service • Fast and powerful way to serve data to client applications via a HTTPS REST interface • Implemented as a MySQL Router feature • Built on the concepts of ORDS, focusing on the strengths of MySQL • Focus on MySQL performance • Focus on MySQL scalability • Using MySQL/HeatWave as metadata storage • Auto REST for tables, views, and procedures • GUI Frontend with MySQL Shell for VSCode 49 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL REST Service (MRS)

• TLS handshakes are slow • Cache and resume TLS sessions from: • Client to Router • Router to Server • Saves time and resources by reducing the connection handshake • Enabled by default • Client and Server side caches, configurable with: • _ssl_session_cache_mode: enable/disable • _ssl_session_cache_size: max number of cached sessions • _ssl_session_cache_timeout: cache timeout 51 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. TLS Session Caching ! New in 8.1.0 MySQL Router

• Motivation: • 1 port to rule them all • Up to now, the application had to be aware of the type of transaction to use either the RW or the RO port • It’s performant, but limits the usage of Router • How it works: • Router classifies each query as read or write automatically and forwards to the appropriate backend • It’s also possible to manually or programmatically to specify the type of query using • ROUTER SET • query_attributes 52 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. R/W Splitting ! New in 8.2.0

53 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. R/W Splitting ! New in 8.2.0 mysqlsh-sql> SELECT 1; // SECONDARY mysqlsh-sql> INSERT INTO tlb VALUES (1) // PRIMARY mysqlsh-sql> START TRANSACTION READ_ONLY; // SECONDARY mysqlsh-sql> CREATE TEMPORARY TABLE tbl (id int); // SECONDARY mysqlsh-sql> SELECT * FROM tbl; // SECONDARY mysqlsh-sql> COMMIT; // SECONDARY mysqlsh-sql> query_attributes router.access_mode read_write; mysqlsh-sql> select @@port; // PRIMARY ~

54 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. R/W Splitting • The query is sent to the PRIMARY if the access_mode is read_write • The query is sent to the SECONDARY if the access_mode is read_only • If the access_mode is auto, a query is sent to a SECONDARY if: • Inside a READ_ONLY transaction, or • Router attribute for access mode set (router.access_mode), or • Outside a transaction, the connection allows sharing and the statement is a “read-only” statement • If none of the above is met, the query is sent to the PRIMARY

56 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Security • MySQL Communication Stack used by default 8.0.30 • Full TLS/SSL Support 8.0.33 • Encrypt Group Replication and Asynchronous replication channels • Certificate-based authentication for intra-node communication • Certificate-based authentication for Admin and Router accounts • Certificate-based authentication for Read Replicas 8.4.0

58 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Concurrency Control & Atomicity • Locking mechanism 8.0.33 • Prevent conflicting operations to run concurrently resulting in unexpected outcome • Supported on the whole API • Operations rollback • Avoid leaving the system / instance in a transient state

59 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Router management • More control over Router configuration • stats_updates_frequency 8.1.0 • read_only_targets 8.1.0 • all • read_replicas • secondaries • unreachable_quorum_allowed_traffic 8.2.0

61 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Router management • List Router Options 8.4.0 • New command: .router_options([options]) • Router exposes all its configuration in the Metadata • 3 verbosity levels • Available on: • <Cluster> • <ReplicaSet> • <ClusterSet>

63 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Router management 3 verbosity levels: • 0: includes only options that can be changed from Shell (default) • 1: Includes all global options and, per Router, the options that have a different value than the global • 2: includes all global and Router options

64 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. MySQL Architectures • InnoDB Cluster Read Replicas 8.1.0 • InnoDB ReplicaSet new commands: 8.3.0 • .rescan() • .dissolve() • .describe() • Support fine-grained replication options 8.2.0 • SOURCE_*, NETWORK_NAMESPACE • ClusterSet async channel & ReplicaSet

65 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. InnoDB Cluster Read Replicas // Default, follow primary mysqlsh-py> cluster.add_replica_instance("brussels:3006") (...) // Change to follow secondaries mysqlsh-py> cluster.set_instance:option("brussels:3006", {"replicationSources": "secondary"}) mysqlsh-py> cluster.rejoin_instance("brussels:3006") (...) // Confipyre Router to use only Read Replicas for R/O traffic mysqlsh-js> cluster.set_routing_option("read_only_targets", "read_replicas"}) (...) ~/mysql_webinar

66 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Deprecations 8.2.0 & Removals 8.4.0 • 5.7 support EOL’d Oct 2023 • dba.configureLocalInstance() • Cluster.checkInstanceState() • Command options: • ipWhitelist • connectToPrimary • clearReadOnly • failoverConsistency • multiMaster • groupSeeds • memberSslMode • queryMembers • user/password • interactive • waitRecovery • updateTopologyMode

Mastering MySQL Database Architecture: Deep Dive into MySQL Shell and MySQL Router

Mastering MySQL Database Architecture: Deep Dive into MySQL Shell and MySQL Router

More Related Content

What's hot

Similar to Mastering MySQL Database Architecture: Deep Dive into MySQL Shell and MySQL Router

More from Miguel Araújo

Recently uploaded

In this document

Mastering MySQL Database Architecture: Deep Dive into MySQL Shell and MySQL Router