Configuring a DB System

DB System Configurations

DB System configurations are collections of variables which define the operation of the MySQL DB System. They are analogous to the my.ini or my.cnf files used in local installations of MySQL Server.

The configuration is linked to the shape used. Shapes are a resource template which define the resources available to your MySQL DB System. For more information on the supported shapes and their associated MySQL Configurations, see Supported Shapes.

Configurations have a default set of variables assigned to them, user and system variables. User variables can be edited, system variables cannot.

Note

Once created, it is not possible to edit the variables defined on a configuration. To add variables, you must create a new configuration with the desired variable definitions, or copy an existing configuration, edit it accordingly, and edit the DB System to use the new configuration.

Configuration Types

MySQL Database Service behavior is defined by configurations and the variables defined on them.

MySQL Database Service supports the following configuration types:
  • Default Configurations: basic configurations. Each configuration is linked to, and tuned for, a specific shape and contains a default set of configuration variables.
  • Custom Configurations: user-created configurations. Custom configurations use a parent-child model, where the parent is a default configuration, or another custom configuration, and the child inherits the variables defined on the parent. For more information on variables, see Configuration Variables.

    When creating a new custom configuration, selecting the required shape also defines the default configuration associated with that shape as the parent to the new configuration. For example, if you create a new configuration and select the MySQL.VM.Standard.E3.1.8GB shape, the parent configuration of your new configuration is MySQL.VM.Standard.E3.1.8GB.Standalone. If you create a configuration by copying an existing configuration, the original configuration is the parent to the copy.

    Note

    The copy inherits all variables defined on the parent. It is possible to edit the values of those variables, but not to remove the variables themselves.

Configuration Variables

This topic describes the MySQL Database Service configuration variables.

Variable Groupings

MySQL Database Service Configurations group the MySQL variables. This topic describes those groupings.

Configurations group variables in the following way:

  • User Variables: MySQL variables which can be edited when you create or copy a configuration.
  • System Variables: MySQL variables which cannot be edited. These values are defined according to shape or the requirements of the MySQL instance.
  • Initialization Variables: MySQL variables which apply for the life span of the DB System’s MySQL instance. While configurations can be edited, and DB Systems can be updated with new configurations, the initialization variables cannot be changed once applied. Any attempt to apply a new configuration, with a different initialization variable value, to a running DB System, results in an error.

Default User Variables

Default user variables are associated with all configurations. The values are editable, but the variables cannot be deleted.

Table 9-2 Default User Variables

Name Default Value
group_replication_consistency BEFORE_ON_PRIMARY_FAILOVER
innodb_buffer_pool_instances Dependent on the amount of RAM provisioned by the shape. The default values are:
  • For shapes which provision 8-128GB RAM - 4
  • 256GB shapes - 8
  • 384GB shapes - 12
  • 512GB shapes - 16
  • 768GB shapes - 24
  • 1024GB shapes - 32
innodb_buffer_pool_size
Dependent on the amount of RAM provided by the shape. For standalone shapes, the default values are:
  • 8GB shape- 2147483648 bytes
  • 16GB shape - 10737418240
  • 32GB shape - 21474836480
  • 64GB shape - 51539607552
  • 128GB shape - 103079215104
  • 256GB shape - 206158430208
  • 384GB shape - 309237645312
  • 512GB shape - 412316860416
  • 768GB shape - 618475290624
  • 1024GB shape - 816043786240
For HA shapes, the default values are:
  • 8GB shape - 1610612736
  • 16GB shape - 9126805504
  • 32GB shape - 18253611008
  • 64GB shape - 46170898432
  • 128GB shape - 95563022336
  • 256GB shape - 198642237440
  • 384GB shape - 302795194368
  • 512GB shape - 405874409472
  • 768GB shape - 612032839680
  • 1024GB shape - 807453851648
