Creating a DB System

This topic describes how to create and configure a MySQL DB System. The following topics are described:

Creating a MySQL DB System

This topic describes how to launch a new DB System.

  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. Click Create MySQL DB System.
    The Create MySQL DB System dialog is displayed. Complete the fields in each section.
  3. Provide basic information for the DB System:
    • Select a Compartment: If you want to launch the MySQL DB System in a compartment other than the current compartment, select the required Compartment from the list. If you do not select a different compartment, the current compartment is used.
    • Name: A user-friendly display name for the DB system. The name does not need to be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the DB system.
    • Description: A user-friendly description of the DB System and its purpose.
  4. Setup your required DB System:
    • Standalone: specifies a single-instance DB System.
    • High Availability: specifies a three-instance DB System containing one primary instance, and two secondary instances. If a regional subnet is selected, the instances are spread across the availability domains. If the subnet is not regional, or your region comprises a single availability domain, the instances are spread across the fault domains. See High Availability for more information.
    • HeatWave: configures a Standalone DB System with a HeatWave-compatible shape (MySQL.HeatWave.VM.Standard.E3) and 1TB of data storage, by default. To attach a HeatWave Cluster to this DB System, you must use either MySQL.HeatWave.VM.Standard.E3 or MySQL.HeatWave.BM.Standard.E3 shapes. MySQL.HeatWave.BM.Standard.E3 is recommended for data sets of 10TB or more and is supported on the DB System node, only. MySQL.HeatWave.VM.Standard.E3 can be selected for either DB System node or cluster nodes. For more information, see HeatWave.
  5. Create Administrator credentials: specify the DB System administrator's credentials.:
    • Username: the user name of the Administrator user. See Reserved Usernames for more information. The Administrator user is granted a specific set of privileges which differ from the root user. For more information on these privileges, see Default MySQL Privileges.
    • Password: the Administrator user's password.
    • Confirm Password: re-enter the Administrator user's password.
  6. Configure Networking:
    • Virtual Cloud Network: the VCN in which to launch the DB System.
    • Subnet: select the required subnet of the selected VCN.
  7. Configure placement:
    • Select an Availability Domain: for Standalone and HeatWave DB Systems, the availability domain in which the DB system resides. For High Availability DB Systems, and how this choice affects instance placement, see High Availability.
    • Choose a Fault Domain: a fault domain is a grouping of hardware and infrastructure within an availability domain. Each availability domain contains three fault domains. Fault domains enable you distribute your DB Systems so that they are not on the same physical hardware within a single availability domain. Select this option to display the Fault Domain selection.

      If you do not select a Fault Domain, Oracle chooses the best placement for you.

  8. Configure hardware:
    • Select a Shape: Opens the Browse All Shapes dialog. Select the shape to use for your DB system. The shape determines the resources allocated to the system. For more information on the available shapes, see Supported Shapes. You can filter the shapes displayed by selecting one of the labels from All Types, to display all available shapes, Virtual Machine, to display only the Virtual Machine-based shapes, and Bare Metal, to display only the Bare Metal-based shapes. Click Select a Shape after you have selected the shape required. Selecting a shape also selects the default configuration associated with that shape. For more information on configurations, see Configuring a DB System. You can change the default configuration in the Advanced Options section.
      Note

      If you selected High Availability (HA) for your DB System, you can only select the E3 shapes. The selected E3 shape is associated with a HA-specific configuration.
    • Data Storage Size: The amount of block storage, in GB, to allocate to the DB System. This block storage stores all data, logs, and temp files. Binaries are not stored in this block storage. Do not specify a value less than 50, or greater than 65536.

      The initial storage value has an impact on your ability to update storage later. For more information, see Increasing storage using the console.

      Note

      If you are importing data, ensure you specify sufficient data storage for the data you are importing.
  9. Configure Backups: Specify the backup details:
    • Enable Automatic Backups: Select to enable scheduled backups. If you do not specify scheduled backups, you must manage your backup strategy manually. It is strongly recommended to enable scheduled backups.
    • Retention Period: Defines how long to retain the backups, in days. Default is seven days.
      Note

      Once defined, it is not possible to edit the retention period of an automatic backup.
    • Backup Window: A backup window is defined as a time during which the backup is initiated. Times are specified using the UTC timezone.
    • Click Show More to see the list of per-Region time windows.
    • To specify a custom backup window, select Select Backup Window. The Backup Window Start Time field is displayed. Specify your backup window start time. Your backup begins processing in the 30 minutes following the defined start time.
  10. Show Advanced Options: opens a group of tabs which enable you to further refine your DB System . For more information on these options, see Advanced Options.
  11. Click Create to create the DB System.
    The MySQL DB System is created.

