Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin

July 18, 2022
Tags: , ,

MySQL – The world’s most popular open source database – is also probably the most popular database in the Cloud.
MySQL HeatWave database service is THE MySQL PaaS, a fully managed cloud service in Oracle Cloud Infrastructure (OCI).

The only MySQL service 100% developed, managed and supported by the MySQL Team.

In short:

MySQL HeatWave is a massively parallel, high performance, in-memory query accelerator that accelerates MySQL performance by orders of magnitude for analytics workloads, mixed workloads, and Machine Learning.

MySQL HeatWave Architecture

For your analytics needs you can explore and visualize your data using a data visualization tool like Tableau, Qlik, Oracle Analytics Cloud, Apache Superset (see: Explore & visualize your MySQL HeatWave data with Superset)…

For your Machine Learning needs you can use a notebook like Jupyter, Apache Zeppelin (see: Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin),…

In this article I will show you how easy it is to use the Machine Learning capabilities of MySQL HeatWave. We will use the famous Iris dataset and we’re going to build, train, deploy and explain a machine learning model within MySQL HeatWave using Apache Zeppelin.

Prerequisites

To be able to reproduce this tutorial, you must have a MySQL HeatWave instance running (details here and also RTFM).
The data used in this article are available on my GitHub account here.

You need a MySQL client to load the data into MySQL HeatWave. I’m using MySQL Shell.
Furthermore if you want to use Zeppelin (although optional because we’re going to run mainly sql queries) , it must be properly setup.
Please read: Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin
You can also download the Zeppelin notebook here.

Finally, you might need to install some packages.
ex: (pip install pandas numpy unlzw3 sklearn seaborn matplotlib jupyter grpcio protobuf)

MySQL HeatWave ML

MySQL HeatWave ML makes it easy to use machine learning, whether you are a novice user or an experienced ML practitioner

You provide the data, and HeatWave ML analyzes the characteristics of the data and creates an optimized machine learning model that you can use to generate predictions and explanations. 

HeatWave ML supports supervised machine learning. That is, it creates a machine learning model by analyzing a labeled dataset to learn patterns that enable it to predict labels based on the features of the dataset. HeatWave ML supports both classification and regression models. 
More information here.

In-database Machine Learning with MySQL HeatWave
In-database Machine Learning with MySQL HeatWave

All HeatWave ML operations are initiated by running CALL or SELECT statements, which can be easily integrated into your applications.

HeatWave ML routines include:

Load the data

In a real life scenario, your data are already inside your MySQL instance. Although you might need to prepare them upfront, because the relevant data set must be inside a single table.

I’m using MySQL Shell (installed on the server that host Zeppelin) to load the data into the iris_ML schema. The dump file – MySQL_HeatWave-iris_ML.sql – was previously uploaded to the server into /home/ubuntu.

The dump contains 4 tables:

  • iris : the reference table ie source of truth, production data
  • iris_test : contains the test dataset
  • iris_train : contains the training dataset
  • iris_validate : contains the validation dataset

Check HeatWave ML status

HeatWave ML is enable by default as soon as your MySQL HeatWave Cluster is active.

The rapid_ml_status variable provides the status of HeatWave ML:

HeatWave ML is up and running \o/

Data exploration

The first step of any project based on data is to look at the data.
Summarize and visualize the data will help you to have a better understanding of the project.

It may surprise you but SQL provides some commands and functions for data exploration, although much less extensive than R or Python 🙂 :

iris simulate the live production table. It contains the original data (150 rows) and actually it will not be used (as is) by HeatWave ML.
To keep things simple as possible in this article, iris as the same structure than iris_train. But in a real life scenario it will most likely not the case.
This production table may have additional columns like for example: a primary key, timestamp, etc… any useful business related information that are not relevant for building our model.

iris_train contains the training dataset a subset of iris table (120 rows), used to train the machine learning model.
This table is typically a subset of the reference table(s).

iris_test contains the test dataset a subset of iris table (30 rows), different than iris_train. Its structure is almost similar to iris_train but without the target column (class) .

iris_validate contains the validation dataset (30 rows). Same data than iris_test but same structure than iris_train in other words this table has the target column (class).

General requirements for HeatWave ML here.

Class distribution is well balanced:

  • Iris-virginica: 50 rows
  • Iris-setosa: 50 rows
  • Iris-versicolor: 50 rows

Data visualization

Visualize your data is probably the more convenient way to explore and understand them.

Below a little Python script to generate some graphs.

Beforehand, I had to edit the Zeppelin python interpreter – zeppelin.python – and replace python by python3:

Update zeppelin.python:  replace python by python3
zeppelin.python: python3:

The interpreter is: %python.ipython
The script is going to connect to MySQL HeatWave, thus you must update the database information (host, database, user, password)

HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.

Dans ce webinaire vous apprendrez

The information above come from the Iris table (reference table). It could interesting to compare these data with the training dataset (iris_train) and the test dataset in order to evaluate the quality of these samples.

Replace query = “SELECT * FROM iris;” by query = “SELECT * FROM iris_train;” and then query = “SELECT * FROM iris_test;”

Training a Model

Now we have a better understanding of the data, let’s moving forward and train the model. We’re having a classification problem.

The ML_TRAIN routine, when run on a training dataset, produces a trained machine learning (ML) model.

You can show the current model, selecting the session variable @iris_model:

The lifetime duration of a session variable is… the session lifetime duration. So when the session is closed the session variable content is lost.

ML_TRAIN stores the machine learning model – @iris_model – in the MODEL_CATALOG table:

You can take the last created model using the following query:

When the model is created (using ML_TRAIN) you should load it into HeatWave ML (ML_MODEL_LOAD):

Row Prediction

HeatWave ML allows you to make prediction for individual rows or the entire table.

Row(s) predictions are generated by running ML_PREDICT_ROW.
Data are specified in JSON format.

