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

MySQL Webinar, presented on the 25th of April, 2024.

Summary:

MySQL solutions enable the deployment of diverse Database Architectures tailored to specific needs, including High Availability, Disaster Recovery, and Read Scale-Out.

With MySQL Shell's AdminAPI, administrators can seamlessly set up, manage, and monitor these solutions, ensuring efficiency and ease of use in their administration. MySQL Router, on the other hand, provides transparent routing from the application traffic to the backend servers in the architectures, requiring minimal configuration.

Completely built in-house and supported by Oracle, these solutions have been adopted by enterprises of all sizes for their business-critical applications.

In this presentation, we'll delve into various database architecture solutions to help you choose the right one based on your business requirements. Focusing on technical details and the latest features to maximize the potential of these solutions.

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

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

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

4. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.4
Business Requirements

5. 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
5Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Business Requirements

6. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.6
MySQLArchitectures

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

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

9. Disaster Tolerance Solution for InnoDB
Clusters deployments in alternate
locations
•HighAvailability(Failurewithina Region)
•RPO = 0
•RTO = seconds(automaticfailover)
•DisasterRecovery(RegionFailure)
•RPO != 0
•RTO = minutes ormore (manual failover)
•No write performance impact
9Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLInnoDBClusterSet

10. Read Scale-out
•Add any amount of asyncread replicasto a
Cluster
•Replicate/Failover from
•PRIMARY
•SECONDARIES
•LIST of candidates
Fully supported on
•InnoDBCluster
•InnoDBClusterSet
10Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
!New in 8.1.0MySQLInnoDBCluster Read Replicas

11. 11Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLInnoDBClusterSetwith 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

12. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.12
What architecture fits my requirements?

13. Single Region
MySQLInnoDBCluster
•RPO = 0
•RTO = Seconds
MySQLInnoDBReplicaSet
•RPO != 0
•RTO = Minutes or more (Manual failover)
13Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
High Availability
Best write performanceManual Failover!"
Automatic failover"

14. Multi Region
14Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
High Availability
MySQLInnoDBCluster: 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
"
!
!
!

15. Multi Region
15Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Disaster Recovery
MySQLInnoDBClusterSet
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. 16Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Read Scale-Out
MySQLInnoDBCluster Read Replicas
Read Intensive Workloads
Offload Primary or Secondaries
Dedicated instances for other purposes
Additional redundancy for the dataset
"
"
"
"

17. Absolutely...
17Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Complex?

18. User Requirements
Copyright © 2024, Oracle and/or its affiliates. All rights reserved.18
Easy to deploy1

19. User Requirements
Copyright © 2024, Oracle and/or its affiliates. All rights reserved.19
Easy to deploy12Easy to maintain

20. User Requirements
Copyright © 2024, Oracle and/or its affiliates. All rights reserved.20
Easy to deploy12Easy to maintainEasy to monitor3

21. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.21
MySQLShell AdminAPI

22. 22Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLShell AdminAPI
Makesiteasy
-----------------------------------------------------------------
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
-----------------------------------------------------------------
-
InnoDBReplicaSet
1
23

23. 23Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLShell AdminAPI
Makesiteasy
-----------------------------------------------------------------
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
-----------------------------------------------------------------
-
InnoDBCluster
1
23

24. 24Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLShell AdminAPI
Makesiteasy
--------------------------------------------------------
Action Command # Calls
--------------------------------------------------------
Create Topology c.create_cluster_set(…)1
Create Replica Cluster cs.create_replica_cluster(…)2
Addinstancesto Replica rc.add_instance(…)3
--------------------------------------------------------
SUM: 6
--------------------------------------------------------
InnoDBClusterSet
1 2
34

25. 25Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLShell AdminAPI
Makesiteasy
-------------------------------------------------------------
Action Command # Calls
-------------------------------------------------------------
AddReadReplicas c.add_replica_instance(…)3
Configure Router behaviorc.set_routing_option(…)1
-------------------------------------------------------------
SUM: 4
-------------------------------------------------------------
InnoDBCluster Read Replicas
123

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

27. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.27
MySQLRouter

28. 28Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Architecture
Harness
Loader
routingconnection
_pool
UtilLibs
metadata_cachehttp_serverF
r
o
n
t
e
n
d
MySQL Shell
...
MySQLMySQLMySQL

29. 29Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Built-in plugins
routingRouting endpoints logic
destination_statusKeep track of the state of the routing destinations
connection_poolConnection pool
metadata_cacheKeep track of the MySQL Architectures state / metadata changes
loggerLogging utility
syslogUni based OSes logging: syslog
eventlogWindows OSes logging: eventlog
http_serverHTTP server to handle REST API request
http_auth_realmAuthentication realm for the http_server
http_auth_backendAuthentication backend for the http_server
~/mysql-server/router/src/

