Creating a DB System

Use the Console or a command-line interface to create a MySQL HeatWave DB system.

Using the Console

Use the Console to create a DB system.

  1. Open the navigation menu, and select Databases. Under MySQL HeatWave, click DB systems.
  2. Click Create DB system.
  3. Select one of the following:
    • Production: Set up a high availability DB system with the following default values suitable for a production environment:
      • High availability: Enabled
      • Shape: MySQL.8
      • Storage size: 1024GB
      • Automatic backups: Enabled
      • Point-in-time recovery: Enabled
      • Delete protected: Enabled
      • Automatic backups retention: Enabled
      • Final backup: Require final backup
    • Development or testing: Set up a standalone DB system the following default values suitable for a development or testing environment:
      • High availability: Disabled
      • Shape: MySQL.2
      • Storage size: 50GB
      • Automatic backups: Enabled
      • Point-in-time recovery: Enabled
      • Delete protected: Disabled
      • Automatic backups retention: Disabled
      • Final backup: Skip final backup
  4. Provide DB system information:
    • Create in compartment: If you want to launch the DB system in a compartment other than the current one, select the compartment from the list.
    • Name: Specify a display name for the DB system. The name need not be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the DB system.
    • Description: (Optional) Specify a description of the DB System and its purpose.
  5. Create Administrator credentials:
    • Username: Specify the user name of the administrator. See Reserved Usernames. The Administrator has a specific set of privileges that differs from the root user. See Default MySQL Privileges.
    • Password: Specify the administrator password.
    • Confirm password: Confirm the administrator password.
    Note the username and password of the DB system administrator. You can later change the password using SQL commands such as ALTER USER or SET PASSWORD.
  6. Select the type of DB system:
    • Standalone: Select for a single-instance DB system.
    • High availability: Select for a three-instance DB system that contains one primary instance and two secondary instances. If you select a regional subnet, the instances are spread across the availability domains. If you do not select a regional subnet, or your region comprises a single availability domain, the instances are spread across the fault domains. See High Availability.
  7. Configure networking:
    • Virtual cloud network: Select the VCN in which you want to create the DB system.
    • Subnet: Select the private subnet of your VCN.
  8. Configure placement:
    • Availability domain: For Standalone DB systems, specify the availability domain in which the DB system resides. For high availability DB systems, selecting an availability domain affects instance placement. See High Availability.
    • Choose a fault domain: Choose this option to enable you to select a fault domain. If you do not choose a fault domain, Oracle chooses one for you. See Fault Domains.
  9. Configure hardware:
    • Enable HeatWave: Enabled by default. Deselect to create a DB system without HeatWave cluster.
      Note

      You can still add a HeatWave cluster to the DB system later if the DB system has a HeatWave-supported shape.
    • Shape details: If you want to change the default shape, click Change shape. In the Browse all shapes panel, do the following:
      1. Select the compute model.
        • ECPU: Based on an architecture-agnostic of ECPU with an abstracted measure of CPU cores, memory, and other resources.
        • OCPU: Based on physical core of a processor with hyper-threading enabled. Filter the shapes by selecting the machine type:
          • All types: Displays all available shapes.
          • Virtual machine: Displays the Virtual Machine-based shapes only.
          • Bare metal: Displays the Bare Metal-based shapes only.
      2. Select the checkbox to choose a shape. See Supported Shapes.
      3. Click Select a shape.
    • HeatWave configuration: This option is shown when Enable HeatWave is selected. If you want to change the default configuration, click Configure HeatWave cluster and do the following in the Configure HeatWave cluster panel:
      1. Shape details: The shape for the HeatWave nodes. If you want to change the shape, click Change shape to select a shape for the HeatWave nodes. See Supported Shapes.
      2. Node: Specify the number of HeatWave nodes to create. A minimum of 1 node to a maximum of 64 nodes are supported. If you enable Lakehouse, a maximum of 512 nodes are supported.
        Note

        The HeatWave.32GB shape supports a maximum of 16 nodes only.
      3. MySQL HeatWave Lakehouse: Enable query processing on the data residing in Object Storage. HeatWave Lakehouse is supported on the MySQL.HeatWave.VM.Standard and HeatWave.512GB shapes only. See MySQL HeatWave Lakehouse.
        Note

        Prior to MySQL 8.3.0-u2, you must disable point-in-time recovery, high availability, read replicas, and outbound replication in order to enable MySQL HeatWave Lakehouse.
      4. Click Save changes.
    • Data storage size (GB): Specify the amount of block storage, in GB, to allocate to the DB system. The value should be greater than 50 and less than 131072. The data storage size is independent of the shape you select.

      The initial storage value has an impact on your ability to increase the storage size later and the performance of the increased storage. See DB System Storage. Also, if you are importing data, ensure you specify sufficient data storage.

  10. Configure backup plan:
    • Enable automatic backups: (Optional) Select the option to enable automatic backups. If you do not select automatic backups, manage your backups manually. It is recommended to enable automatic backups.
    • Backup retention period: (Optional) Specify how may days you want to retain the backup. By default, the DB system retains the backup for seven days. Once you define the retention period of an automatic backup, you can not edit it later.
    • Enable point-in-time recovery: (Optional) Select the option to enable you to restore a DB system at a point-in-time to a new DB system.
    • Select backup window: (Optional) Select the option to enable you to select the backup window start time.
      • Window start time: (Optional) Specify the time (in UTC timezone) when you want to start the backup of your DB system. Your backup starts in the 30 minutes following the window start time.
      • Show backup windows per region: (Optional) Click the option to see the default window start time of your region.
  11. Show advanced options: Click to open a group of tabs that enable you to further configure the DB system. See Advanced Options.
  12. To create the DB system, click Create.
  13. (Optional) To save the resource configuration as a stack, click Save as stack. You can later use the stack to install, configure, and manage the resources through the Resource Manager service. See Managing Stacks.