Row Explanation

Being able to understand and explain a prediction is important in order to trust your model and to be able to explain the results. It might also be required by your local regulation.

ML_EXPLAIN_ROW generates explanations for one or more rows of data. Explanations help you understand which features have the most influence on a prediction.
Feature importance is presented as a value ranging from -1 to 1.

  • A positive value indicates that a feature contributed toward the prediction.
  • A negative value indicates that the feature contributed toward a different prediction

Table Prediction

ML_PREDICT_TABLE generates predictions for an entire table and saves the results to an output table.

A new table called iris_predictions is created. You can display Its first 5 rows with:

Table Explanation

ML_EXPLAIN_TABLE explains predictions for an entire table and saves results to an output table.

A new table called iris_explanations is created. You can display Its first 5 rows with:

Scores

Scoring the model allows to assess the model’s reliability.

Models with a low score can be expected to perform poorly, producing predictions and explanations that cannot be relied upon. A low score typically indicates that the provided feature columns are not a good predictor of the target values.

HeatWave ML supports a variety of scoring metrics to help you understand how your model performs across a series of benchmarks. 
Details here.

ML_SCORE returns a computed metric indicating the quality of the model.

Other metrics are also available. See here.

Here we go!
We’ve seen the machine learning life cycle.

Machine Learning Life Cycle

I told you that Machine Learning with MySQL HeatWave was easy 🙂

You only need to use a limited set of SQL routines:

During the last 20 years MySQL has democratized the usage of transactional databases.
Now with MySQL HeatWave we are in the process of democratizing Analytics and Machine Learning.

With MySQL HeatWave,
valorizing your data has never been so easy!

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Iris Versicolor
Iris Versicolor
1

Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin

July 5, 2022
Tags: ,

MySQLThe world’s most popular open source database – is a very good and safe choice as a transactional database, on-premise and in the cloud.

In the article – Explore & visualize your MySQL HeatWave data with Superset – I introduced MySQL HeatWave – a fully managed database service, that combines transactions, analytics, and machine learning services into one MySQL Database, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication.
MySQL HeatWave is a native MySQL solution thus current MySQL applications work without changes.

Enabling a HeatWave Cluster also provides access to HeatWave Machine Learning (ML), which is a fully managed, highly scalable, cost-efficient, machine learning solution for data stored in MySQL.
HeatWave ML provides a simple SQL interface for training and using predictive machine learning models, which can be used by novice and experienced ML practitioners alike.
Machine learning expertise, specialized tools, and algorithms are not required.
With HeatWave ML, you can train a model with a single call to an SQL routine. 

Below some numbers comparing MySQL HeatWave ML vs AWS Redshift ML:

MySQL HeatWave  ML is 25x faster than Redshift ML
25x faster on average without compromising accuracy
MySQL HeatWave ML is 25x faster than Redshift ML
MySQL HeatWave  ML is 1% of cost of Redshift ML
1% of cost and no additional cost for MySQL HeatWave customers
MySQL HeatWave ML is 1% of cost of Redshift ML
Better performance with HeatWave ML with larger cluster size
Better performance with HeatWave ML with larger cluster size

For more details & raw numbers read: Performance comparison of HeatWave ML with Redshift ML.

MySQL HeatWave is currently available on Oracle Cloud Infrastructure (OCI). Availability on Amazon Web Services (AWS) and Microsoft Azure is coming (Oracle’s “HeatWave” is going multicloud: How hot will things get for MySQL?).

MySQL HeatWave ML can also takes models tuned in MySQL and use them to answer queries from popular machine learning notebook services for code and data, such as Jupyter and Apache Zeppelin.

Apache Zeppelin is an open source web-based notebook that allows data ingestion, data discovery, data analytics, data visualization and data collaboration.
Apache Zeppelin supports many interpreters such as MySQL, Python, JDBC, Shell, Spark, …
More: https://zeppelin.apache.org/

In this article I will show you how to properly configure Apache Zeppelin in order to take advantage of the Analytics and Machine Learning capabilities of MySQL HeatWave the MySQL in-memory query accelerator.

MySQL HeatWave

I’m using OCI and I will not go into all the installation details (RTFM). Below the main steps.

Using your favorite Internet browser, open your OCI console, select a compartment and go to Databases / MySQL / DB Systems.

Create a DB Systems and select HeatWave.

The rest is pretty straightforward!

Please note the Show Advanced Options link at the bottom of the page.

It’s Data Import tab is useful if you want to create an instance with data.

More information in this article: Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

Now we must add the HeatWave cluster.

On the left, under Resources click HeatWave.

Add the HeatWave Cluster…

Set the Node Count

I would recommend to estimate the node count, click Estimate Node Count then Generate Estimate:

Copy the Load Command.
It will be used to load the chosen data into the HeatWave cluster.

Apply the node count estimate:

Finally create the cluster by pushing the button: Add HeatWave Cluster

When the cluster is running, the very last step is to offload the data to the cluster by running the stored procedure (i.e. the “Load Command”) copied previously using your favorite MySQL client.

e.g. Offload to the HeatWave cluster all the tables inside the airportdb schema:
CALL sys.heatwave_load(JSON_ARRAY(‘airportdb’), NULL);

Now you have a MySQL HeatWave cluster up and running with some data loaded into the cluster;
Cheers!

Apache Zeppelin

I assume that you already know what is Apache Zeppelin. If not RTFM.

Please note this is a test installation, probably not suitable for a production environment.

Requirements

Apache Zeppelin officially supports and is tested, among other, on Ubuntu 20.04, so I’ve installed Zeppelin on a VM Ubuntu 20.04 from Oracle Cloud Infrastructure (OCI).

In addition to the OS, you also need the good version of OpenJDK or Oracle JDK.

I’m good to go!

Binary

