Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these “intelligent” systems can produce new, human-quality content across a multitude of domains.
Oracle’s HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.
HeatWave GenAI Content Generation
The key benefits of such architecture are:
Effortless LLM Integration: Simple SQL queries unlock AI power.
Seamless Database Integration: No complex external connections.
Simplified Architecture: Easy management and scalability.
Scalable Performance: Handles large datasets and high traffic.
Always Up-to-Date Data: In-database LLMs ensure data freshness.
Generate text-based content using HeatWave GenAI is a very simple 4 steps process:
Load the LLM in HeatWave memory
Define your prompt in natural language
Inference setting
Generate text-based content using HeatWave GenAI
Load the LLM in HeatWave memory
Use the ML_MODEL_LOAD stored procedure: CALL sys.ML_MODEL_LOAD(‘<LLM>’, NULL); The model needs to be loaded only once before generating text. It remains accessible within HeatWave as long as the service is running. This means you don’t have to reload the model for each text generation.
At the time of writing (August 2024) the available models are Mistral 7B (mistral-7b-instruct-v1) and Llama 2 (llama2-7b-v1).
-- Load Mistral 7B model
mysql>
CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);
Define your prompt in natural language
Like with you prefered programming language, it is more convenient to use a session variable: SET @userPrompt=”My prompt”;
mysql>
SET @userPrompt="Explain what is Generative Artificial intelligence in one sentence";
or whatever your needs:
SET @userPrompt="How to connect to MySQL using Python 3?";
SET @userPrompt="Write a short email to schedule a meeting.";
...
As you likely know, the quality of the prompt significantly impacts the generated text.
Inference setting
Here, you select the desired task, choose the appropriate model, and fine-tune parameters to influence the output.
For simplicity, this example uses default parameters. A more complex example is provided below.
Generate text-based content using HeatWave GenAI
Last step is to pass the user prompt to the LLM using the ML_GENERATE function. The second parameter of this function is a JSON object that allows you to specify the task, the LLM, and some tuning options like the temperature.
The output in a JSON that is not really nice to read. But SQL is very powerful and you can easily improve the output using JSON_TABLE function.
mysql>
SELECT GenContent AS GenContent FROM ( SELECT sys.ML_GENERATE(@userPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( GenContent text PATH "$.text") ) AS jt \G
HeatWave GenAI Content Generation
You can influence LLM outputs by adjusting parameters like temperature, number of tokens, stop sequences, top_k, top_p, etc…
Text generation options include:
task
model_id
temperature
max_tokens
top_k
top_p
repeat_penalty
stop_sequences
token_likelihoods
echo
logprobs
stream
context
Below is a quick example:
mysql>
-- Define your prompt in natural language
SET @userPrompt="Explain what is Generative Artificial intelligence in one sentence";
-- Inference setting
SET @inferenceSetup = '{"task": "generation", "temperature": 3, "repeat_penalty": 1, "top_k": 1, "max_tokens": 500, "model_id": "mistral-7b-instruct-v1"}';
-- Generate text-based content
SELECT GenContent AS GenContent FROM ( SELECT sys.ML_GENERATE(@userPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( GenContent text PATH "$.text") ) AS jt \G
*************************** 1. row ***************************
GenContent: Generative Artificial Intelligence (AI) refers to a subset of AI that involves the creation of new, original content, such as images, music, or text, through the use of algorithms and machine learning models that can learn from and generate data.
HeatWave GenAI Content Generation with fine tuning
And that’s all! As you can see, it’s very simple and easy with HeatWave GenAI to create some exciting GenAI based applications like:
Enhancing Customer Experience
Intelligent Chatbots: Provide real-time, personalized customer support, answering queries, and resolving issues efficiently.
Content Generation: Create engaging blog articles, product descriptions, and social media posts at scale.
Personalized Recommendations: Offer tailored product suggestions based on customer preferences and behavior.
Social Media Management: Generate engaging content, schedule posts, and monitor performance.
Driving Business Insights
Text Classification: Categorize vast amounts of text data for efficient analysis and decision-making.
Market Research: Analyze customer sentiment, identify trends, and gain competitive insights.
Generative AI is a game-changer, offering endless possibilities to improve efficiency, enhance customer experiences, and drive business growth.
HeatWave GenAI: Your AI-Powered Content Creation Partner demo
Moving forward
While the examples provided offer a glimpse of generative AI’s potential, it’s important to note that significant improvements can be achieved through advanced prompt engineering techniques. Exploring these techniques in depth is beyond the scope of this article.
In upcoming articles, we’ll delve deeper into HeatWave GenAI’s capabilities, showcasing its application in text translation and Retrieval Augmented Generation (RAG).
MySQL Replication exist for centuries… OK, I’m exaggerating a little bit 🙂 but this feature is as much appreciated as it is useful, because is helping DBAs to easily build scalable and redundant architectures:
MySQL 3.23.15 and up features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves
But what does replication do? Quoting the documentation again (the most recent this time)
Replication enables data from one MySQL database server (the source) to be copied to one or more MySQL database servers (the replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source.
MySQL 8.4 Reference Manual
In this article we will see how to replicate data from HeatWave MySQL to a on-premise standalone MySQL Instance.
Source is HeatWave MySQL 8.4.1
IP: 137.235.181.51
Port: 3306
Replica is MySQL 8.4.1
IP: 88.65.229.255
Port: 3306
Replication user
‘rplUser’@’88.65.229.%’
We will use 3 main components:
HeatWave MySQL as a source
The asynchronous replication capability of MySQL
A MySQL instance as a read replica
HeatWave MySQL
HeatWave is a fully-managed database service, powered by the integrated in-memory query accelerator. It is the only cloud-native database service that combines transactions, analytics, machine learning and generative AI services into HeatWave, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication. It also includes HeatWave Lakehouse which allows users to query data stored in object storage in a variety of file formats. HeatWave is developed, managed, and supported by the MySQL team in Oracle.
HeatWave MySQL accelerate MySQL query performance by orders of magnitude and get real-time analytics on your transactional data. HeatWave MySQL is built on MySQL Enterprise Edition.
The HeatWave MySQL endpoints are not directly accessible from the internet (i.e. private IP). That said, there are different possibilities to connect such as a compute instance, VPN connection, Bastion session, ornetwork load balancer. In this article I’ll use the latter, which is present on a public subnet, enables you to connect to HeatWave MySQL over the internet. However please note that it is not recommended, to make a database accessible over the internet, because it is a security risk. You must restrict the authorized public IP addresses to a single IP address or a small range of IP addresses, and use in-transit encryption. It is recommended to use a VPN connection. You are warned!
I assuming your HeatWave MySQL instance is already created. If not, you can use the Console or the command-line interface to create an instance. Using the console is pretty straightforward, you can find the details in the OCI documentation.
Since MySQL replication is a familiar concept, widely covered over the years, let’s get right to the practical details that will help you set up your replication efficiently. You’ll find all the juicy details in the MySQL 8.4 Reference Manual and some extra here and here.
On the source, create the replication user then grant that user the REPLICATION SLAVE privilege. If you want to ensure that the source only accepts replicas that connect using encrypted connections (and btw you really want that), use the REQUIRE SSL option
-- On the Source
-- Create the replication user
CREATE USER 'rplUser'@'88.65.229.%' IDENTIFIED BY 'Q{P6@EH$L!YFje^9' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'rplUser'@'88.65.229.%';
If the HeatWave MySQL instance (the source) contains existing data it is necessary to copy this data to the replica. There are different ways to dump the data from the source database, the recommended way in that context is to use MySQL Shell dump and load utilities.
You will most likely need dumpInstance and the relevant options are dependent of your context, the most important is usually compatibility. MySQL Shell allows you to store your backup into an object store bucket and load from there, this is very convenient. For this article, the size of my dataset allows me to store it locally, and the only relevant option is dryRun. More details in the documentation: Instance Dump Utility, Schema Dump Utility, and Table Dump Utility.
$ mysqlsh --js admin@137.235.181.51
JS> util.dumpInstance("/backup/20240714", { dryRun: true })
dryRun enabled, no locks will be acquired and no files will be created.
...
If the result is satisfactory, then disable dry run:
JS> util.dumpInstance("/backup/20240714")
...
For your record, below an example of a dump that is stored in an object Storage bucket named MyBck_07 on Oracle Cloud Infrastructure:
-- On the Replica
\sql SET GLOBAL local_infile=1;
util.loadDump("/backup/20240714", {updateGtidSet:"replace", skipBinlog:true})
\sql SET GLOBAL local_infile=0;
All the option details are available in the documentation. Please note that you can test you command with the dryRun option.
Also for your record, if you want to restore a dump for a bucket, your command will look like:
On the source, you can check the status of connected replicas:
-- Check the status of connected replicas
SQL > SHOW PROCESSLIST \G
*************************** 6. row ***************************
Id: 547
User: rplUser
Host: nlb-mhs-paris-dba.sub03221692542.vcnparisdba.oraclevcn.com:1234
db: NULL
Command: Binlog Dump GTID
Time: 428
State: Source has sent all binlog to replica; waiting for more updates
Info: NULL
Basic information about the replicas that were started with the --report-host option and are connected to the source:
Other useful commands on the source, using performance_schema are:
SQL > SELECT
SERVICE_STATE,
HOST,
USER,
PORT
FROM performance_schema.replication_connection_configuration
INNER JOIN performance_schema.replication_applier_status
USING (CHANNEL_NAME) \G
*************************** 1. row ***************************
SERVICE_STATE: ON
HOST: 137.235.181.51
USER: rplUser
PORT: 3306
SELECT
concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
conn_status.service_state AS io_state,
applier_status.service_state AS sql_state,
format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0",
abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,
format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,
format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,
format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,
conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction
FROM
performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name
\G
*************************** 1. row ***************************
channel: (1)
io_state: ON
sql_state: ON
latency: 0 ps
transport_time: 0 ps
time_to_relay_log: 0 ps
apply_time: 0 ps
last_queued_transaction:
last_applied_transaction:
...
Peroration
This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer. While a Network Load Balancer can be used for the connection, a VPN is recommended for better security.
The only MySQL service 100% developed, managed and supported by the MySQL Team.
In short:
MySQLHeatWave 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.
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.
Finally, you might need to install some packages. ex: (pipinstall 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
All HeatWave ML operations are initiated by running CALL or SELECT statements, which can be easily integrated into your applications.
HeatWave ML routines include:
ML_TRAIN: Trains a machine learning model for a given training dataset.
ML_PREDICT_ROW: Makes predictions for one or more rows of 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.
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:
SHOW GLOBAL STATUS LIKE 'rapid_ml_status';
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 🙂 :
-- Tables structure
DESCRIBE iris_ML.iris; -- Reference table (production table)
DESCRIBE iris_ML.iris_train; -- Table that contains the training dataset
DESCRIBE iris_ML.iris_test; -- Test dataset table
DESCRIBE iris_ML.iris_validate; -- Validation dataset table
-- Number of rows
SELECT count(*) FROM iris_ML.iris;
SELECT count(*) FROM iris_ML.iris_train;
SELECT count(*) FROM iris_ML.iris_test;
SELECT count(*) FROM iris_ML.iris_validate;
irissimulate 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).
-- Data sample
SELECT * FROM iris_ML.iris LIMIT 10;
-- Class distribution
SELECT class, count(*) FROM iris_ML.iris GROUP BY class;
-- Summary Statistics
SELECT MIN(sepal_length), MAX(sepal_length), ROUND(AVG(sepal_length), 2), ROUND(STD(sepal_length), 2) FROM iris_ML.iris;
SELECT MIN(sepal_width), MAX(sepal_width), ROUND(AVG(sepal_width), 2), ROUND(STD(sepal_width), 2) FROM iris_ML.iris;
SELECT MIN(petal_length), MAX(petal_length), ROUND(AVG(petal_length), 2), ROUND(STD(petal_length), 2) FROM iris_ML.iris;
SELECT MIN(petal_width), MAX(petal_width), ROUND(AVG(petal_width), 2), ROUND(STD(petal_width), 2) FROM iris_ML.iris;
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:
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)
%python.ipython
## Edit python interpreter on Zeppelin - zeppelin.python: python3 instead of python
## Update host, database, user and password (from you're MySQL HeatWave)
import mysql.connector as connection
import pandas as pd
import seaborn as sns
sns.set_palette('husl')
%matplotlib inline
import matplotlib.pyplot as plt
try:
mydb = connection.connect(host="10.0.1.2", database='iris_ML', user="<user>", passwd="<password>", port=3306)
query = "SELECT * FROM iris;"
data = pd.read_sql(query,mydb)
mydb.close() #close the connection
except Exception as e:
my_conn.close()
print(str(e))
data.head()
data.info()
data.describe()
data['class'].value_counts()
g = sns.pairplot(data, hue='class', markers='*')
plt.show()
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.
# Train the model using ML_TRAIN
CALL sys.ML_TRAIN('iris_ML.iris_train', 'class', JSON_OBJECT('task', 'classification'), @iris_model);
You can show the current model, selecting the session variable @iris_model:
SELECT @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:
# Model information
SELECT model_id, model_handle, model_owner, target_column_name, train_table_name, model_type, task, model_object_size FROM ML_SCHEMA_admin.MODEL_CATALOG;
You can take the last created model using the following query:
SELECT model_handle FROM ML_SCHEMA_admin.MODEL_CATALOG ORDER BY model_id DESC LIMIT 1 INTO @iris_model;
When the model is created (using ML_TRAIN) you should load it into HeatWave ML (ML_MODEL_LOAD):
CALL sys.ML_MODEL_LOAD(@iris_model, NULL);
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.
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
ML_PREDICT_TABLE generates predictions for an entire table and saves the results to an output table.
# Generate predictions for a table
-- CALL sys.ML_MODEL_LOAD(@iris_model, NULL); -- Uncomment if the model is not yet loaded
DROP TABLE IF EXISTS iris_ML.iris_predictions; -- Useful if a table prediction was already generated
CALL sys.ML_PREDICT_TABLE('iris_ML.iris_test', @iris_model, 'iris_ML.iris_predictions');
A new table called iris_predictions is created. You can display Its first 5 rows with:
SELECT * FROM iris_ML.iris_predictions LIMIT 5;
Table Explanation
ML_EXPLAIN_TABLE explains predictions for an entire table and saves results to an output table.
# Prediction explanation for a table
-- CALL sys.ML_MODEL_LOAD(@iris_model, NULL); -- Uncomment if the model is not yet loaded
DROP TABLE IF EXISTS iris_ML.iris_explanations; -- Usueful if a table explanation was already done
CALL sys.ML_EXPLAIN_TABLE('iris_ML.iris_test', @iris_model, 'iris_ML.iris_explanations');
A new table called iris_explanations is created. You can display Its first 5 rows with:
SELECT * FROM iris_ML.iris_explanations LIMIT 5;
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.
-- This example uses the accuracy: Computes the fraction of labels a model predicts correctly
CALL sys.ML_SCORE('iris_ML.iris_validate', 'class', @iris_model, 'accuracy', @accuracy_score);
SELECT @accuracy_score;
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!
MySQL – The 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.
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.
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.
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04.4 LTS
Release: 20.04
Codename: focal
$ java -version
openjdk version "17.0.3" 2022-04-19
OpenJDK Runtime Environment (build 17.0.3+7-Ubuntu-0ubuntu0.20.04.1)
OpenJDK 64-Bit Server VM (build 17.0.3+7-Ubuntu-0ubuntu0.20.04.1, mixed mode, sharing)
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!
tar xvf zeppelin-0.10.1-bin-all.tgz
mv zeppelin-0.10.1-bin-all/ zeppelin
cd zeppelin
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:
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.
~/zeppelin$ cp conf/zeppelin-site.xml.template conf/zeppelin-site.xml
~/zeppelin$ vim conf/zeppelin-site.xml
...
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/
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:
SHOW VARIABLES LIKE 'rapid_bootstrap';
SHOW STATUS WHERE Variable_name IN ('rapid_cluster_status', 'rapid_ml_status', 'rapid_cluster_ready_number', 'rapid_load_progress');
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.
-- Number of Tickets > $500.00, Grouped By Price
SELECT booking.price, count(*)
FROM airportdb.booking
WHERE booking.price > 500
GROUP BY booking.price
ORDER BY booking.price
LIMIT 10;
-- Average Age of Passengers By Country, Per Airline
SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) as avg_age, count(*) as nb_people
FROM airportdb.booking, airportdb.flight, airportdb.airline, airportdb.passengerdetails
WHERE booking.flight_id=flight.flight_id AND
airline.airline_id=flight.airline_id AND
booking.passenger_id=passengerdetails.passenger_id AND
country IN ("SWITZERLAND", "FRANCE", "ITALY")
GROUP BY airline.airlinename
ORDER BY airline.airlinename, avg_age
LIMIT 10;
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 🙂
MySQL – The 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…
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.
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);
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):
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)
$ superset db upgrade
$ superset fab create-admin
# If you want to load the embeded examples
# superset load_examples
# Create default roles and permissions
$ superset init
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
$ superset run -h 0.0.0.0 -p 8088 --with-threads --reload
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.
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.
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.
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).
Unfortunately MariaDB does not have the sys schema… We cannot use this very elegant query:
-- MySQL 5.7 / 8.0+
SELECT sys.format_bytes(sum(data_length)) DATA,
sys.format_bytes(sum(index_length)) INDEXES,
sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER BY data_length + index_length
;
So back in the time and let’s use the information_schema instead and the following ugly query:
-- MySQL (old version) / MariaDB --
SQL>
SELECT
CONCAT( SUM( ROUND( DATA_LENGTH / (1024 / 1024) ,2)), 'MB' ) AS Data,
CONCAT( SUM( ROUND( INDEX_LENGTH / (1024 / 1024) ,2)), 'MB' ) AS Indexes,
CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ,2)), 'MB' ) AS 'Total Size'
FROM information_schema.TABLES ORDER BY DATA_LENGTH + INDEX_LENGTH
;
+-----------------+-----------------+------------+
| Data | Indexes | Total Size |
+-----------------+-----------------+------------+
| 3044154215.00MB | 5688978432.00MB | 8328.61MB |
+-----------------+-----------------+------------+
Approximately 8GB of data.
What about the storage engines?
Again because of lack of sys schema we cannot use this pretty query:
-- MySQL 5.7 / 8.0+
SELECT count(*) as '# TABLES', sys.format_bytes(sum(data_length)) DATA,
sys.format_bytes(sum(index_length)) INDEXES,
sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE',
engine `ENGINE` FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')
GROUP BY engine
ORDER BY engine;
So let’s back in the time and use instead:
-- MySQL (old version) / MariaDB --
SQL>
SELECT
count(*) as '# TABLES',
CONCAT( SUM( ROUND( DATA_LENGTH / (1024 / 1024) ,2)), 'MB' ) AS Data,
CONCAT( SUM( ROUND( INDEX_LENGTH / (1024 / 1024) ,2)), 'MB' ) AS Indexes,
CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ,2)), 'MB' ) AS 'Total Size',
engine `ENGINE` FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')
GROUP BY engine
ORDER BY engine
;
+----------+-----------------+-----------------+------------+--------+
| # TABLES | Data | Indexes | Total Size | ENGINE |
+----------+-----------------+-----------------+------------+--------+
| 2 | NULL | NULL | NULL | NULL |
| 26 | 3043442688.00MB | 5688705024.00MB | 8327.66MB | InnoDB |
+----------+-----------------+-----------------+------------+--------+
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:
-- Find tables without PK
SELECT tables.table_schema , tables.table_name , tables.engine
FROM information_schema.tables LEFT JOIN (
SELECT table_schema , table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name
HAVING SUM(
case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
ON tables.table_schema = puks.table_schema
AND tables.table_name = puks.table_name
WHERE puks.table_name IS null
AND tables.table_type = 'BASE TABLE'
AND tables.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');
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:
JS>
\sql SET GLOBAL local_infile=1;
\sql ALTER INSTANCE DISABLE INNODB REDO_LOG;
util.loadDump("/restore/dumpMariadb1", {threads: 8, ignoreVersion: true , skipBinlog: true, loadUsers: false})
\sql ALTER INSTANCE ENABLE INNODB REDO_LOG;
\sql SET GLOBAL local_infile=0;
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.
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.
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.
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.
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.
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.
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
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:
$ curl ifconfig.me
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.
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
$ chmod 600 myPrivateKey.key
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.
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
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
