12.2 Prerequisites

Here are some prerequisites for creating a high availability DB System.

• A high availability DB System requires a high availability compatible configuration. All default MySQL configurations that are compatible with high availability are indicated as so in the MySQL Configuration Details, If you want to create a custom configuration that supports high availability, remember to select Support High Availability when Creating a MySQL Configuration or Copying a MySQL Configuration.

• As the DB system high availability feature uses MySQL Group Replication, for which each table must have a primary key. If you try to create a table without a primary key in a high availability DB system, it will fail.

  If you are migrating tables to HeatWave on AWS that do not have primary keys, you must add them. Check tables for primary keys and add keys to the tables that do not have them:

  1. Use a command-line client such as the mysql client or MySQL Shell to check your tables for primary keys and list the ones that do not have primary keys:

     Press CTRL+C to copy
      
     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');

  2. If primary keys are missing, add them using any of the following methods:
     • Run a command similar to the following against the table to which you want to add the invisible column and primary key:

       Press CTRL+C to copy
        
       ALTER TABLE <Table1> ADD <my_row_id> BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY INVISIBLE FIRST;

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

       Note:

       Using invisible columns to add primary keys is a low-impact way to update your existing data for use with a high availability 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.

       Note:

       To use MySQL Shell dump and load utility, use MySQL Shell version 8.0.30 or higher.
     • Using sql_generate_invisible_primary_key variable: Set the variable to ON to add primary keys to new tables that you create. Setting the variable to ON does not add primary keys to already existing tables. See Generating Invisible Primary Keys.
• Automatic Backups must be enabled, and are enabled for you when you create a high availability DB System. You can choose a retention period for the automatic backups.

Parent topic: High Availability