I downloaded the binary package with all interpreters. Available here.

Unpack it in a directory of your choice and you’re ready to go!

Firewalls

Depending on your architecture, you may have to deal with some firewalls.

In OCI I must update my Ingress Rules for the Default Security List (OCI virtual firewall) for the protocol TCP and the port you are going to use, 8080 in this article:

On the Ubuntu side I also had to update IPtable.

Starting Apache Zeppelin

By default Zeppelin is listening at 127.0.0.1:8080, so you can’t access it when it is deployed on another remote machine.
To access a remote Zeppelin, you need to change zeppelin.server.addr to 0.0.0.0 in conf/zeppelin-site.xml.

When the update is done:

Address is 0.0.0.0 and Port is 8080.

Now I can start Zeppelin:

Yippee!

MySQL Connector J

The Zeppelin SQL interpreter needs the JDBC MySQL Connector, available at https://dev.mysql.com/downloads/connector/j/

Obviously, MySQL customers can use the commercial version available on MOS.

The JDBC MySQL Connector is now installed!

Setup the Zeppelin interpreter

Create a new interpreter:

Enter a name for the Interpreter Name box and select jdbc as Interpreter group:

  • Interpreter Name: MySQL HeatWave
  • Interpreter group: jdbc

JDBC interpreter lets you create a JDBC connection to any data sources seamlessly. Inserts, Updates, and Upserts are applied immediately after running each statement.

Properties

  • default.url : jdbc:mysql://<MySQL HeatWave IP Address>:3306/
  • default.user : <MySQL User>
  • default.password: <MySQL Password>
  • default.driver : com.mysql.jdbc.Driver

Dependencies

  • Dependencies artifact : mysql:mysql-connector-java:8.0.29

Don’t forget to save the configuration 🙂

To sum up, the interpreter configuration looks like:

MySQL HeatWave usage

I can now create a notebook – Check MySQL HeatWave (download the notebooks) – in order to check if HeatWave is enable:

The Default Interpreter must be MySQL_HeatWave:

For the check, let’s run these 2 queries:

The HeatWave cluster is up and ML is enable.
All good!

Analytics

Out of curiosity, let’s try some analytic queries.

I already loaded the airportdb sample database (see here) into the MySQL DB system.

Let’s create a new note – Airportdb Analytics Queries (download the notebooks):

airportdb must now be loaded into the HeatWave cluster in order to take advantage of the tremendous performance:

And here are the queries:

Great results, respectively less than 0 second and 1 second.

Without HeatWave the query execution times would be respectively 10 and 17 seconds:

Machine Learning

What about the Machine Learning possibilities of MySQL HeatWave ML?

Well this article is already long enough, so it will be the topic of the next article 🙂

Stay tune!

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

0

Explore & visualize your MySQL HeatWave data with Superset

June 9, 2022

MySQLThe world’s most popular open source database – is probably the best choice for a transactional database especially when considering the costs / performance ratio, and that on-premise or in the cloud.

But what about business intelligence (BI) needs? Analytics workloads?

We’ve got you covered now with the versatile MySQL HeatWave – a fully managed database service, that combines transactions, analytics, and machine learning services into one MySQL Database, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication.
MySQL HeatWave is a native MySQL solution thus current MySQL applications work without changes.

MySQL HeatWave is also fast, super fast, easy to use and provides an incredible price/performance ratio…

Details & more numbers: Performance comparison of MySQL HeatWave with Snowflake, Amazon Redshift, Amazon Aurora, and Amazon RDS for MySQL.

MySQL HeatWave is currently available on Oracle Cloud Infrastructure (OCI). Availability on Amazon Web Services (AWS) and Microsoft Azure is coming (Oracle’s “HeatWave” is going multicloud: How hot will things get for MySQL?).

Indeed, having a fast and easy to use database is valuable. But analytics also imply that you need to explore and visualize your data.
There are plenty of tools available on the market, one of the them is Apache Superset, an open source modern data exploration and visualization platform.

In this article I will show you how to properly configure Apache Superset in order to take advantage of a high performance, in-memory query accelerator: MySQL HeatWave.

MySQL HeatWave

I’m on OCI and I will not go into all the installation details (RTFM). Below the main steps.

Using your favorite Internet browser, open your OCI console, select a compartment and go to Databases / MySQL / DB Systems.

Create a DB Systems and select HeatWave.

The rest is pretty straightforward!

Please note the Show Advanced Options link at the bottom of the page.

It’s Data Import tab is useful if you want to create an instance with data.

More information in this article: Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

Now we must add the HeatWave cluster.

On the left, under Resources click HeatWave.

Add the HeatWave Cluster…

Set the Node Count

I would recommend to estimate the node count, click Estimate Node Count then Generate Estimate:

Copy the Load Command.
It will be used to load the chosen data into the HeatWave cluster.

Apply the node count estimate:

Finally create the cluster by pushing the button: Add HeatWave Cluster

When the cluster is running, the very last step is to offload the data to the cluster by running the stored procedure (i.e. the “Load Command”) copied previously using your favorite MySQL client.

e.g. Offload to the HeatWave cluster all the tables inside the airportdb schema:
CALL sys.heatwave_load(JSON_ARRAY(‘airportdb’), NULL);

Apache Superset

I assume that you know what is Apache Superset. If not RTFM.
I also assume that it is already installed. You’ll find the necessary information in their documentation: Installing Superset from Scratch or Installing Superset Locally Using Docker Compose.

it is worth noting that I had some issues during the installation process following the documentation.

For the record, below what I’ve done using a VM Ubuntu 20.04 from Oracle Cloud Infrastructure (OCI).
Please note this is a test installation, not suitable for a production environment.

Install the required dependencies (Ubuntu or Debian):

Upgrade pip (and prevent some issues)

Install Superset