30. 30Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Built-in plugins
io Abstraction for IO OS layer
rest_apiGeneral REST API handler
rest_metadata_cacheMetadata cache REST API handler
rest_routerGlobal Router REST API handler
rest_routingRouting endpoint REST API handler
router_opensslOpenSSL library integration
router_protobufProtobuflibrary integration
~/mysql-server/router/src/

31. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.31
Router and MySQLArchitectures

32. 32Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Core component of MySQLArchitectures
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
•InnoDBCluster
•InnoDBReplicaSet
•InnoDBClusterSet
•InnoDBCluster Read Replicas
•3 TCP Ports:
•PRIMARY traffic
•SECONDARY traffic
•RW splitting
!New in 8.1.0
!New in 8.2.0

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

34. •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
34Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
GR Notifications
mysql_innodb_cluster_metadata
metadata_cache
MySQL Shell
GR
MySQL
MySQL Router

35. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.35
Deployment Strategies

36. It’s possible to use Router without bootstrapping, however…
36Copyright © 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

37. Auto-configurationfor 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
37Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Bootstrapping
MySQLRouter

38. 38Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Deploying MySQLRouter
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. 39Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Deploying MySQLRouter
It’s recommendedto 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. 40Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Deploying MySQLRouter
It’s recommendedto 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 multiplemachines under a VIP.

41. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.41
Advanced Features

42. •Based on a Connection Pool
•Re-useserver-side connections that the client wanted to close,
saving the setup costs of establishing new ones
•Shareserver-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
42Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Connection Sharing and Reuse
MySQLRouter

43. •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
43Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Connection Sharing and Reuse
MySQLRouter

44. •Built on top of the HTTP Server plugin
•Follows the OPENAPI 2.0 spec
•Exposes a Swaggerfile 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
44Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
REST API

45. 45Copyright © 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. 46Copyright © 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”
},
~

47. 47Copyright © 2024, Oracle and/or its affiliates. All rights reserved.

48. 48Copyright © 2024, Oracle and/or its affiliates. All rights reserved.

49. MySQLREST 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/HeatWaveas metadata storage
•Auto REST for tables, views, and procedures
•GUI Frontend with MySQL Shell for VSCode
49Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLREST Service (MRS)

50. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.50
Latest Additions / MySQLRouter

51. •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
•Clientand Serverside caches, configurable with:
•_ssl_session_cache_mode: enable/disable
•_ssl_session_cache_size: max number of cached sessions
•_ssl_session_cache_timeout: cache timeout
51Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
TLS Session Caching!New in 8.1.0
MySQLRouter

52. •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 reador 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
52Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
R/W Splitting
!New in 8.2.0

53. 53Copyright © 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 tlbVALUES (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_attributesrouter.access_moderead_write;
mysqlsh-sql> select@@port; // PRIMARY
~

54. 54Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
R/W Splitting
•The query is sent to the PRIMARY if the
access_modeis read_write
•The query is sent to the SECONDARY if the
access_modeis read_only
•If the access_modeis auto, a query is sent to
a SECONDARYif:
•Inside a READ_ONLYtransaction, 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

55. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.55
Latest Additions / MySQLShell

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

57. 57Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Security
38Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Security

58. 58Copyright © 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. 59Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Router management
•More control over Router configuration
•stats_updates_frequency8.1.0
•read_only_targets8.1.0
•all
•read_replicas
•secondaries
•unreachable_quorum_allowed_traffic8.2.0

60. 60Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Router management

61. 61Copyright © 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>

62. 62Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
Router management

63. 63Copyright © 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. 64Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
MySQLArchitectures
•InnoDBCluster Read Replicas 8.1.0
•InnoDBReplicaSetnew commands: 8.3.0
•.rescan()
•.dissolve()
•.describe()
•Support fine-grained replication options 8.2.0
•SOURCE_*, NETWORK_NAMESPACE
•ClusterSetasyncchannel & ReplicaSet

65. 65Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
InnoDBCluster 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")
(...)
// ConfipyreRouter to use only Read Replicas for R/O traffic
mysqlsh-js> cluster.set_routing_option("read_only_targets", "read_replicas"})
(...)
~/mysql_webinar

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

67. Copyright © 2024, Oracle and/or its affiliates. All rights reserved.67
Thank you!
Questions?Join our Slack channel
bit.ly/mysql-slack

68. 68Copyright © 2024, Oracle and/or its affiliates. All rights reserved.68