Business Continuity

This section describes MySQL Database Service business continuity.

Recovery Time Objective (RTO) and Recovery Point Objective (RPO)

MySQL Database Service has daily automatic backups and is resilient to failures because it leverages Block Volumes to store user data. Consequently, it offers the same durability, security, and performance guarantees. RTO and RPO information is provided for Single Instance and Highly Available DB Systems.

Single Instance MySQL DB System

The following table describes the Recovery Time Objective (downtime tolerance) and Recovery Point Objective (data loss tolerance) for a single-instance DB System.

Table 3-1 RTO (downtime tolerance) and RPO (data loss tolerance) for a single instance DB System

Failure and Maintenance Events Downtime (RTO) Potential Data Loss (RPO)
  • Periodic software and hardware maintenance updates
Minutes to hours Zero
Localized events, including:
  • Network connectivity failures
  • Storage connectivity failures
Minutes to hours Zero
Events requiring restoring from backup, including:
  • Complete storage failures
  • Full database failures
  • Availability or fault domain failures
Minutes to hours Up to the last successful automatic backup

Highly Available MySQL DB System

The following table describes the Recovery Time Objective (downtime tolerance) and Recovery Point Objective (data loss tolerance) that a Highly Available DB System was designed to provide in the event of a single-instance failure.

Table 3-2 RTO (downtime tolerance) and RPO (data loss tolerance) for a Highly Available DB System

Failure and Maintenance Events Downtime (RTO) Potential Data Loss (RPO)
  • Periodic software and hardware maintenance updates
Minutes to hours Zero
Localized, per instance events, including:
  • Storage connectivity failures
  • Network connectivity failures
  • Full database failures
Minutes Zero
  • Availability or fault domain failures (depending on High Availability type)
  • Complete storage failures
Minutes Zero

Backups

Automatic and manual backups are replicated to another availability domain and can be restored in the event of a disaster or user error. Data loss is limited by the last successful backup.

For more information, see Backing Up a DB System.

High Availability

A highly available database system is one which guarantees if one instance fails, another takes over, with zero data loss and minimal downtime.

MySQL Database High Availability uses MySQL Group Replication to provide standby replicas to protect your data and provide business continuity. MySQL Group Replication implements the Paxos algorithm. A highly available DB System is made up of three MySQL instances, a primary, and two secondaries. All data written to the primary instance is also written to the secondaries. In the event of failure of the primary, one of the secondaries is automatically promoted to primary, is set to read-write mode, and resumes availability to client applications with no data loss. This is called a failover. It is also possible to switch manually, and promote a secondary to primary. This is called a switchover.

When a failover or switchover is initiated, the endpoint (READ, WRITE) always points to the newly promoted primary. That is, the IP address of the DB System's endpoint never changes, regardless of which instance is the currently promoted primary.

When creating a DB System with High Availability, three instances are created and placed according to your region- and subnet-type. You can choose the preferred location of your primary instance, only. The secondaries are placed automatically in the other two availability or fault domains. The following instance placement models are used:

  • Multiple availability domains with a regional subnet: the primary and secondaries are placed in different availability domains.
    Note

    It is not possible to specify in which fault domains the secondaries are placed. If you select fault domain 1 during the creation of the DB System, only the primary is placed in that fault domain. The secondaries are placed automatically.
  • Multiple availability domains with an availability domain-specific subnet: the primary and secondaries are placed in different fault domains in the same availability domain.
  • Single availability domain region: the primary and secondaries are placed in different fault domains in the same availability domain.

MySQL Database High Availability instances replicate over a secure, managed, internal network, unconnected to the VCN subnet you configured for your DB System's endpoint connection. Although limited information regarding this internal network is available in some Performance Schema tables, it is not possible for you to connect to it, nor is it possible to view any other information related to it.

Highly available DB Systems consume more resources (OCPUs, RAM, network bandwidth) than standalone DB Systems. As such, throughput and latency differ from the standalone DB System type.

Prerequisites

  • High Availability uses MySQL Group Replication. This replication type requires that each table has a defined primary key. If you are migrating data to MySQL Database and have not defined primary keys on your tables, you must add them. Similarly, if you try to create a table without a primary key in a High Availabilty-enabled DB System, it will fail. You can add a primary key using Invisible Columns, which enable you to add an invisible column to your tables and add the primary key in that column. For more information, see Primary Keys.
    Note

    Using invisible columns to add primary keys is a low-impact way to update your existing data for use with a highly available DB System. It is transparent to your applications, the new column remains hidden from SELECT queries, enabling your applications to continue working as previously.

Primary Keys

This section describes how to check for and add primary keys to tables which do not have them.

Checking Tables for Primary Keys

Primary keys are a prerequisite for the group replication used by High Availability. This task describes how to check your tables for primary keys and list the ones which do not have primary keys.

