Prerequisites
A high availability DB system requires a high availability compatible configuration. As the DB system high availability feature uses MySQL Group Replication, 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 fails.
All default configurations that are compatible with high availability has the HA suffix in the configuration name. If you want to create a custom configuration that supports high availability, you need to use the Copy Configuration method to copy from an existing configuration that supports high availability. See Copying a Configuration.
If you are migrating data to HeatWave 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:
- Checking Tables for Primary Keys Using a Command-Line Client
- 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 fromSELECT
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 toON
to add primary keys to new tables that you create. Setting the variable toON
does not add primary keys to already existing tables. See Generating Invisible Primary Keys.
- Using invisible column: See Manually Adding Primary Keys Using a Command-Line Client.
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.
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.
- MySQL version 8.0.23 or higher. Invisible columns were introduced in 8.0.23 version.
Related Topics