innodb_ft_result_cache_limit 33554432
innodb_max_purge_lag_delay 300000
local_infile ON
mandatory_roles public
max_connections Dependent on shape. The default values per shape are:
  • 8GB shapes - 500
  • 16GB shapes - 1000
  • 32GB shapes - 2000
  • 64GB shapes - 4000
  • all shapes larger than 64GB - 8000
time_zone UTC

Initialization Variables

Initialization variables apply for the life span of the MySQL instance and, once applied, cannot be changed. The following initialization variable is available:

  • Ignore case in table and schema names: By default, table and schema names are stored as specified and comparisons are case sensitive. To disable case sensitivity, enable Ignore case in table and schema names. If you enable this option, table names are stored in lowercase on disk and comparisons are not case-sensitive. For more information on the lower_case_table_name variable which defines this behavior, see lower_case_table_names.

Creating a MySQL Configuration

This topic describes how to create a custom MySQL configuration.

To create a custom MySQL configuration:
  1. Open the navigation menu and select Databases. Under MySQL, click Configurations.
  2. Click Create MySQL Configuration.
    The Create MySQL Configuration dialog is displayed.
  3. On the Create MySQL Configuration dialog, provide the following details:
    • Select a Compartment: if you want to create the new configuration in a different compartment, select the required compartment.
    • Name your Configuration: A user-friendly display name for the configuration. The name does not need to be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the configuration.
    • Description: a user-friendly description of the configuration.
    • Shape: The shape associated with the configuration. Click Change Shape to open the Browse All Shapes dialog. Select the required shape and click Select a Shape to save your selection.
    Click Next to define the variables.
  4. On the Variables Information dialog, provide the following details:
    • Initialization Variables: Initialization variables apply for the life span of the MySQL instance and, once applied, cannot be changed.

      By default, table and schema names are stored as specified and comparisons are case sensitive. To disable case sensitivity, enable Ignore case in table and schema names. If you enable this option, table names are stored in lowercase on disk and comparisons are not case-sensitive. For more information on the lower_case_table_name variable which defines this behavior, see lower_case_table_names.

    • Variables Information: select the variable you want to add, choose the variable value, and click +Another Variable to add another variable to your configuration. See Default User Variables for information on the defaults.
      Note

      If you do not select any variables, several defaults are added automatically. These defaults are present in every configuration. For more information, see Default User Variables.
  5. Click Create to save the configuration.
    Note

    Once defined, and the configuration saved, it is not possible to edit the variables or shape defined in a configuration.

Creating a MySQL Configuration using the CLI

This task describes how to create a MySQL Configuration using the CLI oci mysql configuration create command. This task updates two MySQL variables with custom values.