Advanced Options

Advanced options on the Create DB System and Restore to a new DB System dialogs.

The following topics are described:

Configuration

Advanced Options: Configuration tab

A default configuration is applied to your DB System, based on the choice of shape. To see the default configuration, or change the selection to a custom configuration, click Select Configuration. A list of the default and custom configurations associated with the chosen shape is displayed.

Note

While it is possible to select a Standalone configuration for use with a Highly Available (HA) DB System, it is strongly recommended you accept the default HA-specific configuration associated with the selected shape. HA-specific configurations are optimized for use with HA DB Systems. If you intend to use a custom configuration for your HA DB System, ensure it is based on an existing HA-specific configuration.

Management

Advanced Options: Management tab

Enables you to define the time and day essential maintenance is carried out on your DB System.

  • Maintenance Window: Maintenance Window Start Time (Optional): specify a convenient time for required maintenance, such as operating system or MySQL instance upgrades. If you do not specify a day and time, one is chosen for you. Times are specified using the UTC timezone. For more information, see Maintenance.

Networking

Advanced Options: Networking tab

Enables you to configure the following options on your DB System:

  • Hostname: (available only if USE DNS HOSTNAMES IN THIS VCN is enabled on the selected VCN) Your choice of host name for the DB system. The host name must begin with an alphabetic character, can contain only alphanumeric characters and hyphens (-), and must not use more than 30 characters. The hostname must be unique within the subnet. If it is not unique, the DB System will fail to provision. This specifies a host name prefix for the DB System and associates a DNS name with the DB System endpoint using the following syntax:
    hostname.privatesubnet.yourvcnname.oraclevcn.com
    For example::
    myhost.sub01031036431.myvcn.oraclevcn.com
    For more information on DNS domains and hostnames, see DNS Domains and Hostnames.
  • IP Address: define a private IP address to assign to the DB System's endpoint. The address must be available in the subnet's CIDR. If you do not specify an address, Oracle automatically assigns a private IP address from the subnet.
  • MySQL Port: the port on which the server listens.
  • MySQL X Protocol Port: supported by clients such as MySQL Shell.

Data Import

Advanced options: Data Import tab.

Enables you to import data from a MySQL Shell dump in an Object Storage bucket. Define the Pre-Authenticated Request (PAR) URL of the bucket, bucket prefix, or manifest file. If you have not created a PAR URL, click the link to open the Click here to create a PAR URL for an existing bucket. wizard and complete the fields as required. The create PAR URL wizard only supports bucket and bucket prefix PAR URL generation. To create an object PAR URL for a manifest file, you must do so manually using the MySQL Shell dump utility's ociParManifest option, or the Object Storage service functionality. See Import Using Pre-Authenticated Requests.
Note

Ensure you have defined enough Data Storage to accommodate the imported data.
  • Select a Bucket in CompartmentName: select the Object Storage bucket which contains your dump.
  • Configure Prefix:
    • Select the prefix: select one of the available prefixes from the selected bucket.
    • Enter a prefix: (Optional) enables you to define a bucket prefix, similarly to a folder name. The prefix must exist in the selected bucket. Prefix names take the format prefixName/. Omitting the forward slash delimiter in the PAR results in an invalid URL. You can specify paths with multiple prefixes, prefixName/prefixName1/prefixName2/
    Note

    MySQL Database supports the folder-type of prefix, only. The filename-matching prefix type is not supported.
  • Specify an expiration time for the PAR: select an expiration time for the PAR. Default is 1 week.

