Choose the Right MySQL Architecture on OCI
As organizations that rely on open-source databases, such as Oracle MySQL HeatWave, migrate from self-managed, on-premises solutions to managed cloud database services (such as MySQL database) to lower costs and improve operational efficiency, it is important to learn how to choose the right MySQL architecture on Oracle Cloud Infrastructure (OCI).
Oracle MySQL HeatWave is a managed database service on OCI that aims to provide a highly scalable and available database service without the need to plan and carry out tedious day-to-day operational tasks such as database provisioning, backup, patches, and upgrades. If you were to adopt Oracle MySQL HeatWave for your workloads on OCI, you are not only reducing the current operational cost of your self-managed databases but also to be able to focus on high-value and innovation works. To achieve the maximum benefits of running your workloads on Oracle MySQL HeatWave and OCI, you would need to understand Oracle MySQL HeatWave and OCI to design a flexible and scalable cloud architecture that would allow you to grow and expand your cloud usage based on your business needs and requirements.
Architecture
This reference architecture aims to provide you with relevant technical information to help your design and optimize a flexible and scalable MySQL architecture on OCI to support your workload requirements.
The scope of this reference architecture assumes the following non-functional requirements and specifications:
| Domains | Requirements | Descriptions |
|---|---|---|
| Availability | R1 | The architecture should be highly available to support 24/7 operation with service uptime of at least 99.95% |
| R2 |
|
|
| Scalability | R3 | The architecture should be flexible and scalable to support the workloads |
| Security | R4 | The architecture must be highly secured with access control and authorization mechanisms |
Based on these requirements, the Oracle MySQL HeatWave High Availability model is selected together with other OCI services to build the architecture for your workloads.
This reference architecture is illustrated in three different viewpoints to address the concerns of the infrastructure architects, information architects, DevOps engineers, and operation engineers.
Infrastructure view
mysql-oci-architecture-infrastructure-oracle.zip
In this architecture view, a regional virtual cloud network is provisioned with public and private subnets in a single OCI region comprising three availability domains (ADs). The Oracle MySQL HeatWave High Availability model will deploy three redundant MySQL database instances across the three ADs in a multi-AD OCI region or three fault domains in a single-AD OCI region.
This architectural view illustrates the application deployment options and network connectivity options for Oracle MySQL HeatWave:
- Oracle MySQL HeatWave is deployed in the secured private subnet without any public access from the internet.
- Connectivity options to access Oracle MySQL HeatWave:
- Via the Internet using Oracle Cloud Infrastructure Load Balancing.
- Via a VPN between your data centers and OCI regions using OCI
Dynamic Routing Gateway:
- OCI site-to-site VPN
- Oracle Cloud Infrastructure FastConnect
- Generally, there are three application deployment patterns to access Oracle MySQL HeatWave in OCI:
- Applications deployed to Oracle Cloud Infrastructure Compute instances or Oracle Container Engine for Kubernetes instances (the most preferred pattern with both the application and MySQL Database collocated in the same OCI cloud region).
- Applications hosted in your data center.
- Applications hosted in non-OCI cloud (such as Amazon Web Services, Microsoft Azure, and others).
Infrastructure (scalability/redundancy) view
mysql-oci-architecture-scalability-oracle.zip
In this architecture view, the primary OCI region with Oracle MySQL HeatWave High Availability model is interconnected with another OCI region via OCI Dynamic Routing Gateway. This architectural view illustrates MySQL scalability within the same OCI region as well as redundancy across another OCI region:
- The primary MySQL database instances can be scaled easily by deploying the "Managed Read Replica" instances within the same OCI region to scale your read transactions (see Explore More).
- The primary MySQL instances can be replicated to a MySQL replica deployed in another OCI region to provide redundancy for your workloads (see Explore More).
Database access view
This architecture view illustrates the tools options to work with Oracle MySQL HeatWave for database administrators and developers:
- OCI tools such as OCI Database Tools (SQL Worksheet) and Oracle Cloud Infrastructure Cloud Shell with the pre-installed with MySQL Shell tool can be accessed using web browsers over the public internet to work with MySQL.
- Oracle Cloud Infrastructure
Bastion can be used to connect your tools with Oracle MySQL HeatWave via the Internet.
- Oracle Cloud Infrastructure Bastion service can be used to connect your tools with Oracle MySQL Database Service via the internet.
- Visual Studio Code with the preview version of MySQL Shell plugin can be used as well with minimal configuration to connect to Oracle MySQL HeatWave.
mysql-oci-architecture-database-access-oracle.zip
Operation view
This architectural view illustrates the monitoring aspects of Oracle MySQL HeatWave using Oracle Cloud Observability and Management Platform services:
- Create relevant thresholds on Oracle MySQL HeatWave console to trigger alarms for operation support act.
- Event-driven monitoring using OCI Events to track specific MySQL events (for example, database restart).
- Real-time monitoring using OCI Dashboard to create customize OCI Dashboard on specific MySQL instances.
mysql-oci-architecture-operation-oracle.zip
- Region
An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).
- Availability domains
Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.
- Virtual cloud network (VCN) and subnets
A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.
- Load balancer
The Oracle Cloud Infrastructure Load Balancing service provides automated traffic distribution from a single entry point to multiple servers in the back end.
- Security list
For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.
- Compute
The Oracle Cloud Infrastructure Compute service enables you to provision and manage compute hosts in the cloud. You can launch compute instances with shapes that meet your resource requirements for CPU, memory, network bandwidth, and storage. After creating a compute instance, you can access it securely, restart it, attach and detach volumes, and terminate it when you no longer need it.
- Events
Oracle Cloud Infrastructure services emit events, which are structured messages that describe the changes in resources. Events are emitted for create, read, update, or delete (CRUD) operations, resource lifecycle state changes, and system events that affect cloud resources.
- Notifications
The Oracle Cloud Infrastructure Notifications service broadcasts messages to distributed components through a publish-subscribe pattern, delivering secure, highly reliable, low latency, and durable messages for applications hosted on Oracle Cloud Infrastructure.
- Bastion service
Oracle Cloud Infrastructure Bastion provides restricted and time-limited secure access to resources that don't have public endpoints and that require strict resource access controls, such as bare metal and virtual machines, Oracle MySQL Database Service, Autonomous Transaction Processing (ATP), Oracle Container Engine for Kubernetes (OKE), and any other resource that allows Secure Shell Protocol (SSH) access. With Oracle Cloud Infrastructure Bastion service, you can enable access to private hosts without deploying and maintaining a jump host. In addition, you gain improved security posture with identity-based permissions and a centralized, audited, and time-bound SSH session. Oracle Cloud Infrastructure Bastion removes the need for a public IP for bastion access, eliminating the hassle and potential attack surface when providing remote access.
- FastConnect
Oracle Cloud Infrastructure FastConnect provides an easy way to create a dedicated, private connection between your data center and Oracle Cloud Infrastructure. FastConnect provides higher-bandwidth options and a more reliable networking experience when compared with internet-based connections.
- MySQL HeatWave
Oracle MySQL Database Service is a fully-managed database service that lets developers quickly develop and deploy secure, cloud-native applications using the world’s most popular open source database. Oracle MySQL HeatWave is a new, integrated, high-performance, in-memory query accelerator for Oracle MySQL Database Service that accelerates MySQL performance for analytics and transactional queries.
Recommendations
Use the following recommendations as a starting point to design the OCI architecture according to your workload requirements.
- VCN
- Use regional subnets for flexibility purposes.
- When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources you plan to attach to subnets in the VCN.
- Use CIDR blocks that are within the standard private IP address
space:
10.0.0.0 - 10.255.255.255172.16.0.0 - 172.31.255.255192.168.0.0 - 192.168.255.255
- Select CIDR blocks that don't overlap with any other network (in OCI, your on-premises data center, or another cloud provider) to which you intend to set up private connections.
- After you create a VCN, you can change, add, and remove its CIDR blocks.
- When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary. Oracle MySQL Database Service always lives in a private subnet.
- Security
- For resources requiring maximum security, Oracle recommends using security zones. A security zone is a compartment associated with an Oracle-defined recipe of security policies that are based on best practices. For example, the resources in a security zone must not be accessible from the public internet and they must be encrypted using customer-managed keys. When you create and update resources in a security zone, OCI validates the operations against the policies in the security-zone recipe and denies operations that violate any policies.
- Cloud Guard
- Clone and customize the default recipes provided by Oracle to create custom detector and responder recipes. These recipes enable you to specify what type of security violations generate a warning and what actions are allowed to be performed on them. For example, you might want to detect Oracle Cloud Infrastructure Object Storage buckets with public visibility.
- Network security groups (NSGs)
You can use NSGs to define a set of ingress and egress rules that apply to specific VNICs. We recommend using NSGs rather than security lists because NSGs enable you to separate the VCN's subnet architecture from the security requirements of your application.
Considerations
When standardizing and operationalizing Oracle MySQL HeatWave as your core cloud database service, consider these other cloud service configurations for ease of operation and flexibility.
- Network connectivity
- Be aware of the soft limit of various VCN limits when designing your cloud network, for example, 50 VCN limit per OCI tenancy. These limits can be changed by submitting a service request to OCI support.
- Oracle MySQL HeatWave requires a minimum set of private IP addresses for maintenance
purposes:
- MySQL Standalone requires three private IP addresses
- MySQL High Availability requires seven private IP addresses
- Oracle Cloud Infrastructure Bastion service is limited to a maximum of three-hour usage, hence if you require a highly available bastion service for your applications or development purposes, it is better to use a dedicated Oracle Cloud Infrastructure Compute instance as the bastion host to connect to MySQL instances.
- Operability
Make sure of OCI Dashboard to build a customized dashboard for your fleet of MySQL instances for your service operation.
- Availability
If you are planning to build a MySQL replica as a backup in another region, you need to consider the network latency between the primary and the backup region. OCI provides inter-region network latency information to help you decide which region to be used as your backup region for your primary region. The other consideration factor for MySQL replica is the time duration (binlog_expire_logs_seconds) to keep the binary transaction logs before the logs are deleted, the default time duration to keep the binary transaction log is 3,600 seconds or 60 minutes. If you are experiencing severe network latency between your primary and backup regions, you may want to increase this parameter so that the transaction logs are kept longer so they can be transmitted and applied at the MySQL replica in the backup region.
- Portability and flexibility
- Make use of OCI Domain Name Service (DNS) to resolve MySQL hostname for portability. Assign a custom hostname based on your naming convention to the MySQL instance during creation so that the hostname can be resolved by the OCI DNS as the VCN resolver.
- Use the DNS hostname of the Oracle Cloud Infrastructure Load Balancing for managed MySQL read-replica instances to minimize changes to your application code for flexibility purposes. You can find out the assigned DNS hostname of this Oracle Cloud Infrastructure Load Balancing in OCI DNS service console.
Explore More
Learn more about Oracle MySQL HeatWave.
Review these additional resources:
- Free MySQL Training Courses
- Migrate from AWS RDS to MySQL HeatWave on OCI
- Migrate from on-premises MySQL to MySQL HeatWave on OCI
- Migrate from AWS RedShift to MySQL HeatWave on OCI
- Best practices framework for Oracle Cloud Infrastructure
- Read Replicas and Load Balancer
- Outbound Replication
- Service Limits
- Creating a Virtual Cloud Network
- Inter-Region Latency