To perform this task, you must have the following:
  • Compartment OCID
  • Policy which permits you to create MySQL Configurations in the compartment or tenancy.
  • Properly configured CLI installation and the requisite SSH keys. For more information, see Command Line Interface
  1. Open a command prompt and run the following to create a configuration:
    oci  mysql configuration create -c ocid1.compartment.oc1..longAlphaNumericString
         --shape-name VM.Standard.E2.1 --display-name UserConfig001 
         --description "this is a user-defined configuration"
         --variables file://config.json

    where:

    • compartmentId: (mandatory) the OCID of the compartment in which the configuration is created.

    • description: a brief, user-defined description of the configuration.

    • displayName: the display name of the DB System. If you do not define a display name, one is generated for you. Generated names take the form mysqlconfigurationYYYYMMDDHHMMSS.

    • shapeName: (mandatory) the name of the shape

    • variables: (complex type) the configuration variables to update in the configuration. These are defined as "name":value pairs.

    config.json contains the following variable definitions:

    {
        "autocommit": true,
        "connectTimeout": 20,
        "sql-require-primary-key": true
    }
     
    If the command validates and is processed correctly, a response similar to the following is displayed, summarizing the request made:
    {
      "data": {
        "compartment-id": "ocid1.compartment.oc1..longAlphanumericString",
        "defined-tags": {
          "Oracle-Tags": {
            "CreatedBy": "userName",
            "CreatedOn": "2020-07-07T11:01:19.623Z"
          }
        },
        "description": this is a user-defined configuration,
        "display-name": "UserConfig001",
        "freeform-tags": {},
        "id": "ocid1.mysqlconfiguration.oc1.iad.longAlphanumericString",
        "lifecycle-state": "ACTIVE",
        "parent-configuration-id": null,
        "shape-name": "VM.Standard.E2.1",
        "time-created": "2020-07-07T11:01:19.635000+00:00",
        "time-updated": "2020-07-07T11:01:19.635000+00:00",
        "type": "CUSTOM",
        "variables": {
          "autocommit": true,
          "binlog-expire-logs-seconds": 3600,
          "completion-type": null,
          "connect-timeout": 20,
          "cte-max-recursion-depth": null,
          "default-authentication-plugin": null,
          "foreign-key-checks": null,
          "generated-random-password-length": null,
          "information-schema-stats-expiry": null,
          "innodb-buffer-pool-instances": 4,
          "innodb-buffer-pool-size": 3758096384,
          "innodb-ft-enable-stopword": null,
          "innodb-ft-max-token-size": null,
          "innodb-ft-min-token-size": null,
          "innodb-ft-num-word-optimize": null,
          "innodb-ft-result-cache-limit": 33554432,
          "innodb-ft-server-stopword-table": null,
          "innodb-lock-wait-timeout": null,
          "innodb-max-purge-lag": null,
          "innodb-max-purge-lag-delay": 300000,
          "local-infile": true,
          "mandatory-roles": "public",
          "max-connections": 1000,
          "max-execution-time": null,
          "max-prepared-stmt-count": null,
          "mysql-firewall-mode": null,
          "mysql-zstd-default-compression-level": null,
          "mysqlx-connect-timeout": null,
          "mysqlx-deflate-default-compression-level": null,
          "mysqlx-deflate-max-client-compression-level": null,
          "mysqlx-document-id-unique-prefix": null,
          "mysqlx-enable-hello-notice": null,
          "mysqlx-idle-worker-thread-timeout": null,
          "mysqlx-interactive-timeout": null,
          "mysqlx-lz4-default-compression-level": null,
          "mysqlx-lz4-max-client-compression-level": null,
          "mysqlx-max-allowed-packet": null,
          "mysqlx-min-worker-threads": null,
          "mysqlx-read-timeout": null,
          "mysqlx-wait-timeout": null,
          "mysqlx-write-timeout": null,
          "mysqlx-zstd-max-client-compression-level": null,
          "parser-max-mem-size": null,
          "query-alloc-block-size": null,
          "query-prealloc-size": null,
          "sql-mode": null,
          "sql-require-primary-key": true,
          "sql-warnings": null,
          "transaction-isolation": null
        }
      }
    }
    
The response includes all user-configurable parameters and their current values.

It is also possible to run the command from a single command line, rather than using a JSON payload. The following command is identical to that in the JSON payload:

oci mysql configuration create -c ocid1.compartment.oc1..longAlphaNumericString
     --shape-name VM.Standard.E2.1 --display-name UserConfig001 
     --description "this is a user-defined configuration"
     --variables '{ "autocommit": true, "connectTimeout": 20, "sql-require-primary-key": true }'
       

Editing the Details of a Custom Configuration

This topic describes how to edit the details of a custom configuration. It is not possible to edit the default configurations.

Note