Binaries are in $HOME/.local/bin
I decided to put the following information into my ~/.bashrc (however not sure it is a good practice, but good enough for testing)

then

Initialize the database:

Please not that you will probably have to update your firewall.

If like me you are on OCI you must update the security list (OCI virtual firewall) for the protocol TCP and the port you are going to use, 8088 in this article

On Ubuntu you may have to update IPtable.

Start Superset

Superset is listening on port 8088

If everything worked, you should be able to navigate to hostname:port in your browser and login using the username and password you created.

If you have everything on your laptop, you can remove -h 0.0.0.0 and then only local connections will be allowed (i.e. navigate to localhost:8088).

Install MySQL Connector Python 8.0

Unlike what is written here, I would recommend to use MySQL 8.0, to download and use the official MySQL Python connector available on the MySQL website.

Go to https://dev.mysql.com/downloads/connector/python/ for the latest version.

Obviously, MySQL customers can use the commercial version available on MOS.

Configure Superset for MySQL HeatWave

Using the Apache Superset UI you can add a new backend database.

Go to Data / Databases

Then click + DATABASE

Add your MySQL HeatWave database.
There are 2 important parts:

1/ SQLALCHEMY URI :

mysql+mysqlconnector://<MySQL user>:<MySQL password>@<MySQL host>

You need the MySQL user, password and IP.

2/ ENGINE PARAMETERS

In order to take advantage of the power of MySQL HeatWave, autocommit must be enabled.

If autocommit is disabled, queries are not offloaded and execution is performed on the MySQL DB System. In other words, the queries will be much slower. Details here.

Go to Advanced / Other / ENGINE PARAMETERS

{“isolation_level”:”AUTOCOMMIT”}

You all set 🙂

Thanks to MySQL HeatWave, you can now add your datasets and build awesome and very fast dashboards, explore and visualize your data at the speed of light.

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

2

Data Migration from MariaDB to MySQL

March 9, 2022

Yet another customer wanted to migrate from MariaDB to MySQL. So I decided to write down the overall data migration process.

Indeed each migration are a unique story so I won’t provide you a “How to migrate”…
The idea is to highlight the general path and help you to avoid common pitfalls and thus help you save some precious time.

Context

  • Source database
    • MariaDB 10.3
    • Around 8GB of data
    • Only InnoDB tables
    • Many user accounts (important information)
  • Target databases
    • MySQL Database Service 8.0.28
    • MySQL 8.0.28

In fact, the (real) target was MySQL Database Service the MySQL PaaS on Oracle Cloud Infrastructure, but I will also show the workflow for on-premise MySQL.

Please find below a reenactment of the events…

MySQL Shell

The first step is to install the MySQL DBA BFF: MySQL Shell.
MySQL Shell is an advanced client and code editor for MySQL. You can manage your data using SQL, JavaScript and Python.

In addition to data manipulation (btw MySQL 8.0 also provides a NoSQL CRUD API to handle JSON documents), there are also a lot of things you can do with MySQL Shell like create a MySQL cluster (InnoDB Cluster, InnoDB ReplicaSet, InnoDB ClusterSet),  verify whether your MySQL server instances is ready for upgrade, dump and restore, etc… :

Believe me if you try it, you will love it 🙂


It is way better than the old mysql text client and some of these features work with MariaDB (at least the ones needed for the migration).

Easy to install.
e.g.

The source dataset

Let’s have a look on the dataset.

Unfortunately MariaDB does not have the sys schema
We cannot use this very elegant query:

So back in the time and let’s use the information_schema instead and the following ugly query:

Approximately 8GB of data.

What about the storage engines?

Again because of lack of sys schema we cannot use this pretty query:

So let’s back in the time and use instead:

All tables are in InnoDB \o/
That’s how DBA’s life should be 🙂

Migrate the data

The source is MariaDB 10.3 and the target is MySQL 8.0, on-premise or using MySQL Database Service.

With such amount of data ~10GB a dump is not an issue especially if you use MySQL Shell utilities (please forget about the aging mysqldump – it deserves a nice retirement).

Migrate to MySQL on-premise

Because I need the whole instance, I’m going to use MySQL Shell dumpInstance utility.

An important option of dumpInstance is compatibility. It can

  • enforce the InnoDB storage engine (force_innodb) ie change CREATE TABLE statements to use the InnoDB storage engine for any tables that do not already use it
  • ignore any missing primary keys (ignore_missing_pks),
  • automatically add primary keys in invisible columns (create_invisible_pks)
  • etc…

Here, I will only use force_innodb. (if your memory is better than mine you remember that all the tables are already in InnoDB, so not really needed in this case, but I must admit that I’m paranoid :-O).
Joke aside, it is in my opinion a good habit to use this option.

If you are worried about primary keys below a query that would help you to check your schema:

The MySQL Shell dump instance command for dumping data from MariaDB to MySQL 8.0 on-premise looks like:

  • The dump will be stored in the /bkp/dumpMariadb1 directory
  • threads: 8 -> 8 parallel threads to dump chunks of data from the MariaDB instance
  • compatibility: force_innodb -> update to InnoDB for any tables that do not already use it

Depending on your context you may have to tune this command.

Move the dump on the MySQL 8.0 host and then restore the dump.

In MySQL 8.0 there is a special ninja technique 🙂 to load a dump at the speed of light… All right, I may be exaggerating a bit, but at least at the speed of the sound by disabling crash recovery aka disabling redo logging.

DISCLAIMER: This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.

The commands to restore the dump on MySQL 8.0 on-premise look like:

  • local_infile=1 -> allows local data loading
  • ALTER INSTANCE DISABLE INNODB REDO_LOG -> disable redo logging to speed up data loading
  • The dump has been moved to the MySQL instance at /restore/dumpMariadb1
  • threads: 8 -> 8 parallel threads to  upload chunks of data to the MySQL instance
  • ignoreVersion: true -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version)
  • skipBinlog: true -> skips binary logging for the sessions used during the restore
  • loadUsers: false -> do not import users and their roles and grants. This one is very important because MariaDB users are not compatibles. Please note that the default is false (paranoid mode enable) 