Click Create and set PAR URL to generate the PAR URL and add it to the PAR Source URL field on the Data Import tab. If you defined a bucket prefix, you must add it to the PAR URL manually. It is not added by default. Ensure you include the prefix name and trailing forward slash. prefixName is not valid, prefixName/ is valid.

For more information, see Import Using Pre-Authenticated Requests and Working with PARs.

Tags

Advanced options: Tags tab

Optionally, you can apply tags. For more information on tagging, see Resource Tags.

Creating a DB System and Importing Data using the CLI

This task describes how to create a DB System using the CLI mysql db-system create command. It creates a DB System with Backups enabled and imports data from Object Storage using a pre-authenticated request (PAR) URL.

To perform this task, you must have the following:
  • Compartment OCID
  • Policy which permits you to create DB Systems in the compartment or tenancy.
  • Properly configured CLI installation and the requisite SSH keys. For more information, see Command Line Interface
  • Properly configured VCN. See Configuring the Network for more information.
  • A PAR URL for the manifest file (@.manifest.json) of a MySQL Shell-generated dump to an Object Storage bucket. For more information, see Using Pre-Authenticated Requests.
This task shows the command line syntax and parameters used to create a DB System using a JSON payload.
  1. Open a command prompt and run the following to create a DB System. For example:.
    oci mysql db-system create --from-json file://dbsystem-launch.json

    where dbsystem-launch.json contains the following:

    {
      "adminUsername": "User001",
      "adminPassword": "User001@#",
      "compartmentId": "ocid1.compartment.oc1..AlphaNumericString", 
      "configurationId": "ocid1.mysqlconfiguration.oc1..AlphaNumericString", 
      "shapeName": "MySQL.VM.Standard.E3.1.8GB", 
      "dataStorageSizeInGbs": 63, 
      "description": "This is my DBSystem", 
      "displayName": "MyDBSystem", 
      "hostnameLabel": "MyHostname",
      "availabilityDomain": "nnnn:region-AD-3",
      "subnetId": "ocid1.subnet.oc1.iad.AlphaNumericString",
      "backupPolicy": {
        "isEnabled": true,
        "retentionInDays": 7,
        "windowStartTime": "00:10"
      },
      "source": {
          "sourceType": "IMPORTURL",
          "sourceUrl": "https://objectstorage.region.oraclecloud.com/../@.manifest.json"
        }
    }

    where:

    • adminUsername: (mandatory) the user name of the root user of the DB System. Some usernames are reserved and cannot be used in the definition of a DB System. For more information, see Reserved Usernames.

    • adminPassword: (mandatory) the password of the root user of the DB System.

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

    • availabilityDomain: (mandatory) the name of the availability domain in which the DB System is created.

    • configurationID: the OCID of the configuration to apply to the DB System. If you do not define a configuration ID, the default configuration associated with the selected shape is used. If you define a custom configuration, it must use the same shape as defined in the request.

    • shapeName: (mandatory) the name of the shape.

    • subnetId: (mandatory) the OCID of the VCN subnet the DB System is attached to.

    • dataStorageSizeInGbs: (mandatory) the amount of data storage, in GBs, for the DB System.

      Note

      Do not specify a value less than 50, or greater than 65536, and ensure you define enough storage to accommodate imported data.
    • hostnameLabel: (use only if USE DNS HOSTNAMES IN THIS VCN is enabled on the selected VCN) Your choice of host name for the DB system. The host name must begin with an alphabetic character, can contain only alphanumeric characters and hyphens (-), and must not use more than 30 characters. The hostname must be unique within the subnet. If it is not unique, the DB System will fail to provision. This specifies a host name prefix for the DB System and associates a DNS name with the DB System endpoint using the following syntax:
      hostname.privatesubnet.yourvcnname.oraclevcn.com
      For example, if you specify a value of myhost for hostnameLabel, on a subnet named myprivatesubnet, on a VCN named myvcn your DB System's FQDN is set to:
      myhost.myprivatesubnet.myvcn.oraclevcn.com
      For more information on DNS domains and hostnames, see DNS Domains and Hostnames.
    • description: a brief, user-defined description of the DB System.

    • 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 mysqldbsystemYYYYMMDDHHMMSS.

    • backupPolicy: the container for the backup policy parameters. The following parameters define the schedule for automatic backups of your DB System:
      • isEnabled: set to true to enable backups.
      • retentionInDays: number of days to retain the automatic backups. The default is 7.
      • windowStartTime: specifies the start of a 30-minute window in which daily, automated backups are performed. Uses the following format: HH:MM in UTC. You can also define offsets, for example: 00:00-08:00, which sets the time to 8 hours behind midnight UTC.
    • source: the container for the import parameters. The following parameters define the data import type and source:
      • sourceType: IMPORTURL defines the import type as a PAR URL.
      • sourceUrl: the PAR URL for the manifest file (@.manifest.json) of a MySQL Shell-generated dump to an Object Storage bucket.
    If the command validates and is processed correctly, a response similar to the following is displayed, summarizing the request made:
    {
      "data": {
        "analytics-cluster": null,
        "availability-domain": "nnnn:region-AD-3",
        "backup-policy": {
          "defined-tags": null,
          "freeform-tags": null,
          "is-enabled": true,
          "retention-in-days": 7,
          "window-start-time": "00:10"
        },
        "channels": [],
        "compartment-id": "ocid1.compartment.oc1..alphaNumericString",
        "configuration-id": "ocid1.mysqlconfiguration.oc1..alphaNumericString",
        "current-placement": {
          "availability-domain": null,
          "fault-domain": null
        },
        "data-storage-size-in-gbs": 63,
        "defined-tags": {
          "Oracle-Tags": {
            "CreatedBy": "userEmail",
            "CreatedOn": "2021-06-04T10:02:32.140Z"
          }
        },
        "description": "This is my DBSystem",
        "display-name": "MyDBSystem",
        "endpoints": [],
        "fault-domain": null,
        "freeform-tags": {},
        "heat-wave-cluster": null,
        "hostname-label": null,
        "id": "ocid1.mysqldbsystem.oc1.iad.alphaNumericString",
        "ip-address": null,
        "is-analytics-cluster-attached": false,
        "is-heat-wave-cluster-attached": false,
        "is-highly-available": false,
        "lifecycle-details": null,
        "lifecycle-state": "CREATING",
        "maintenance": {
          "window-start-time": "TUESDAY 06:46"
        },
        "mysql-version": null,
        "port": null,
        "port-x": null,
        "shape-name": "MySQL.VM.Standard.E3.1.8GB",
        "source": {
          "source-type": "IMPORTURL"
        },
        "subnet-id": "ocid1.subnet.oc1.iad.alphaNumericString",
        "time-created": "2021-06-04T10:02:37.186000+00:00",
        "time-updated": "2021-06-04T10:02:37.186000+00:00"
      },
      "etag": "alphaNumericString",
      "opc-work-request-id": "ocid1.mysqlworkrequest.oc1.iad.alphaNumericString"
    }
The MySQL DB System, MyDBSystem, is created.

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 db-system create -c ocid1.compartment.oc1..AlphaNumericString 
 --configuration-id ocid1.mysqlconfiguration.oc1..AlphaNumericString   
 --shape-name MySQL.VM.Standard.E3.1.8GB --availability-domain nnnn:region-AD-3 
 --subnet-id ocid1.subnet.oc1.AlphaNumericString 
 --admin-username User001 --admin-password User001# --data-storage-size-in-gbs 63 
 --display-name MyDBSystem --description "This is my DBSystem" --hostname-label MyHostname 
 --backup-policy '{ "isEnabled": true, "retentionInDays": 7, "windowStartTime": "00:10" }'
 --source '{"sourceType": "IMPORTURL", "sourceURL":"https://objectstorage.region.oraclecloud.com/p/../@.manifest.json"}'
       
Note

The formatting of your command line input depends on your platform. The examples in this topic are for Linux. For more information, see Managing CLI Input.