The DB system is created.
Note

If you encounter OutOfHostCapacity work request error while creating a DB system, see Resolving OutOfHostCapacity Error.

Using the CLI With JSON Payload

Use the command-line interface to create a DB system with a JSON payload and import data from an Object Storage bucket.

This task requires the following:
  1. Open the command-line interface and run the following command with a JSON payload to create a DB system:
    oci mysql db-system create --from-json file://<File.json>

    The <File.json> file contains the following:

    {
      "adminUsername": "<AdminUsername>",
      "adminPassword": "<AdminPassword>",
      "compartmentId": "<CompartmentOCID>", 
      "configurationId": "<ConfigurationOCID>", 
      "shapeName": "<ShapeName>", 
      "dataStorageSizeInGbs": "<StorageSize>", 
      "description": "<Description>", 
      "displayName": "<DBSystemName>", 
      "hostnameLabel": "<HostName>",
      "availabilityDomain": "<Region>",
      "subnetId": "<SubnetOCID>",
      "backupPolicy": {
        "isEnabled": true,
        "retentionInDays": 7,
        "windowStartTime": "00:10", 
        "pitrPolicy": {
          "isEnabled": true 
         }
      },
      "source": {
          "sourceType": "IMPORTURL",
          "sourceUrl": "https://objectstorage.<region>.com/p/<Secret>/n/<Namespace>/b/<BucketName>/o/"
        }
    }
    • adminUsername: Specify the username of the root user of the DB system. Some usernames are reserved and you cannot use them in the definition of a DB system. See Reserved Usernames.
    • adminPassword: Specify the password of the root user of the DB system.
    • compartmentId: Specify the OCID of the compartment in which you create the DB system.
    • availabilityDomain: Specify the name of the availability domain in which you create the DB system.
    • configurationID: (Optional) Specify the OCID of the configuration to apply to the DB system. If you do not define a configuration ID, the DB system uses the default configuration associated with the selected shape. If you define a custom configuration, use the same shape as you define in the request.
    • shapeName: Specify the name of the shape. For example, MySQL.VM.Standard.E3.1.8GB.
    • subnetId: Specify the OCID of the VCN subnet to which the DB system is attached.
    • dataStorageSizeInGbs: Specify the amount of data storage, in GBs, for the DB system. The value should be greater than 50 and less than 131,072, and ensure you define enough storage to accommodate imported data. The data storage size is independent of the shape you select.
    • hostnameLabel: (Optional) Specify the hostname for your DB system. The option is available only if you enable USE DNS HOSTNAMES IN THIS VCN option on the selected VCN. The hostname must begin with an alphabetic character, contain only alphanumeric characters and hyphens (-), and not use more than 30 characters. The hostname must be unique within the subnet. If it is not unique, the DB system fails to provision. When you specify a hostname, Oracle assigns it to the VCN and subnet domain, which is automatically created during the instance launch, in the following format:
      <hostnameLabel>.<subnet DNS label>.<VCN DNS label>.oraclevcn.com
      For example, if you specify a value of myhost for hostnameLabel, on a subnet named myprivatesubnet, on a VCN named myvcn the fully qualified domain name of your DB system is set to the following:
      myhost.myprivatesubnet.myvcn.oraclevcn.com
    • description: (Optional) Specify a brief description of the DB system.
    • displayName: (Optional) Specify the display name of the DB system. If you do not define a display name, Oracle generates one for you in the format mysqldbsystemYYYYMMDDHHMMSS.
    • backupPolicy: (Optional) Specify the container for the backup policy parameters. The following parameters define the schedule for automatic backups of your DB system:
      • isEnabled: Set it to true to enable backups.
      • retentionInDays: Specify the number of days to retain the automatic backups. By default, your data is retained for seven days.
      • windowStartTime: Specify the start of a 30-minute window for your daily automated backups. It uses the UTC timezone in HH:MM format. You can also define offsets, for example: 00:00-08:00, which sets the time to 8 hours behind midnight UTC.
      • pitrPolicy: Set it to true to enable point-in-time recovery that restores data from a DB system to a new DB system at a given point-in-time.
    • source: (Optional) Specify the container for the import parameters. The following parameters specify the data import type and source URL:
      • sourceType: IMPORTURL: Specify the import type as a PAR URL.
      • sourceUrl: Specify the Pre-Authenticated Request (PAR) URL for the bucket or bucket prefix.
    • mysqlVersion: (Optional) Specify the version of MySQL to use in your DB system. If you do not specify it, the latest available version is used. See MySQL Server Upgrades and MySQL Server Versions.