Migrate to MySQL Database Service

As a prerequisites I’m assuming that you have the basic Oracle Cloud Infrastructure and MySQL Database Service knowledge.
If it is not yet the case, please read my Discovering MySQL Database Service series.

To migrate your data into MySQL Database Service (MDS), MySQL Shell is again your best friend to export the data.
You can either store the data locally or very easily in an OCI object storage bucket.

If the data are located in a bucket you can then use the MDS data Import option to automatically create a new MDS instance populated with your data.
If you choose the “local” storage, the import will be manual only.

The main thing to keep in mind during the import process is that you will most likely have a user account issue (MariaDB users are not compatibles), so my recommendation is to take a MySQL Shell dumps with all the business data (i.e. mysql, information_schema, performance_schema, sys are not include) without the user accounts.

Because I need the whole instance, in both case I’m using MySQL Shell dumpInstance utility.

Backup the database into a bucket

I’m going to store my dump inside an Object Storage bucket so I must create the bucket before the dump. This is very easy by using the OCI interface.

If you are not familiar with OCI Object Storage you can follow this tutorial: Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket.

In order to put these data into the OCI bucket, we must make some configuration for allowing MySQL Shell to easily connect to the OCI object storage service.

To this end, we will create an OCI CLI configuration file.

The CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks. The CLI provides the same core functionality as the Console, plus additional commands. Some of these, such as the ability to run scripts, extend Console functionality.

To be clear, we will not use the CLI but its configuration file. The configuration file name and default location (on Linux) is /home/opc/.oci/config.

It should have the following information:

  • user: OCID of the user calling the API.
  • fingerprint: Fingerprint for the public key that was added to this user.
  • key_file: Full path and filename of the private key.
  • tenancy: OCID of your tenancy.
  • region: An Oracle Cloud Infrastructure region.

For more details please click here.

As an example, mine looks like:

You may be wondering how to get this information? Fair question!
Don’t panic I already explained everything in this tutorial: Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket

My Bucket is named: migrationMariadbNoUser

The MySQL Shell dump instance command for migrating data from MariaDB to MDS and stored data into a bucket looks like:

  • users: false -> exclude users and their roles and grants in the dump
  • osBucketName: “migrationMariadbNoUser” -> name of the Object Storage bucket to which the dump is to be written
  • osNamespace: “abcdefghijkl” -> namespace where the Object Storage bucket named by osBucketName is located
  • ocimds: true -> enables checks and modifications for compatibility with MDS
  • threads: 8 -> 8 parallel threads to dump chunks of data from the MariaDB instance
  • compatibility: ->
    • force_innodb -> change CREATE TABLE statements to use the InnoDB 
    • strip_restricted_grants -> remove specific privileges that are restricted by MDS
    • strip_definers -> remove the DEFINER clause from views, routines, events, and triggers
    • strip_tablespaces -> all tables are created in their default tablespace

Complete documentation is available here.

The command above is storing the dump, compatible with MDS, into the bucket migrationMariadbNoUser.

Backup the database locally

If your dataset is not big and/or you are too lazy to spend a little time in configuration, the alternative is to dump your data locally.
The main drawback is that you will have to import the data into MDS manually – but this should not be a big deal if your dataset is not huge.

If you want to store the dump locally the command looks like:

The command is close to the one for the on-prem.
Please note – users: false – option. You can also dump the user and then run the import without loading the users (default behavior).

In fact, the most import difference compare to an on-prem data migration are the compatibility options. Because MDS is a PaaS you have less flexibility than the on-prem MySQL so more compatibility needs.

Restore into MySQL Database Service from a bucket

You have 2 solutions, the automated one, easiest but less flexible or the manual.

The first one is the MDS Data Import feature using the OCI interface.

MySQL Data Service - Data Import
MySQL Data Service – Data Import

You’ll find the “How To” in: Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

More details in the documentation.

If you want to speed up your import you can disable the Crash Recovery (during the import and enable it when it is finished). Some details here.

You can also restore the dump manually, in a new fresh MDS instance.

The command looks like:

Please note that in this case, you won’t be able to speed up the restore by disabling the crash recovery system (Redo Logs).

Restore into MySQL Database Service from a local dump

In this case we cannot use the crash recovery trick nor the Data Import feature.
Anyway the MySQL Shell dumpInstance function will still do the job.

The command looks like:

  • threads: 8 -> 8 parallel threads to  upload chunks of data to the MySQL instance
  • ignoreVersion: true -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version)

loadUsers: false is not necessary here because the dump was made with users: false.

And don’t forget to re-create your users 🙂
You have plenty of authentication plugins to create secure user accounts.
We also provide Multifactor Authentication.

Btw if security is one of your concern (and it should be) below some MySQL password management features:

Database migration is not only about data migration, so you still have some work to do. But now you should have a better understanding of the overall data migration process.

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

0

Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell

October 5, 2021
Tags: , ,

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 tenth 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.

MySQL Database Service

Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell

In the previous episode we’ve seen how to connect to our MySQL Database Service instance with MySQL Workbench using a SSH port forwarding from the OCI Bastion service.

And in episode 8Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell – we’ve seen how to connect to our MySQL Database Service instance with MySQL Shell using here again a SSH port forwarding from the OCI Bastion service.

Two more steps to our Discovering MySQL Database Service journey.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using the Oracle Cloud Infrastructure Cloud Shell.
It provides a pre-authenticated Oracle Cloud Infrastructure CLI and preinstalled developer tools for easily managing Oracle Cloud resources.