It is not possible to edit the configuration's variables once they are defined, nor is it possible to change the associated shape. To change the configuration variables associated with a DB System, you must create a new configuration, either by creating a new configuration or by copying an existing configuration and editing as required, and edit your DB System to use it instead of the existing configuration.
To edit a custom MySQL configuration:
  1. Open the navigation menu and select Databases. Under MySQL, click Configurations.
    The list of Configurations is displayed.
  2. Locate the custom configuration you want to edit and do one of the following:
    • Click the More options menu (three dots) and select Edit to open the Edit MySQL Configuration dialog.
    • Click the name of the configuration to open the MySQL Configuration Details page and click Edit to open the Edit MySQL Configuration dialog.
    The Edit MySQL Configuration dialog is displayed, enabling you to edit the configuration name, description, and tags.
  3. When finished editing, click Save Changes to save your changes.

Copying a Configuration

Describes how to create a new configuration based on an existing configuration.

To create a new configuration, based on an existing configuration, do the following:
  1. Open the navigation menu and select Databases. Under MySQL, click Configurations.
  2. On the three dot menu, on the same line as the configuration you want to copy, select Copy Configuration.
    The Copy Configuration dialog is displayed.
  3. On the Copy MySQL Configuration dialog, provide the following details:
    • Name your Configuration: A user-friendly display name for the configuration. The name does not need to be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the configuration.
    • Description: a user-friendly description of the configuration.
    • Select a Compartment: if you want to create the new configuration in a different compartment, select the required compartment.
    • Select a Shape: The shape associated with the configuration. It is not possible to edit the shape in a copied configuration.
    • Variables Information: select the variable you want to add, choose the variable value, and click +Another Variable to add another variable to your configuration.
  4. Click Copy to save the configuration.
    Note

    Once defined, and the configuration saved, it is not possible to edit the variables or shape defined in a configuration.

Updating the Configuration Used by a DB System

It is not possible to edit the variables of a configuration, once the configuration is saved. To apply a new set of configuration variables to a DB System, you must create a new configuration, using the variables you require, then apply this new configuration to the DB System.

To update the configuration used by the DB System, see:

Updating a DB System's Configuration using the CLI

This task describes how to replace the MySQL Configuration used by a DB System with the CLI oci mysql db-system update command.

To perform this task, you must have the following:
  • DB System OCID
  • MySQL Configuration OCID
  • Properly configured CLI installation and the requisite SSH keys. For more information, see Command Line Interface
  1. Open a command prompt and run the following to replace the DB System's configuration:
    oci mysql db-system update --db-system-id ocid1.mysqldbsystem.oc1..AlphaNumericString
         --configuration-id ocid1.mysqlconfiguration.oc1..AlphaNumericString

    where:

    • db-system-id: (mandatory) the OCID of the DB System you want to update.

    • configuration-id : (mandatory) the OCID of the configuration with which to replace the existing configuration.

    This command stops the running MySQL server and restarts it with the new configuration values.

MySQL Configuration Details

The MySQL Configuration Details page lists all relevant information and functionality for the selected MySQL Configuration,

Table 9-3 Configuration Information

Field Description
OCID The unique identifier of the MySQL configuration.
Description Description of the MySQL configuration.
Shape The shape type the configuration is associated with. Each configuration can only be used with a specific shape..
State Lifecycle state.
Source Configuration Name of the configuration this configuration is based on. If you created a new configuration, this field displays the name of the default configuration associated with the shape you selected. If you copied the configuration from an existing configuration, this field displays the name of the parent configuration.
Compartment The compartment in which the configuration was created.
Type The type of configuration, Default or Custom. Where Default is provided with the service, and Custom is user-created.
Created Date and time the configuration was created.
Last Updated The date and time the configuration was last updated.

MySQL Configuration Resources

Resources section of the MySQL Configuration Details page.

Table 9-4 MySQL Configuration Details Resources

Resource Name Description
Variables Lists the following:
  • Name: the name of the variable.
  • Value: the value of the variable.
  • User defined: whether the variable's value is user defined, or not.
For more information on MySQL variables, see the following resources:
Associated MySQL DB System Lists the following:
  • Name: the name of the DB System using this configuration.
  • DB System State: the current state of the DB System.
  • Created: the date and time the DB System was created.