MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.

This is the fifth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.

Episode 5 – Create a MySQL DB system from a MySQL Shell dump

In the previous episode we’ve seen how to export data from a MySQL instance to an Oracle Cloud Infrastructure bucket using the awesome MySQL Shell. One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll see how these data can now easily be imported into a MySQL Database Service instance.

The workflow

Again, the workflow is quite simple.
It is the continuation of the process initiated during the previous episode: Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket.

We will create a MySQL Database Service instance which will contain the data from the MySQL Shell dump stored inside the Oracle Cloud Infrastructure object storage bucket.

Thus the needed components are:

• The OCI Object Storage bucket created previously (see the previous episode)
• Either
  • the MySQL Shell load dump utility : util.loadDump()
  • the MDS Data Import feature

I would recommend to use the later because it is the easiest way to create and populate with your own data a MDS.
So let’s see how easy it is!

MySQL Database Service

MySQL Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle.
Oracle automates all tasks such as backup and recovery, database and operating system patching, and so on.

You are responsible solely for managing your data, schema designs, and access policies.

Note
Your OCI user must be part of a group that has the right privileges in order to work with MySQL Database Service.
At least the following rules:
Allow group to {SUBNET_READ, SUBNET_ATTACH, SUBNET_DETACH, VCN_READ, COMPARTMENT_INSPECT} in [ tenancy | compartment | compartment id ]
Allow group to manage mysql-family in [ tenancy | compartment | compartment id ]
Allow group to use tag-namespaces in tenancy
Please see: Mandatory Policy Statements / Policy Details for MySQL Database Service

Create a MySQL DB system from a MySQL Shell dump

Go to the OCI console, in the menu, go to: Databases / MySQL

Select the right compartment (left of the screen – DBA in this example) then press Create MySQL DB System.

Provide DB System information & create administrator credentials

Check that you are in the right compartment, name your MySQL instance and enter a meaningful description (optional but recommended).

Select your instance type – Standalone in this example – then create the administrator credentials, usaername & password (see this like your root account, although you obviously will not have all the privileges because it’s a PaaS).

Let’s take a quick look at the different current types of MySQL DB systems:

• Standalone: specifies a single-instance DB System.
• High Availability: specifies a three-instance DB System containing one primary instance, and two secondary instances. 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. For more information, see HeatWave.

Back to our MDS creation.

Network configuration & configure placement

Use the VCN created in Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network (Demo_VCN from the DBA compartment in this example).
And very important use the private subnet (it’s a good practice to put your databases in private subnet).

You can also place your MySQL DB system in the Availability Domain of your choice if your region has several. Same logic for the Fault Domain.

Configure hardware

This is were you’re going to choose the “power” of your MySQL instance.
Currently it starting from 1 to 64 CPU cores and 8 GB to 1 TB for the memory size.

In the Data Storage Size box you can enter in GB the amount of storage to allocate to the MySQL DB System for all data and log files.

Configure Backup Plan

You can specify the backup details ie enable/disable automatic backups, setup the retention period and the backup window.

We almost there 🙂
In fact, if you want to create an empty MySQL DB system you can push the Create button right now.

However, because we want to create this MySQL instance automatically populated with our data that we previously dumped (see Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket) we have a few more steps, “hidden” under the Show Advanced Options link.

Data Import

Select the Data Import tab

In order to create the PAR URL for our MySQL Shell dump stored in the bucket, we must click on
Click here to create a PAR URL for an existing MySQL Shell dump file (@.manifest.json)

A new form appears where you need to

• Select the right bucket (again check that you are in the right compartment), bucket in DBA
• Select the MySQL Shell dump manifest file (@.manifest.json)
• Specify an expiration time for the PAR

Brilliant!
We have all the information to Create and set PAR URL.

So we can create our MySQL DB system that will contain our data.
Click on Create.

After a while (mostly dependent on the quantity of data to import) your MySQL instance is up and running \o/

You then have access to diverse information.
Among others, the endpoint which gives you some relevant ones from the connection point of view like the private IP Address and the default MySQL 8.0 ports 3306 & 33060 respectively for classic and X protocols.

Well, that’s all for today!
In this episode, we have seen how to create a MySQL DB system from a MySQL Shell dump stored into an Oracle Cloud Infrastructure bucket.

Next episode is:

Update the Private Subnet Security List

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Overview of Object Storage
• Using Pre-Authenticated Requests (PAR)
• MySQL Shell
• MySQL Shell Utilities
• MySQL — The world’s most popular open source database

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
Cloud, Howto, MDS, MySQL, Tuto by Olivier DASINI