Cloud Shell

Oracle Cloud Infrastructure Cloud Shell gives you access to an always available Linux shell directly in the Oracle Cloud Infrastructure Console.

You can use the shell to interact with resources like MySQL Database Service, Oracle Container Engine for Kubernetes cluster, Oracle Autonomous Database, …

Cloud Shell provides:

  • An ephemeral machine to use as a host for a Linux shell, pre-configured with the latest version of the OCI Command Line Interface (CLI) and a number of useful tools
  • 5GB of storage for your home directory
  • A persistent frame of the Console which stays active as you navigate to different pages of the console

For more information, please see the Cloud Shell documentation 

Create a SSH port forwarding session

In episode 7Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – we created a Bastion, a SSH port forwarding (SSH tunneling) session and generated SSH key pair.


We are now going to use these in order to connect to MDS with Cloud Shell. However if you remember well the CIDR block allowlist is set to your location (IP of your office, of your home if you WFH, …), but not the Cloud Shell IP.

So many possibilities, we can setup this bastion only for Cloud Shell, we can add another bastion, dedicated for the Cloud Shell, we can update the current bastion. I’ll go for this last option.

So the plan is to connect to Cloud Shell and get the IP address.

Open the OCI console and click on the Cloud Shell icon, on the top right corner:

It will open the Cloud Shell in the bottom of the browser (the first time it can takes some time).

To get the IP address run the following command:

We can now update (or setup) the Bastion.

Go to the OCI console, in the menu, go to: Identity & Security / Bastion, Check that you are in the right compartment then click on the Bastion we previously created – BastionMDS in this example – or create a new one if you prefer/can.

Click on Edit

Update the CIDR Block Allowlist with the IP that you got when you executed curl ifconfig.me.
Don’t forget the /32 (CIDR block)

Then saves the changes.

We can now create a session and then get the SSH port forwarding command generated by OCI.

Click on Create Session

And like we have seen in Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session, create the session with the relevant parameters:

Then you can either generate (a new) SSH key pair or choose the public key we’ve created in Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session.

The private SSH key must be download into the Cloud Shell and be protected (chmod 600)

Drag and drop your private SSH key to the Cloud Shell and execute chmod 600 on the file

For the last steps we must now get the SSH port forwarding command generated by OCI and copy/paste it to the Cloud Shell.

To do so, when the session is active, go to the right and click on the action menu (3 vertical dots on the right).
A contextual menu is showing up then click on copy SSH command.

Paste this command in your Cloud Shell prompt.

The SSH command looks like :

Replace <privateKey> with the path to the private key we’ve just downloaded.

Replace <localPort> with a free port on the Cloud Shell – 3306 (and 33060) should be free.

Example:

Before run the command add an ampersand (&) at the end.

FYI, the error message “bind: Cannot assign requested address” is not a problem, this is just because the Cloud Shell tries to bind on ipv6 too.
If you want to avoid it, just add -4 between ssh and -i like this: ssh -4 -i

Connect to MDS using MySQL Shell in Cloud Shell

As you can see in the picture above, MySQL Shell is part of Cloud Shell.

So we will use MySQL Shell to connect to our MySQL instance through the SSH tunnel using MySQL

I recommend you to read Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell for more details.

Basically you’ll need the following information for the connection:

  • MySQL client is here MySQL Shell : mysqlsh
  • MySQL Database Service user you’ve created : admin
  • IP to connect to MDS (through the SSH tunnel) : localhost
  • Port to connect to MDS (through the SSH tunnel) : 3306
  • Your MDS password

We are now connected to our MySQL Database Service instance \o/
The server version is here 8.0.26-cloud MySQL Enterprise

And as expected we can see and query the data we download during Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump.

Well, that’s all for today!
In this episode, we have seen how to securely connect to a MySQL Database Service instance using the Oracle Cloud Infrastructure Cloud Shell through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

MySQL Database Service Features Overview

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

0

Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench

September 28, 2021
Tags: , ,

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 ninth 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.

MySQL Database Service

Episode 9 – Connect to MySQL Database Service Using MySQL Workbench

In the previous episode we’ve seen how to connect to our MySQL Database Service instance with MySQL Shell using a SSH port forwarding from the OCI Bastion service.
One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using MySQL Workbench – a unified visual tool for database architects, developers, and DBAs that provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

MySQL Workbench

MySQL Workbench is a GUI client for MySQL. It’s an unified visual tool for database architects, developers, and DBAs.

MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

It is available on Windows, GNU Linux and Mac OS X.
You can download the community version here.

Create a SSH port forwarding session

In episode 7 – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – we created a Bastion, a SSH port forwarding (SSH tunneling) session and generated SSH key pair.


We are now going to use these in order to connect to MDS with MySQL Workbench.

If your session is not valid anymore, you must create a new one (see Create a session paragraph from Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session).

Please note that you need a SSH client on your computer. If you are using a GNU Linux, MacOS, Windows 10 system you are good to go.

If not… all is not lost 🙂
Please read episode 10 : Connect to MySQL Database Service Using OCI Cloud Shell.

In the console menu, go to Identity & Security / Bastion, check that you are in the right compartment then click on the Bastion we previously created (BastionMDS in this example).

You are now seeing the session we already created (if not create a new one).

We now must get the SSH port forwarding command generated by OCI.

To do so, go to the right and click on the action menu (3 vertical dots on the right). A contextual menu is showing up then click on copy SSH command.

Paste this command in your shell prompt.

The SSH command looks like :

Replace <privateKey> with the path to the private key we downloaded in the previous episode – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session

Replace <localPort> with a free port on the local machine from which you want to connect to the bastion (ie your computer/laptop).

Example:

Then run the command…

If you hit the following error: WARNING: UNPROTECTED PRIVATE KEY FILE!