To check your existing tables for primary keys, do the following:
  1. From your MySQL client of choice, run the following statement against your database to generate a list of tables with no defined primary key:
    SELECT tables.table_schema , tables.table_name , tables.engine  
    FROM information_schema.tables  LEFT JOIN (     
       SELECT table_schema , table_name
       FROM information_schema.statistics
       GROUP BY table_schema, table_name, index_name 
       HAVING SUM( 
         case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks   
       ON tables.table_schema = puks.table_schema 
      AND tables.table_name = puks.table_name   
      WHERE puks.table_name IS null 
      AND tables.table_type = 'BASE TABLE' 
      AND tables.table_schema NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');
  2. Add primary keys to the tables. For information on adding primary keys in invisible columns, see Adding Primary Keys to Invisible Columns.
Adding Primary Keys to Invisible Columns

Describes how to add primary keys to invisible columns.

The following is assumed:
  • You are running MySQL 8.0.23 minimum. Invisible columns were introduced in that version.
To add a primary key to an invisible column, do the following:
  1. Run a command similar to the following against the table to which you want to add the invisible column and primary key:
    ALTER TABLE table1 ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY INVISIBLE FIRST;

    This alters a table, table1, with one column, test by adding a column, id, which is invisible and contains the primary key for the table.

For more information on the SQL statement see the following:

Current Limitations

The following are the current limitations of MySQL Database Service High Availability:
  • Downtime during MySQL instance upgrades. Each MySQL instance is upgraded separately.
  • It is not possible to enable HeatWave clusters on a DB System with High Availability.
  • It is not possible to enable Inbound Replication on a DB System with High Availability.
  • It is not possible to restore a backup from a standalone DB System to a DB System with High Availability.
  • It is not possible to access the secondary instances directly, using MySQL Shell, or any other such client.
  • It is not possible to enable High Availability on a running DB System.
  • It is not possible to edit the configuration of a DB System with High Availability.
  • The maximum size of transaction is shape-dependent. The shapes and associated transaction size limits (bytes) are:
    • MySQL.VM.Standard.E3.1.8GB: 85899346
    • MySQL.VM.Standard.E3.1.16GB: 171798692
    • MySQL.VM.Standard.E3.2.32GB: 343597384
    • MySQL.VM.Standard.E3.4.64GB: 687194767
    • MySQL.VM.Standard.E3.8.128GB: 1073741824
    • MySQL.VM.Standard.E3.16.256GB: 1073741824
    • MySQL.VM.Standard.E3.24.384GB: 1073741824
    • MySQL.VM.Standard.E3.32.512GB: 1073741824
    • MySQL.VM.Standard.E3.48.768GB: 1073741824
    • MySQL.VM.Standard.E3.64.1024GB: 1073741824

Billing for High Availability

Highly available DB Systems contain three MySQL instances, each of which utilizes the same amount of block volume storage, number of OCPUs, and amount of RAM defined in the shape chosen. There is no extra charge for the internal networking used by the group replication.

Failover

In the event of a failure of your primary instance, a secondary instance, residing in a separate availability or fault domain, is automatically promoted to primary.

When a failover or switchover is complete, the endpoint (READ, WRITE) always points to the newly promoted primary. That is, the IP address used by your applications or tooling does not need to be updated or changed after a failover

This following table describes some typical automatic failover reasons:

Table 3-3 Failover Reasons

Failover Description
Hardware Failures in any one of the following will cause a new primary to be elected:
  • Storage failures
  • Network failures
  • Availability or fault domain failures
  • Host failures
MySQL Server Failures such as the following will cause a new primary to be elected:
  • MySQL process stops
  • Operating System stops
  • MySQL instance or process is slow or overloaded
  • Replication errors
When you first create your highly available DB System, the instance you select as primary is the preferred primary instance of the DB System. That is, if an error occurs, and the primary leaves the group, one of the other secondaries is promoted to primary, the instance you first selected as primary will always be the preferred primary. Once the error has been corrected, and the original primary returns to the group as one of the secondaries, if another failover occurs, that instance you first defined as primary will be the preferred instance for promotion.
Note

If you perform a switchover, the selected instance becomes the preferred primary instance.

Switchover

Switchover enables you to manually switch from the current primary instance to one of the secondary replicas. The current primary instance is demoted to secondary, and the selected secondary is promoted to primary. The endpoint is unchanged by this operation; you do not need to change your client's connection configuration. Internally, the endpoint is redirected to the newly promoted primary.

Switchover results in a short period of downtime while the DB System's endpoint is redirected to the newly promoted instance. Database connections must also be reopened. The following is the switchover process:
  • Running transactions are allowed to complete. The switchover process waits until all running transactions finish and commit.
  • New transactions are accepted and can perform reads and writes but, when the secondary is promoted to be the new primary, and the connection to the previous primary is broken, all such transactions are rolled back.
  • The DB System's endpoint is assigned to the newly promoted primary.
  • Existing connections to the previous primary are closed. The client application must reopen connections.
Switchover can be used in the following scenarios:
  • Application testing: ensuring your application works correctly with the newly promoted primary.
  • Proximity of availability domain: while fault domains are contained in the same data center, availability domains are spread across different data centers. Even though these availability domains are connected with a low-latency network, it may be necessary to test that latency by moving primary from availability domain to availability domain to confirm the best location for your primary. For example, switching to an instance in the same availability domain as the application which connects to it.

Switching Primary Instance

This task describes how to promote one of the secondary instances to primary.

This task assumes the following:
  • A running DB System with High Availability enabled.
To switch from the current primary instance to one of the secondary instances, do the following:
  1. Open the navigation menu. Under MySQL, click DB Systems.
  2. Choose your Compartment.
    A list of DB Systems is displayed.
  3. In the list of DB Systems, find the DB System you want to switch and do one of the following:
    • Choose Switchover from the Actions icon (three dots) on the same line as your DB System.
    • Click the name of the DB System to open the MySQL DB System Details page. Select Switchover from the More Actions menu.
    The Switchover dialog is displayed.
  4. Select the availability or fault domain, depending on your setup, which contains the instance you want to switch to.
  5. Click Switchover to begin the switch process.
The DB System's status changes to Updating, and the selected instance becomes the primary.