MySQL: Updating the Configuration of a MySQL DB System on OCI

Overview:

• A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to the my.ini or my.cnf files that you use in on-premises MySQL server installation
• Each configuration is linked to the shape of the MySQL DB system
• There are two types of configuration:
  • Default Configuration is defined in MySQL Database service and designed for a specific shape and contains a default set of configuration variables
  • Custom Configuration is defined
• Types of Configuration Variables:
  • System variables are not user-configurable. For the same shape, there are different configurations for standalone and HA
  • User variables are those variables that you can edit when creating or copying a configuration
  • Initialization variables apply for the life span of the MySQL instance and, once you apply it, you cannot change it later. There is currently only one initialization variable lower_case_table_name 
  • Service-specific variables are those variables that are specific only to MySQL database OCI service. Currently, there are two variables:
    • thread_pool_dedicated_listeners
    • thread_pool_max_transactions_limit
• Updating the DB system configuration to a new config that contains only dynamic user variables does not require a DB system restart

In this blog, I’ll demonstrate the steps to update the configuration of an existing MySQL DB system to use another MySQL configuration (MyConfig3) from the OCI console

Prerequisites:

• An Oracle Cloud free trial or paid account
• An existing MySQL DB System
• An existing MySQL Configuration. You can follow the blog to create MySQL configuration.

Limitations:

• You cannot update/select a configuration that changes the initialization variables of a DB System

Steps to Update a MySQL DB System Configuration

1. Sign in to the OCI Console

2. Open the navigation menu and navigate to “Databases -> DB Systems“

3. Choose your compartment. The list of MySQL DB systems is displayed. Click your DB system name

4. In the DB system details page and under the DB system configuration section, click Edit next to the Configuration option

5. In the “Edit DB system” window and under the Configuration section, click the “Change configuration” button

6. In the Browse Configurations window, select MyConfig3 from the list

Note: in my example selecting MyConfig2 is not an option because it attempts to change the initialization variables of the DB system.

7. Once you select the new config, the Edit DB system window will show the name of the new config and a compare configurations table that shows variables compared. Click the “save changes” buttons

8. DB system status will be UPDATING, but the database will NOT restart because all changed variables are dynamic.

9. Once the update finishes, the configuration name will be changed to the new config