Don’t worry 🙂
Like stated in the error message, the private key must be protected. A chmod 600 (or equivalent regarding your OS) will deliver you.

Then run the command again:

No error anymore and no prompt.
But do not close the window if not it will close the SSH tunnel (thus you will need to run the command again).

Alternatively, you can add an ampersand (&) at the end of the command.

Connect to MDS using MySQL Workbench

Now it is the time to connect to our MySQL instance through the SSH tunnel using MySQL Workbench.

Open MySQL Workbench.
Go to Database / Manage Connections

The Manage Server Connections appears, then click the New button (at the bottom left).

We can now setup our MDS connection in the form.

The main information must be entered in the Connection tab:

  • Connection Name : MDS1 (sorry I’m very bad at naming)
  • Connection Method : Standard (TCP/IP)
  • Hostname or IP to connect to MDS (through the SSH tunnel) : 127.0.0.1 (or localhost)
  • Port to connect to MDS (through the SSH tunnel) : 3333
  • MDS Username : admin
  • MDS Password : (come on! I don’t know your password)

Test the connection:

Can you see “Successfully made the MySQL connection“. ?

If the answer is no, please check that you SSH tunnel is still enable, the host Bastion could have closed the connection.
Also check your MDS credentials and host and port.

You can close the form. Your connection is now available in MySQL Workbench.

You can find it in the connection main page or go to Database / Connect to database

And in Stored Connection drop list select your MDS connection

Then click OK.

We are now connected to our MySQL Database Service instance \o/
The server version is here 8.0.26-cloud MySQL Enterprise.

And as expected we can see and query the data we download during Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump.

Well, that’s all for today!
In this episode, we have seen how to securely connect to a MySQL Database Service instance using MySQL Workbench through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

Connect to MySQL Database Service Using OCI Cloud Shell

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

2

Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell

September 21, 2021
Tags: , ,

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 eight 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.

MySQL Database Service

Episode 8 – Connect to MySQL Database Service Using MySQL Shell

In the previous episode we’ve seen how to use an OCI Bastion session in order to provide a restricted and time-limited access to administer our MySQL instance.
One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using the fantastic MySQL Shell – an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server.

MySQL Shell

MySQL Shell is an advanced client & code editor for MySQL. In addition to the provided SQL functionality, it provides scripting capabilities for JavaScript and Python.

MySQL Shell includes utilities for working with MySQL, among others:

  • Instance & schema dump utilities support the export of all schemas or a selected schema from an on-premise MySQL instance into an OCI Object Storage bucket or a set of local files
  • Dump loading utility supports the import into a MySQL DB System or a MySQL Server instance of schemas or tables dumped using MySQL Shell’s Dump Utility

The complete list is available in the documentation: MySQL Shell utilities.

You’ll also find practical resources on my blog.

Create a SSH port forwarding session

In the previous episode – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – we created a Bastion, a SSH port forwarding (SSH tunneling) session and generated SSH key pair.


We are now going to use these in order to connect to MDS with MySQL Shell.

If your session is not valid anymore, you must create a new one (see Create a session paragraph from Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session).

Please note that you need a SSH client on your computer. If you are using a GNU Linux, MacOS, Windows 10 system you are good to go.

If not… all is not lost 🙂
Please read episode 10 : Connect to MySQL Database Service Using OCI Cloud Shell.

In the console menu, go to Identity & Security / Bastion, check that you are in the right compartment then click on the Bastion we previously created (BastionMDS in this example).

You are now seeing the session we already created (if not create a new one).

We now must get the SSH port forwarding command generated by OCI.

To do so, go to the right and click on the action menu (3 vertical dots on the right). A contextual menu is showing up then click on copy SSH command.

Paste this command in your shell prompt.

The SSH command looks like :

Replace <privateKey> with the path to the private key we downloaded in the previous episode – Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session

Replace <localPort> with a free port on the local machine from which you want to connect to the bastion (ie your computer/laptop).

Example:

Then run the command…

If you hit the following error: WARNING: UNPROTECTED PRIVATE KEY FILE!

Don’t worry 🙂
Like stated in the error message, the private key must be protected. A chmod 600 (or equivalent, it depends on your OS) will fix that issue.

Then run the command again:

No error anymore and no prompt.
But do not close the window because it will close the SSH tunnel (thus you will need to run the command again).

Alternatively, you can add an ampersand (&) at the end of the command.

Connect to MDS using MySQL Shell

Now it is the time to connect to our MySQL instance through the SSH tunnel using MySQL Shell.

The information needed are:

  • MySQL client is here MySQL Shell : mysqlsh
  • MySQL Database Service user you’ve created : admin
  • IP to connect to MDS (through the SSH tunnel) : localhost
  • Port to connect to MDS (through the SSH tunnel) : 3333
  • Your MDS password

We are now connected to our MySQL Database Service instance \o/
The server version is here 8.0.26-cloud MySQL Enterprise

By default, MySQL Shell is in JavaScript mode. We can easily switch to SQL mode with the command \sql. And run any valid SQL command

Another way to do that would be to initiate the connection in SQL mode instead of JS by providing the – – sql option when calling mysqlsh:

And as expected we can see and query the data we download during Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump.

Well, that’s all for today!
In this episode, we have seen how to securely connect to a MySQL Database Service instance using MySQL Shell through a SSH tunnel (port forwarding session) using the OCI Bastion service.

Next episode is:

Connect to MySQL Database Service Using MySQL Workbench

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

3

Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session

September 14, 2021
Tags: , ,

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 seventh 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.

MySQL Database Service

Episode 7 – Use a Bastion SSH port forwarding session

In the previous episode we’ve seen how to allow traffic from the VCN to the MySQL Database Service instance on ports 3306 & 33060.
One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll discover and use the OCI Bastion service to provide a restricted and time-limited access to administer our MySQL instance.

Bastion

