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.
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.
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:
the Session Type: SSH port forwarding session
a session name: a meaningful one 🙂
how to connect to the target host : we’ll use IP Address.
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:
ex: chmod 600 ssh-key-YYYY-MM-DD.key
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.
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.
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 privatesubnet (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…
MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.
This is the fifth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.
Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free by starting your 30-day trial.
Episode 5 – Create a MySQL DB system from a MySQL Shell dump
In the previous episode we’ve seen how to export data from a MySQL instance to an Oracle Cloud Infrastructure bucket using the awesome MySQL Shell. One more step to our Discovering MySQL Database Service journey.
In this episode, we’ll see how these data can now easily be imported into a MySQL Database Service instance.
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.
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.
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.
MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.
This is the fourth 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 4 – Dump your MySQL data into an Object Storage bucket
In the previous episode we’ve created our Virtual Cloud Network, that provides you with complete control over your cloud networking environment that we will create in this Discovering MySQL Database Service journey.
In this episode, we’ll see what is OCI Object Storage and how to export data from it to a MySQL instance using MySQL Shell. In fact, in the process of creating a MySQL DB system, this step is optional. But it is especially useful if you want to create a MDS instance with data from a MySQL server on premise or in any public/private cloud.
Because it is a plausible scenario, let’s see how to do that!
The workflow
The workflow is quite simple. We will export all or part of our MySQL instance data using MySQL Shell into an Oracle Cloud Infrastructure Object Storage. Then we will import the data from the bucket to a MySQL Database Service instance. Today we are going to see only the first part (red rectangle in the picture below).
The Oracle Cloud Infrastructure Object Storage service is an internet-scale, high-performance storage platform that offers reliable and cost-efficient data durability. The Object Storage service can store an unlimited amount of unstructured data of any content type, including analytic data and rich content, like images and videos.
With Object Storage, you can safely and securely store or retrieve data directly from the internet or from within the cloud platform. You can access data from anywhere inside or outside the context of the Oracle Cloud Infrastructure, as long you have internet connectivity and can access one of the Object Storage endpoints.
Create an Object Storage Bucket
Here as well, many ways to do that. The more convenient way is probably to use the OCI console.
In the menu, go to: Storage / Object Storage & Archive Storage / Buckets
Select the right compartment (left of the screen – DBA in this example) then press Create Bucket.
The bucket must have a name (Sakila_dump in this example – if you want to test with a sample database, Sakila database is available here).
The Default Storage Tier must be Standard – the default value.
Then you good to go!
Now let’s export the data to this fresh new created object storage bucket.
Export data to the bucket
You already get it, were going to use this amazing tool called MySQL Shell. But before we need to 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.
Awesome! Now we have all the parts to export our MySQL data with the MySQL Shell dump utilities.
MySQL Shell
MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to the mysql text client, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. X DevAPI enables you to work with both relational and document data. AdminAPI enables you to work with InnoDB Cluster.
In this article we are mainly interested in the utility part of MySQL Shell. MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. The util global object provides many functions like checkForServerUpgrade(), importJSON(), exportTable(), importTable(), loadDump(), util.dumpTables(), util.dumpInstance(), util.dumpSchemas(), …
In order to create a MySQL Database Service instance automatically populated with your data, you will use either util.dumpInstance() or util.dumpSchemas().
I’m going to use the later – dumpSchema() – but please keep in mind that the logic is the same for dumpInstance().
There are many relevant options. I will briefly describe them below (please RTFM for more details).
ocimds: Setting this option to true enables checks and modifications for compatibility with MySQL Database Service.
osBucketName: The name of the Oracle Cloud Infrastructure Object Storage bucket to which the dump is to be written.
osNamespace: The Oracle Cloud Infrastructure namespace where the Object Storage bucket named by osBucketName is located.
compatibility: Apply the specified requirements for compatibility with MySQL Database Service for all tables in the dump output, altering the dump files as necessary.
threads: The number of parallel threads to use to dump chunks of data from the MySQL instance. Each thread has its own connection to the MySQL instance.
dryRun: Display information about what would be dumped with the specified set of options, and about the results of MySQL Database Service compatibility checks (if the ocimds option is specified), but do not proceed with the dump. Setting this option enables you to list out all of the compatibility issues before starting the dump.
ociParManifest: Setting this option to true generates a pre-authenticated request for read access (an Object Read PAR) for every item in the dump, and a manifest file listing all the pre-authenticated request URLs. The pre-authenticated requests expire after a week by default, which you can change using the ociParExpireTime option.
ociParExpireTime: The expiry time for the pre-authenticated request URLs that are generated when the ociParManifest option is set to true. The default is the current time plus one week, in UTC format.
If your config file is not in the default location, you will also need ociConfigFile.
ociConfigFile: An Oracle Cloud Infrastructure CLI configuration file that contains the profile to use for the connection, instead of the one in the default location ~/.oci/config.
osBucketName is the name of the bucket we created previously – Sakila_dump in this article.
osNamespace is available in the OCI console. Open the menu and go to Storage / Object Storage & Archive Storage Click on the Bucket name (if you don’t see your bucket , check if you are in the right compartment). Namespace field is what your are looking for.
compatibility provides many types of modification. Please read carefully the documentation in order to use the correct setup for your needs. I’m using here: force_innodb, skip_invalid_accounts, strip_definers, strip_restricted_grants, strip_tablespaces.
If you have tables without primary keys (that is usually not a good thing) you can force the dump using the ignore_missing_pks compatibility mode. But you will not be able to use some advanced features like MDS High Availability, nor MDS HeatWave)
Another alternative is to use create_invisible_pks instead of ignore_missing_pks. Please check the documentation for the relevant details.
ociParManifest & ociParExpireTime are very useful in this context. They will allow you to create a Pre-Authenticated Requests (PAR) and setup an expiration time of your choice. Thus you will be able to load your data whenever it suits you.
To create or manage pre-authenticated requests, you need PAR_MANAGE permission to the target bucket.
Note While you only need PAR_MANAGE permission to create a pre-authenticated request, you must also have the appropriate permissions for the access type that you are granting. Details here
Connect to your (on-premise / private or public cloud) MySQL instance using MySQL Shell:
MySQL localhost:3306 ssl JS>
util.dumpSchemas(["sakila"], "", { osBucketName: "Sakila_dump", osNamespace: "azertyuiop", ocimds: true, ociParManifest: true, ociParExpireTime: "2021-08-11T00:20:00.000+02:00", threads: 8, compatibility: ["force_innodb", "skip_invalid_accounts", "strip_definers", "strip_restricted_grants", "strip_tablespaces"]})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.25
... <snip> ...
1 thds dumping - 100% (47.27K rows / ~47.27K rows), 3.01K rows/s, 193.24 KB/s uncompressed, 35.48 KB/s compressed
Duration: 00:00:15s
Schemas dumped: 1
Tables dumped: 16
Uncompressed data size: 3.03 MB
Compressed data size: 557.12 KB
Compression ratio: 5.4
Rows written: 47273
Bytes written: 557.12 KB
Average uncompressed throughput: 193.22 KB/s
Average compressed throughput: 35.48 KB/s
We can now check the bucket content:
Well, that’s all for today! In this episode, we have seen how to export data from a MySQL instance to an Oracle Cloud Infrastructure bucket using this awesome tool called MySQL Shell. These data can now easily be imported in a MySQL Database Service instance.
MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.
This is the third 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.
In the previous episode we’ve created our compartment, the foundation stone, of the architecture that we will build during this Discovering MySQL Database Service journey.
In this episode, we’ll see what is a Virtual Cloud Network (VCN) and how to create one and use it.
Virtual Cloud Network (VCN)
Oracle Virtual Cloud Networks (VCNs) provide customizable and private cloud networks in OCI. Just like a traditional data center network, the VCN provides customers with complete control over their cloud networking environment. This includes assigning private IP address spaces, creating subnets and route tables, and configuring stateful firewalls. For more information please see VCNs and Subnets.
Create a Virtual Cloud Network
If you are a network expert, OCI provides you the tools and the granularity to create your VCN.
if, like me, your are not an expert and/or if you want to quickly create a VCN with all the relevant component, the VCN Wizard is the right feature for you.
Creates a regional public subnet with routing to the internet gateway. Instances in a public subnet may optionally have public IP addresses.
Creates a regional private subnet with routing to the NAT gateway and service gateway (and therefore the Oracle Services Network). Instances in a private subnet cannot have public IP addresses.
Sets up basic security list rules for the two subnets, including SSH access.
Brilliant! This is exactly what we need.
Select Create VCN with Internet Connectivity from the VCN Wizard. It creates a VCN with a public subnet that can be reached from the internet. Also creates a private subnet that can connect to the internet through a NAT gateway, and also privately connect to the Oracle Services Network.
To summarize we will have the following component:
VCN
Public Subnet
Private Subnet
Internet Gateway (IG)
NAT Gateway (NAT)
Service Gateway (SG)
You should provide a name for the VCN – Demo_VCN in this example.
Check that the compartment is the good one, the one we’ve created in the previous episode – DBA in this example.
The VCN and subnet default configuration should be fine.
After clicking on the Next button, you can review your configuration:
Click on Create when you are ready…
Et voilà!
You should end up with something that looks like:
Among others an IPv4 CIDR block (10.0.0.0/16) and 2 subnets (private in 10.0.1.0/24 and public in 10.0.0.0/24)
Well, that’s all for today! In this episode we have created an already configured Virtual Cloud Network for use with MySQL DB Systems.
