Configuring a DB System

This topic describes how to configure MySQL DB Systems and how to use the Configurations console. The following topics are described:

MySQL Configurations for DB Systems

MySQL Configurations for DB System are collections of MySQL 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 define the resources available to your MySQL DB System. For more information on the supported shapes and their associated MySQL Configurations, see Supported Shapes.

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 and edit it accordingly, and edit the DB System to use the new configuration.

Supported Shapes

This topic describes the shapes supported by MySQL Database Service.

Shapes are associated with MySQL Configurations. To apply a specific configuration to your DB System, you must choose the associated shape.

The following shapes are supported by MySQL DB Systems:

Table 7-1 Supported Shapes

Supported Shapes OCPUs Memory (RAM) Associated Configurations
VM.Standard.E2.1 1 8 VM.Standard.E2.1 Built-in
VM.Standard.E2.2 2 16 VM.Standard.E2.2 Built-in
VM.Standard.E2.4 4 32 VM.Standard.E2.4 Built-in
VM.Standard.E2.8 8 64 VM.Standard.E2.8 Built-in
MySQL.VM.Standard.E3.1.8 1 8 MySQL.VM.Standard.E3.1.8GB Built-in
MySQL.VM.Standard.E3.1.16 1 16 MySQL.VM.Standard.E3.1.16GB Built-in
MySQL.VM.Standard.E3.2.32 2 32 MySQL.VM.Standard.E3.2.32GB Built-in
MySQL.VM.Standard.E3.4.64 4 64 MySQL.VM.Standard.E3.4.64GB Built-in
MySQL.VM.Standard.E3.8.128 8 128 MySQL.VM.Standard.E3.8.128GB Built-in
MySQL.VM.Standard.E3.16.256 16 256 MySQL.VM.Standard.E3.16.256GB Built-in
MySQL.VM.Standard.E3.24.384 24 384 MySQL.VM.Standard.E3.24.384GB Built-in
MySQL.VM.Standard.E3.32.512 32 512 MySQL.VM.Standard.E3.32.512GB Built-in
MySQL.VM.Standard.E3.48.768 48 768 MySQL.VM.Standard.E3.48.768GB Built-in
MySQL.VM.Standard.E3.64.1024 64 1024 MySQL.VM.Standard.E3.64.1024GB Built-in
MySQL.Analytics.VM.Standard.E3     MySQL.Analytics.VM.Standard.E3.Built-in
BM.Standard.E2.64 64 512 BM.Standard.E2.64.Built-in

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. 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.
    • 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 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 }'
       

To edit a MySQL Configuration

This topic describes how to edit a custom MySQL Configuration. It is not possible to edit the default MySQL configurations.

To edit a custom MySQL configuration:
  1. Navigate to the required compartment.
  2. Select Configurations in the navigation menu.
    The list of Configurations is displayed.
  3. 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.
    Note

    It is not possible to edit the configuration's variables once they are defined, nor is it possible to change the associated shape.
  4. 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. 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 7-2 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 was copied from.
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 7-3 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.