Related Topics

Using the CLI Without JSON Payload

Use the command-line interface to create a DB system without a JSON payload and import data from an Object Storage bucket.

This task requires the following:
  1. Open the command-line interface and run the following command without a JSON payload to create a DB system:
     oci mysql db-system create 
     -c <CompartmentOCID> 
     --configuration-id <ConfigurationOCID>   
     --shape-name <ShapeName> 
     --availability-domain <Region>
     --subnet-id <SubnetOCID> 
     --admin-username <AdminUsername> 
     --admin-password <AdminPassword> 
     --data-storage-size-in-gbs <StorageSize> 
     --display-name <DBSystemName> 
     --description <Description>
     --hostname-label <HostName> 
     --backup-policy '{ "isEnabled": true, "retentionInDays": 7, "windowStartTime": "00:10", "pitr-policy": {"isEnabled": true} }'
     --source '{"sourceType": "IMPORTURL", "sourceURL":"https://objectstorage.<region>.com/p/<Secret>/n/<Namespace>/b/<BucketName>/o/"}'
    • c: Specify the OCID of the compartment in which you create the DB system.
    • configuration-id: (Optional) Specify the OCID of the configuration to apply to the DB system. If you do not define a configuration ID, the DB system uses the default configuration associated with the selected shape. If you define a custom configuration, use the same shape as you define in the request.
    • shape-name: Specify the name of the shape. For example, MySQL.VM.Standard.E3.1.8GB.
    • availability-domain: Specify the name of the availability domain in which you create the DB system.
    • subnet-id: Specify the OCID of the VCN subnet to which the DB system is attached.
    • admin-username: Specify the username of the root user of the DB system. Some usernames are reserved and you cannot use them in the definition of a DB system. See Reserved Usernames.
    • admin-password: Specify the password of the root user of the DB system.
    • data-storage-size-in-gbs: Specify the amount of data storage, in GBs, for the DB system. The value should be greater than 50 and less than 131,072, and ensure you define enough storage to accommodate imported data. The data storage size is independent of the shape you select.
    • display-name: (Optional) Specify the display name of the DB system. If you do not define a display name, Oracle generates one for you in the format mysqldbsystemYYYYMMDDHHMMSS.
    • description: (Optional) Specify a brief description of the DB system.
    • hostname-label: (Optional) Specify the hostname for your DB system. The option is available only if you enable USE DNS HOSTNAMES IN THIS VCN option on the selected VCN. The hostname must begin with an alphabetic character, contain only alphanumeric characters and hyphens (-), and not use more than 30 characters. The hostname must be unique within the subnet. If it is not unique, the DB system fails to provision. When you specify a hostname, Oracle assigns it to the VCN and subnet domain, which is automatically created during the instance launch, in the following format:
      <hostnameLabel>.<subnet DNS label>.<VCN DNS label>.oraclevcn.com
      For example, if you specify a value of myhost for hostnameLabel, on a subnet named myprivatesubnet, on a VCN named myvcn the fully qualified domain name of your DB system is set to the following:
      myhost.myprivatesubnet.myvcn.oraclevcn.com
    • backup-policy: (Optional) Specify the container for the backup policy parameters. The following parameters define the schedule for automatic backups of your DB system:
      • isEnabled: Set it to true to enable backups.
      • retentionInDays: Specify the number of days to retain the automatic backups. By default, your data is retained for seven days.
      • windowStartTime: Specify the start of a 30-minute window for your daily automated backups. It uses the UTC timezone in HH:MM format. You can also define offsets, for example: 00:00-08:00, which sets the time to 8 hours behind midnight UTC.
      • pitr-policy: Set it to true to enable point-in-time recovery that restores data from a DB system to a new DB system at a given point-in-time.
    • source: (Optional) Specify the container for the import parameters. The following parameters specify the data import type and source URL:
      • sourceType: IMPORTURL: Specify the import type as a PAR URL.
      • sourceUrl: Specify the Pre-Authenticated Request (PAR) URL for the bucket or bucket prefix.
    • mysqlVersion: (Optional) Specify the version of MySQL to use in your DB system. If you do not specify it, the latest available version is used. See MySQL Server Upgrades and MySQL Server Versions.

The command creates a DB system with backup and imports data from Object Storage using a pre-authenticated request (PAR) URL and you get a response similar to the following, which summarizes the request and creates your DB system:

{
  "data": {
    "availability-domain": "nnnn:<region>-AD-3",
    "backup-policy": {
      "is-enabled": true,
      "retention-in-days": 7,
      "window-start-time": "00:10",
      "pitr-policy": {    
         "is-enabled": true
        },
    "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-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,   
    "point-in-time-recovery-details": 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>"
}

Related Topics