Oracle Cloud Infrastructure Bastion provides restricted and time-limited access to target resources that don’t have public endpoints.

Bastions let authorized users connect from specific IP addresses to target resources using Secure Shell (SSH) sessions. When connected, users can interact with the target resource by using any software or protocol supported by SSH. 

So, Bastions are logical entities that provide secured, public access to target resources in the cloud that you cannot otherwise reach from the internet.
Bastions reside in a public subnet and establish the network infrastructure needed to connect a user to a target resource in a private subnet

Another important concept is Bastion sessions.
Bastion sessions let authorized users in possession of the private key in an SSH key pair connect to a target resource for a predetermined amount of time.
You provide the public key in the SSH key pair at the time you create the session, and then supply the private key when you connect.
In addition to presenting the private key, an authorized user must also attempt the SSH connection to the target resource from an IP address within the range allowed by the bastion’s client CIDR block allowlist.

There are 2 kind of session types, managed ssh session and ssh port forwarding session. In this article, we’re going to use only the ssh port forwarding session.
Port forwarding (also known as SSH tunneling) creates a secure connection between a specific port on the client machine and a specific port on the target resource.
Using this connection you can relay other protocols.

Note
Your OCI user must be part of a group that has the right privileges in order to use all Bastion features.
At least the following rules:
Allow group SecurityAdmins to manage bastion-family in tenancy
Allow group SecurityAdmins to manage virtual-network-family in tenancy
Allow group SecurityAdmins to read instance-family in tenancy
Allow group SecurityAdmins to read instance-agent-plugins in tenancy
Allow group SecurityAdmins to inspect work-requests in tenancy
Please see: Bastion IAM Policies and Required IAM policy to managing Bastions

Create a bastion

Go to the OCI console, in the menu, go to: Identity & Security / Bastion

Check that you are in the right compartment and click Create Bastion

You should provide:

  • a bastion name – BastionMDS
  • the target VCN, in the right compartment – Demo_VCN
  • the target subnet, the private one – Private Subnet-Demo_VCN
  • the CIDR block allowlist ie the IP from where you want to provide the secure access (e.g. 11.0.0.0/24, <your IP>/32, …). You can have multiple CIDR blocks.
  • You can also choose the maximum session ttl (max 3 hours, and you can redefine it per session later) if you click on Show Advanced Options
  • Then click on Create Bastion

Create a session

Now it is the time to create a Bastion SSH port forwarding session.

Click on your brand new Bastion (or go to the OCI console and in the menu, go to: Identity & Security / Bastion and click on your Bastion).

Click on Create Session, a new form will show up.

You should provide:

Like we mentioned earlier, Bastion sessions let authorized users in possession of the private key in an SSH key pair connect to a target resource. You provide the public key in the SSH key pair at the time you create the session, and then supply the private key when you connect.

If you do not already have a SSH key pair (or want to use another one) select Generate SSH Key pair.
Download the private key so that you can connect to the instance using SSH.

Be careful because,

It will not be shown again!

You can also download the public key if needed.
Please note that you will need to modify the private key file permissions.

Example:

If you want to setup the session time-to-live click on Show Advanced Options

Then click Create Session:

Our Bastion session is now created and active.

Well, that’s all for today!
In this episode, we have seen how to use an OCI Bastion session in order to provide a restricted and time-limited access to administer our MySQL instance.

Next episode is:

Connect to MySQL Database Service Using MySQL Shell

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

3

Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List

September 7, 2021
Tags: , ,

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 sixth 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.

MySQL Database Service

Episode 6 – Update the Private Subnet Security List

In the previous episode we’ve seen how to create a MySQL DB system from a MySQL Shell dump stored into an Oracle Cloud Infrastructure object storage bucket. One more step to our Discovering MySQL Database Service journey.

In this episode, we’ll introduce the concept of Security Lists a virtual firewall to control traffic at the network packet level.

Security Lists

A security list acts as a virtual firewall for an instance, with ingress and egress rules that specify the types of traffic allowed in and out.
Each security list is enforced at the VNIC level. However, you configure your security lists at the subnet level, which means that all VNICs in a given subnet are subject to the same set of security lists.
The security lists apply to a given VNIC whether it’s communicating with another instance in the VCN or a host outside the VCN.

The default security list comes with an initial set of stateful rules, which should in most cases be changed to only allow inbound traffic from authorized subnets relevant to the region that homes that VCN or subnet.

Note
Your OCI user must be part of a group that has the right privileges in order to work with Security Lists.
At least the following rules:
Allow group SecListAdmins to manage security-lists in tenancy
Allow group SecListAdmins to manage vcns in tenancy

Please see: Working with Security Lists

Update the Security List

Go to the OCI console, in the menu, go to: Networking / Virtual Cloud Networks
And select (click on) your VCN ( Demo_VCN in this example), and please check that you are in the right compartment.

Select (click on) the private subnet (Private Subnet-Demo_VCN in this example):

Select the Security List for the Private Subnet (Security List for Private Subnet-Demo_VCN in this example):

Click on Add Ingress Rules:

Now we are adding our ingress rules in order to be able to connect to the MDS instance from the VCN on ports 3306 (MySQL classic protocol) and 33060 (MySQL X protocol).

The information to provide are (please adapt to your context):
+ Source type: CIDR
+ Source CIDR: 10.0.0.0/16
+ IP protocol: TCP
+ Source port range: All
+ Destination port range: 3306,33060

Then click on Add Ingress Rules.

You should end up with something like:

Well, that’s all for today!
In this episode, we have seen how to allow traffic from the VCN to the MySQL Database Service instance on ports 3306 & 33060.
We are now able to connect to our MySQL instance…

Next episode is:

Use a Bastion SSH port forwarding session

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

2

Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

August 31, 2021
Tags: , ,

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.

MySQL Database Service

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:

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

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

9