High Availability

A high availability 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. A high availablility DB system guarantees if one instance fails, another takes over, with zero data loss and minimal downtime.

High Availability Overview

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 high availability 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.

While creating a high availability DB system, you can select the availability domain where you want to place the primary instance. This is the preferred placement of the primary instance. 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

    You cannot specify in which fault domains the secondaries are placed. For example, if you select fault domain one 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.

In the event of a failover or switchover, a secondary is promoted to a primary instance:

  • Failover: 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.
  • Switchover: You can switch manually and promote a secondary to primary.

MySQL Database high availability instances replicate over a secure, managed, internal network, unconnected to the VCN subnet you configured for your DB system endpoint connection. Although limited information regarding this internal network is available in some Performance Schema tables, you can neither connect to it nor view any other information related to it.

High availablility DB systems consume more resources (OCPUs, RAM, network bandwidth) than standalone DB systems. Hence the throughput and latency differ from the standalone DB systems.

Prerequisites

High availability uses MySQL Group Replication, which requires that each table has a defined primary key. If you try to create a table without a primary key in a high availabilty DB system, it fails.

If you are migrating data to MySQL Database and have not defined primary keys on your tables, you must add them. Check tables for primary keys and add keys to the tables that do not have them:

  1. Checking Tables for Primary Keys Using a Command-Line Client
  2. Add primary keys using any of the following methods:
    • Using invisible column: See Manually Adding Primary Keys Using a Command-Line Client.
      Note

      Using invisible columns to add primary keys is a low-impact way to update your existing data for use with a high availablility DB system. It is transparent to your applications, the new column remains hidden from SELECT queries, enabling your applications to continue working as previously.
    • Using MySQL Shell dump utility: See create_invisible_pks in MySQL Shell Dump Utility.
    • Using MySQL Shell load utility: See createInvisiblePKs in MySQL Shell Load Utility.

Checking Tables for Primary Keys Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to check your tables for primary keys and list the ones that do not have primary keys. Primary keys are a prerequisite for the group replication used by high availability.

  1. Run the following statement against the database to generate a list of tables that do not have primary keys:
    SELECT t.table_schema, t.table_name
    FROM information_schema.tables t
      LEFT JOIN (SELECT table_schema, table_name 
                 FROM information_schema.statistics
                 WHERE index_name = 'PRIMARY' 
                 GROUP BY table_schema, table_name, index_name
                 ) pks 
      ON t.table_schema = pks.table_schema AND t.table_name = pks.table_name 
    WHERE pks.table_name IS NULL
      AND t.table_type = 'BASE TABLE' 
      AND t.table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');

Manually Adding Primary Keys Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to add primary keys to invisible columns.

This task requires the following:
  • MySQL version 8.0.23 or higher. Invisible columns were introduced in 8.0.23 version.
Do the following to add a primary key to an invisible column:
  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 <my_row_id> BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY INVISIBLE FIRST;

    The command alters the table, <Table1>, with one column, test by adding a column, <my_row_id>, which is invisible and contains the primary key for the table.

Enabling and Disabling High Availability Using the Console

Use the Console to enable or disable high availability on a DB system.

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 is complete, the endpoint (READ, WRITE) always points to the newly promoted primary. You do not need to update or change the IP address used by your applications or tooling after a failover.

Table 7-1 Failover Reasons

Failover Description
Hardware Failures in any one of the following causes a new primary to be elected:
  • Storage failures
  • Network failures
  • Availability or fault domain failures
  • Host failures
MySQL Server Failures such as the following causes 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 high availablility DB system, the instance you select as primary is the preferred primary instance of the DB system. 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.

If a failover occurs, and the primary instance is no longer in the preferred availability or fault domain, a message is displayed on the DB System Details page, " Current placement (domainName) differs from preferred placement, due to failover or maintenance activity.", where domainName is the name of the fault domain or availability domain of the current primary instance.

Note

If a failover occurs on a DB system with an inbound replication channel, the channel is paused until the failover completes. Once failover is complete and a new primary promoted, the channel resumes automatically.

Switchover

Switchover manually switches from the current primary instance to one of the secondary instances.

Switchover demotes the current primary instance to secondary, and the selected secondary is promoted to primary. Also, the preferred placement changes to the current placement, that is, the current placement of the primary instance is same as the preferred placement. The endpoint is unchanged by this operation; you do not need to change the IP address of the DB system endpoint. Internally, the endpoint is redirected to the newly promoted primary.

Switchover results in a short period of downtime while the DB system endpoint is redirected to the newly promoted instance. You must reopen the database connections.

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 uncommitted transactions are rolled back.
  • The DB system endpoint is assigned to the newly promoted primary.
  • Existing connections to the previous primary are closed. The client application must reopen connections.

You can use switchover 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 Using the Console

Use the Console to promote one of the secondary instances to primary.

This task requires the following:
  • A running DB system with high availability enabled.
Do the following to switch from the current primary instance to one of the secondary instances:
  1. Open the navigation menu. Under MySQL, click DB Systems.
  2. Choose your compartment from the List Scope.
  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 menu on the same line as your DB system.
    • Click the name of the DB system to open the DB System Details page. Select Switchover from the More Actions menu.
  4. In the Switchover dialog box, select the availability or fault domain, depending on your setup, which contains the instance you want to switch to.
  5. Click Switchover.
The status of the DB system changes to Updating, and the selected instance becomes the primary.

Limitations

A high availability DB system has certain limitations.

  • A high availability DB system performs rolling upgrades, which has a brief period of downtime before the newly promoted primary resumes connections. Each MySQL instance is upgraded separately. See Maintenance Process of a High Availability DB System.
  • You cannot enable HeatWave clusters on a DB System with high availability.
  • You cannot restore a backup from a standalone DB system to a DB system with high availability.
  • High availability is supported for version 8.0.24 of MySQL, or higher. You cannot create a highly available DB system using a backup from a DB system with an earlier version.
  • You cannot access the secondary instances directly, using MySQL Shell, or any other such client.
  • You cannot edit the configuration of a DB system with high availability. However, you can create a backup and restore the backup to a DB system with configuration of your choice. See Creating a Manual Backup Using the Console and Restoring From a Backup to a New DB System Using the Console.
  • The maximum size of transaction is shape-dependent. The shapes and associated transaction size limits (bytes) are as follows:
    • 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

High availability 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.

You are charged for three MySQL instances. There is no extra charge for the internal networking used by the group replication.