MySQL: Creating a MySQL Configuration 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

In this blog, I’ll demonstrate the steps to create MySQL configuration using OCI console for MySQL.VM.Standard.E3.1.8GB shape, then create a new MySQL standalone DB system using the new configuration.

Prerequisites:

• An Oracle Cloud free trial or paid account
• OCI Compartment

Steps to Create a MySQL Configuration

1. Sign in to the OCI console

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

3. The list of configurations is displayed. Choose your compartment

4. Click the “Create Configuration” button

5. In the “Create Configuration” window, enter and select the below options, then click the “Create” button

    – Enter name and description

    – Select compartment

    – Click the “Change shape” button and select MySQL.VM.Standard.E3.1.8GB shape from the list

    – Under the “User variables” section, add the below variables:

       – max_connections = 200

       – sort_buffer_size = 500000

       – time_zone = -5:00

6. Once create configuration finishes, the configuration details page is displayed

7. We can now use the new configuration when creating the MySQL DB system. In the “Create DB System” wizard: 

    – Under the “Configure Hardware” section, make sure to select “MySQL.VM.Standard.E3.1.8GB” shape

    – Click “Show advanced options“, select the “Configuration” tab, click the “Select Configuration” button, then select “MyConfig” configuration from the available list