Deploy a Highly Available MySQL InnoDB Cluster

MySQL is an open source object-relational database management system. It is highly extensible and scalable, and it supports data replication across multiple data centers.

Architecture

This reference architecture shows a typical three-node deployment of a MySQL InnoDB cluster and load-balanced application servers with MySQL routers on Oracle Cloud Infrastructure Compute instances.

The following diagram illustrates this reference architecture.

Description of mysql-innodb-cluster-oci.png follows
Description of the illustration mysql-innodb-cluster-oci.png

The architecture has the following components:

  • Region

    A region is a localized geographic area composed of one or more availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or 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.

  • Fault domains

    A fault domain is a grouping of hardware and infrastructure within an availability domain. Each availability domain has three fault domains with independent power and hardware. When you place Compute instances across multiple fault domains, applications can tolerate physical server failure, system maintenance, and many common networking and power failures inside the availability domain.

  • Virtual cloud network (VCN) and subnets

    Every Compute instance is deployed in a VCN that can be segmented into subnets. This architecture uses different public subnets to host the MySQL database servers, application servers, and the public load balancer. If your architecture requires it, you can use private subnets for the application and database servers.

  • Security lists

    For each subnet, you can create security rules that specify the source, destination, and type of traffic allowed in and out of the subnet. This architecture adds ingress rules for TCP ports 3306, 33060, and 33061 in the security list attached to the MySQL database server subnet. The MySQL server listens on TCP port 3306. TCP port 33060 is used by the X Protocol (from the MySQL router nodes to the database servers). Port 33061 is used for group replication communication.

    TCP port 33060 requires an egress rule in the security list attached to the application nodes subnet.

    Application-specific TCP ports require ingress rules on the security lists attached to the application server and load balancer subnets, like TCP port 80 for the load balancer.

  • Object Storage

    The Object Storage service is used for storing the database backups.

Recommendations

Your requirements might differ from the architecture described here. Use the following recommendations as a starting point.

  • Compute shapes

    This architecture uses an Oracle Linux 7.7 OS image with a VM.Standard2.1 shape for both the application server and the database server. You can choose a different shape if your application’s resource needs are different.

  • VCN

    When you create the VCN, determine how many IP addresses your cloud resources in each subnet require. Using Classless Inter-Domain Routing (CIDR) notation, specify a subnet mask and a network address range large enough for the required IP addresses. Use an address space that falls within the standard private IP address blocks.

    Select an address range that doesn’t overlap with your on-premises network, so that you can set up a connection between the VCN and your on-premises network later, if necessary.

    After you create the VCN, you can't change the address range.

    When you design the subnets, consider functionality and security requirements. All compute instances within the same tier or role should go into the same subnet.

    Use a regional subnet.

  • MySQL Server version

    Use MySQL Server version 8.0 for this architecture. We recommend MySQL Enterprise Edition as a best practice.

  • Load balancer

    Oracle Cloud Infrastructure has three load balancer shapes: 100 Mbps, 400 Mbps, and 8000 Mbps. This architecture uses the smallest shape (100 Mbps). If your application requires more bandwidth, use a larger shape.

Considerations

  • Scalability

    This architecture deploys one MySQL database server in each fault domain. It also has one application server with a MySQL router in each fault domain.

    You can vertically scale the MySQL InnoDB cluster by changing the virtual machine (VM) shape of each Compute node. Using a shape with a higher core count increases the memory allocated to the Compute instance and network bandwidth.

    You can scale out the MySQL InnoDB cluster by adding more nodes to each fault domain.

  • Application availability

    Fault domains provide the best resilience within a single availability domain. This architecture uses another approach to high availability: deploying Compute instances that perform the same tasks across multiple fault domains. This design removes a single point of failure by introducing redundancy.

    This architecture uses MySQL InnoDB Cluster on the database tier for its key capabilities like fault tolerance, zero data loss, automatic failover, and active update. If your application can use a simple replication architecture for the database tier and doesn’t require automatic failover capabilities, you can use MySQL InnoDB ReplicaSet.

    After the architecture is deployed, you can connect to the public IP address of the MySQL node by using SSH tools like PuTTY or Git Bash. You can use the MySQL client for DDL and DML operations on the MySQL database. You can set up the InnoDB cluster and do administration tasks through MySQL Shell.

  • Cost

    Select the VM shape based on the cores, memory, and network bandwidth that you need for your database. You can start with a one-core shape. If you need more performance, memory, or network bandwidth for the application or database node, you can change the VM shape later.