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

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

This is the 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).

Thus the needed components are:

• OCI Object Storage
• One of the MySQL Shell dump utilities :
  • util.dumpInstance()
  • util.dumpSchemas()

Object Storage

What is Object Storage?

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.
• tenancy: OCID of your tenancy.
• region: An Oracle Cloud Infrastructure region.

For more details please click here.

As an example, mine looks like:

So how to get these information?

First you can create the directory where to store the config file:

In the OCI console, on the top right (icon) go to Profile / User Settings

On this page, click on API Keys, bottom left:

Click on Add API Key,

Check Generate API key Pair ;
Click on Add API Key;
Download the Private Key ;
Click Add

The Configuration File Preview is generated with almost all the relevant information \o/.

So far we have something like:

Copy and paste it in the config file we’ve previously created.

And don’t forget to update the key_file path (ex: key_file=/home/opc/.oci/oci_api_key.pem)

Brilliant! However, one information is still missing: the compartment OCID.

In the menu of the console, go to: Identity & Security / Compartments
Select the relevant compartment (DBA in this example)

Copy the compartment OCID:

We can now update the config file with the last information.

So your final config file will look like:

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().

As a side note there are several articles on my blog about MySQL Shell dump & load utilities.
I would recommend you to read HeatWave – A MySQL cloud feature to speed up your queries and MySQL SHELL – The new era.

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:

Run the dump command in dry run mode:

If no error then… Go Go Go!!!
(Without dry run)

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.

Next episode is:

Create a MySQL DB system from a MySQL Shell dump

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Overview of Object Storage
• Command Line Interface (CLI)
• MySQL Shell
• MySQL Shell Utilities
• Oracle Cloud Infrastructure Compartments
• MySQL — The world’s most popular open source database

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

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

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

This is the 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.

Episode 3 – Create a Virtual Cloud Network

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.

The VCN wizard will:

• Creates a VCN.
• Creates an internet gateway, NAT gateway, and service gateway for the VCN.
• 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.

Next episode is:

Dump your MySQL data into an Object Storage bucket

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• VCNs and Subnets
• Virtual Networking Quickstart
• MySQL — The world’s most popular open source database

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

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

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

This is the second 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 2 – Create a compartment

In the previous episode we’ve introduced the different components that will be used during this Discovering MySQL Database Service journey.

In this episode, we’ll see what is a compartment, how to create one and use it in order to create a MySQL DB system.

Compartment

A compartment is a collection of related resources that can be accessed only by groups that have been given permission by an administrator in your organization.

Actually, compartment is a powerful Oracle Cloud Infrastructure feature for security that would allow you to organize and isolate your cloud resources.

Learn more with this article and the documentation.

Please note that it is not mandatory to create compartments, it means that all your assets would be in the root compartment (the default) but it is obviously a bad practice 🙂

Create a compartment

Create a compartment is very simple.

Assuming that you already setup your tenancy —Setting Up Your Tenancy — you can use the console menu :

• Go to: Identity & Security and Compartments

• Fill Name and Description fields
• Then push Create Compartment button

Here we go!
The compartment is now created.

Well, that’s all for today!
In this episode, following the best practices, we have created our compartment where we will store our resources. Thus we are able to enforce security isolation and access control.

Next episode is:

Create a Virtual Cloud Network

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• Managing Compartment
• Oracle Cloud Infrastructure Compartments
• MySQL — The world’s most popular open source database

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

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

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

This is the first 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 1 – Introduction

Like any series, in this episode I’m going to give you some context and set up the characters.

Open Source Software

As you may know, Open Source Software has been mainstream in the enterprise for quite some time.
Open Source usage still increases and it also drives innovation (you can read this and this).
This is particularly the case for Open Source Databases, like MySQL obviously.

MySQL

MySQL has been around for a while now, take a look at this funny article.

Most popular database (Jetbrains survey – Stackoverflow survey) means you’ll find experts, experienced DBA, skilled developers, tons of resources… to help you to grow your business.

Popular also means that MySQL is widely used and used in many industries that run various workloads.

MySQL is the ideal database if your use case is:

• Cloud Native Apps
• Move Existing Workloads
• Hybrid Cloud Flexibility
• SaaS Applications

MySQL is the right database for on-premise, cloud and hybrid architectures.

Oracle Cloud Infrastructure (OCI)

Oracle Cloud Infrastructure (OCI) is a deep and broad platform of public cloud services that enables customers to build and run a wide range of applications in a scalable, secure, highly available, and high-performance environment.

Some highlights of this Next-generation cloud infrastructure:

• Security First
• Reduce costs and enhance performance
• Best support for hybrid architectures

More details here

You can freely use Oracle Cloud Infrastructure services – including MySQL Database Service – by trying the Oracle Cloud Free Tier.

What’s included with Oracle Cloud Free Tier?

MySQL Database Service (MDS)

In Short: MDS = OCI + MySQL

Ok, please allow me to elaborate a little more 🙂

MySQL Database Service is a fully managed cloud service. Meaning that low-value tasks (no direct value for the business) are done by the MySQL team at OCI:

It is also worth noting that MDS is:

• 100% Developed by the MySQL team
• 100% Managed by the MySQL team
• 100% Supported by the MySQL Team, 24/7
• 100% Up to date with the latest security fixes
• 100% Built on MySQL Enterprise Edition
• 100% Compatible with on-premises MySQL
• 100% Compatible with Oracle technologies

In terms of features, you will obviously find the common one, High Availability, Replication, Migration tools,…
Learn more…

However it is exclusively in OCI that you will find HeatWave : the only MySQL cloud service with a massively-scalable integrated analytics engine.
In other words, HeatWave is a MySQL cloud service with an integrated, high-performance, in-memory query accelerator that enables customers to run sophisticated analytics directly against their operational MySQL databases, eliminating the need for complex, time-consuming, and expensive data movement and integration with a separate analytics database.
Learn more…

Last but not least, with MDS you’re going to save money!

Components

A DBMS is indeed part of a chain of components. In this series we will deal with different concepts and components.
Below the main ones:

1. Tenancy
2. Region
3. Availability Domain
4. Fault Domain
5. Groups
6. Policy
7. Compartment
8. Virtual Cloud Network (VCN)
9. Security Lists
10. Object Storage
11. MySQL Database Service (MDS)
12. Bastion
13. SSH Client
14. MySQL Shell
15. MySQL Workbench
16. Cloud Shell

Tenancy
A secure and isolated partition within Oracle Cloud Infrastructure (OCI) where you can create, organize, and administer your cloud resources. When you sign up for OCI, a tenancy is created for your company. Tenancy also refers to the root compartment that contains all of your organization’s compartments and other OCI resources.

Region
A collection of availability domains located in a single geographic location.

Availability Domain
One or more isolated, fault-tolerant Oracle data centers that host cloud resources such as instances, volumes, and subnets. A region contains one or more availability domains.

Fault Domain
A logical grouping of hardware and infrastructure within an availability domain. Fault domains isolate resources during hardware failure or unexpected software changes.

Groups
A collection of users who all need a particular type of access to a set of resources or compartment.

Policy
An Identity and Access Management (IAM) document that specifies who has what type of access to your resources. Policy can refer to several types of documents: an individual statement written in the policy language, a collection of statements in a single named “policy” document, and the overall body of policies that your organization uses to control access to resources.

Compartment
A collection of related resources that can be accessed only by groups that have been given permission by an administrator in your organization.

Virtual Cloud Network (VCN)
A virtual version of a traditional network — including CIDRs, subnets, route tables, and gateways — on which your instance runs.

Security Lists
A virtual firewalls for your Compute instances and other kinds of resources.

Object Storage
An internet-scale, high-performance storage platform that offers reliable and cost-efficient data durability.

MySQL Database Service (MDS)
A fully managed database service that lets developers quickly develop and deploy secure, cloud native applications using the world’s most popular open source database.

Bastion
Provide restricted and time-limited secure access to resources that don’t have public endpoints and require strict resource access controls.

SSH Client
Software program which uses the secure shell protocol to connect to a remote computer.

MySQL Shell
An interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server

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

Cloud Shell
A web browser-based terminal accessible from the Oracle Cloud Console. It provides access to a Linux shell, with a pre-authenticated Oracle Cloud Infrastructure CLI, a pre-authenticated Ansible installation, and other useful tools.

In this series of articles, I assume that you already setup your tenancy.
More information: Setting Up Your Tenancy

Well, that’s all for today!
I have introduced you to the different characters of this Discovering MySQL Database Service series.

Not so much technical contents so far, but I swear there will be more next time 🙂

Next episode is:

Create a compartment

Resources

• MySQL Database Service (MDS)
• Documentation of MySQL Database Service
• Oracle Cloud Infrastructure (OCI)
• Oracle Cloud Infrastructure Glossary
• Oracle Cloud Free Tier
• MySQL — The world’s most popular open source database

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

If you have (too) long running select queries it is probably because of lack of relevant indexes, problematic schema that lead to poor queries or inadequate hardware.

That said, sometime even if you doing it right, the query execution time could be too long regarding of what the application or your users expect. It is often true for reporting, real time analytics or BI queries.

At Oracle we have developed HeatWave, that allow you to easily run high performance analytics against your MySQL database.

To be more precise, HeatWave is a massively-scalable integrated analytics engine for MySQL Database Service (MDS), the MySQL DBaaS in Oracle Cloud Infrastructure (OCI).

Some key points of HeatWave:

• Single MySQL database for OLTP & analytics applications
• All existing applications work without any changes
• Extreme performance:
  • 400x faster than MySQL, scales to thousands of cores
  • 1100x faster than Amazon Aurora
  • 2.7x faster than Amazon Redshift

If your application already use a MySQL database that is not MySQL Database Service, you can still use HeatWave, thanks to the Inbound Replication feature.

This is basically what you architecture will look like

Your “problematic” select queries will be running on MDS (with HeatWave plugin enabled).

Create this architecture is pretty simple, this is the topic of this article…

Assumptions
You have an application and a MySQL instance somewhere (on-premise / public | private cloud / on the moon, …), called 10.0.1.9 in this article.
You’ve already created your HeatWave instance, called MDSHW (10.0.1.10) in this article.

Not yet created? Don’t worry it is quite easy : MySQL Database Service with a HeatWave cluster

Last but not least, you want to dramatically improve the response time of your select queries.

You can take advantage of the power of HeatWave, by setup a replication channel between your MySQL instance and a MySQL Database Service with a HeatWave cluster.

For simplicity, in this blog post the MySQL instance is on OCI. However this architecture is also relevant with the DB out of OCI.
In that case you will need a VPN (e.g. Using OpenVPN with MySQL Database Service).

Also for simplicity, I’m using MySQL 8.0. However, the following architecture is also relevant with MySQL 5.7.
To understand how to setup a replication channel from MySQL 5.7 to MySQL Database Service, you can read : Replicate from MySQL 5.7 to MySQL Database Service.

What is the plan?

Assuming we already have the application and a MySQL 8.0 database running, we will:

1. Create a dedicated replication user on the source
2. Create a dump of the MySQL instance
3. Load the dump into MySQL Database Service with HeatWave
4. Create a replication channel on MySQL Database Service with HeatWave
5. Enable a HeatWave cluster

Create a dedicated replication user on the source

Connect to the MySQL source instance (10.0.1.9), using MySQL Shell:

then create the replication user:

with his relevant privileges:

Simple!

Create a dump of the MySQL instance

If you think mysqldump (or mysqlpump) when you heard “dump” then it is time to upgrade your knowledge!!! 🙂

When using MySQL 8.0 or even 5.7, forget these 2 tools and please welcome MySQL Shell utilities and especially its load / dump tools – you’ll thank me later 😉

Please note ocimds & compatibility options.
These are very important in order to be able to load your data into MDS.

Details are available in this great Anastasia‘s article (MySQL SHELL – The new era) and obviously in the documentation.

In this scenario the dump is stored locally. But MySQL Shell also allow you to store your dump into an OCI Object Storage Bucket.
This could be a better alternative if your data set is large.

Super simple!

Load the dump into MySQL Database Service with HeatWave

Now it is the time to load the dump into MySQL Database Service with HeatWave (10.0.1.10).

MySQL Shell loadDump utility is even more amazing than you think!
It allows you to start loading in parallel the dump even if it is not completed yet 🙂

Please note the updateGtidSet option.
Documentation is available here.

Very simple!

Create a replication channel on MySQL Database Service with HeatWave

This step is even easier than the other 🙂 because most of the work will be done using the OCI console.
I already described it in this article and you’ll find all the details in the documentation.

Below the main stages.

The feature we are looking for is named Channel.
You can find it by clicking on “MySQL” then “Channel” and finally push the “Create Channel” button

Then fill the form:

Create in Compartment: Choose the right Compartment

Source Connection – Configure Connection to the MySQL Source
Hostname: 10.0.0.9

Username: rpl
(from the replication user created on the source)

Password: Rpl1234_
(from the replication user created on the source)

SSL Mode: – set when create the replication user –
Required (REQUIRED)
Establish an encrypted connection.

Target – Configure the DB System target
Select a DB System: MDSHW

Finally, push the Create Channel button… et voilà!

Wait for the Channel icon become green (ACTIVE)…

You can confirm the creation of the replication channel with the command SHOW REPLICA STATUS\G or run the following query using the replication channel name (default: replication_channel) :

Enable a HeatWave cluster

The last stage covered in this article is how to enable the HeatWave cluster.

You must choose the number of node (2 minimum), it depends of the size of the data you want to put in HeatWave.
The “Estimate Node Count” feature will help you.

Then click Add HeatWave Cluster button.

What next?

There are some data preparation and finally load the data into the HeatWave cluster.

But that would be for another article 🙂

Obviously all these information are available in the HeatWave User Guide.

Péroraison

We continuously try to push the limits to improve MySQL, adding new features (check The complete list of new features in MySQL 8.0) to allow you to reach your business objectives.

Obviously, this is also true for MySQL Database Service.
HeatWave, the query accelerator is therefore a great example.

And you know what?
There are plenty of good things to come 🙂

Stay tuned!

Oh, one last thing, you can try MySQL Database Service, HeatWave and other Oracle Cloud Infrastructure services for free.
Click here.

References

• MySQL Database Service
  • Getting Started with MySQL Database Service
  • MySQL Database Service – Inbound Replication
  • Migrate from on premise MySQL to MySQL Database Service
  • Using MySQL Workbench with MySQL Database Service
  • Options to Run MySQL Server in OCI (and use MySQL Analytics Service)
• HeatWave
  • HeatWave Quickstart
  • HeatWave User Guide
• Performance comparison of HeatWave with MySQL Database, Amazon Redshift, and Amazon Aurora
• MySQL Database Service—New HeatWave Innovations
• MySQL Benchmarks
• MySQL Shell
  • Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
  • Dump Loading Utility
• Oracle Cloud Infrastructure (OCI)
  • Oracle Cloud Infrastructure Free Tier
  • Overview of Object Storage
• MySQL Replication

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

Thanks for using MySQL!

Up until now we have seen MDS (MySQL Database Service) and MySQL in Azure.
As the Cloud technology keeps moving fast, I thought it would be a good idea to see how to set up a MySQL in Amazon , as a service, that is RDS.

Let’s get started then!

What we need is an Amazon subscription that is really easy to get one and there is also a Free Tier. The AWS Free Tier is available to you for 12 months starting with the date on which you create your AWS account. When your free usage expires or if your application use exceeds the free usage tiers, you simply pay standard, pay-as-you-go service rates.

For the specific session, I had root access. There is also the IAM user. AWS Identity and Access Management (IAM) is an AWS service that helps an administrator securely control access to AWS resources. IAM administrators control who can be authenticated (signed in) and authorized (have permissions) to use Amazon RDS resources. 

Once inside the Amazon Subscription, we are seeing the Management Console:

From the Management Console, we are able to identify the RDS service and choose it:

Once inside the RDS service, we click on the Create Database:

From the Databases provided, we are choosing MySQL and Standard Create, as we would like to set up our configuration.
With Easy Create enabled, you specify only the DB engine type, DB instance size, and DB instance identifier. 
Easy Create uses the default setting for other configuration options.

With Easy Create not enabled, you specify more configuration options when you create a database, including ones for availability, security, backups, and maintenance.

Now, on MySQL versions we have chosen the latest provided until now for Amazon, that is 8.0.21 :

If you noticed, there is also the choice to choose Production , Dev or Free Tier template.
For Production Template, there are default options for High Availability and consistent performance. We have chosen the Free Tier, as this is for educational purposes.

We have also given a Database Identifier, database-1. Each DB instance has a DB instance identifier.
This customer-supplied name uniquely identifies the DB instance when interacting with the Amazon RDS API and AWS CLI commands. The DB instance identifier must be unique for that customer in an AWS Region.

Moving on next are the admin’s credentials, the super user of the database:

The DB instance class determines the computation and memory capacity of an Amazon RDS DB instance. The DB instance class you need depends on your processing power and memory requirements. Apparently, since we are only testing this we have a basic tier:

db.t2 – Instance classes that provide a baseline performance level, with the ability to burst to full CPU usage.

Moving along with the rest of our options:

Amazon RDS provides three storage types: General Purpose SSD (also known as gp2), Provisioned IOPS SSD (also known as io1), and magnetic (also known as standard).

They differ in performance characteristics and price, which means that you can tailor your storage performance and cost to the needs of your database workload. 

You can create MySQL, MariaDB, Oracle, and PostgreSQL RDS DB instances with up to 64 tebibytes (TiB) of storage. You can create SQL Server RDS DB instances with up to 16 TiB of storage.
For this amount of storage, use the Provisioned IOPS SSD and General Purpose SSD storage types.

The following list briefly describes the three storage types:

• General Purpose SSD – General Purpose SSD volumes offer cost-effective storage that is ideal for a broad range of workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. Baseline performance for these volumes is determined by the volume’s size.
• Provisioned IOPS – Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads, particularly database workloads, that require low I/O latency and consistent I/O throughput.
• Magnetic – Amazon RDS also supports magnetic storage for backward compatibility. We recommend that you use General Purpose SSD or Provisioned IOPS for any new storage needs. The maximum amount of storage allowed for DB instances on magnetic storage is less than that of the other storage types.

One the Availability & Durability section, you are seeing the option Multi-AZ deployment. Amazon RDS Multi-AZ deployments provide enhanced availability and durability for database instances, making them a natural fit for production database workloads. When you provision a Multi-AZ database instance, Amazon RDS synchronously replicates your data to a standby instance in a different Availability Zone (AZ).

The next section to check is that on of the Connectivity:

Be certain to choose the correct Virtual private cloud, as it cannot be changed after the database is created and set the proper access (IP-ranges) for your subnets.

Security groups control the access that traffic has in and out of a DB instance. Each VPC security group rule enables a specific source to access a DB instance in a VPC that is associated with that VPC security group.
The source can be a range of addresses, or another VPC security group. By specifying a VPC security group as the source, you allow incoming traffic from all instances (typically application servers) that use the source VPC security group.

Moving on next, we have the Database options, we choose the DB name and we have the default parameter group & the option group. We will talk about these in more detail in a few minutes.

The backups sections is pretty straightforward, we may choose automatic ones and there is a retention period from 7 to 35 days.

We can set a specific window for the backups and we may enable the logs that we wish:

Here, we have chosen the error logs and the slow query log.

And now we are ready to create our database:

Please note that once the MySQL RDS is ready, it is the only time we are able to see the connection details and specifically the password, so we need to take a note of this.

Once our database is ready for use, we will see a notification at the top of the page:

Now, remember the default-group that we saw earlier while setting our MySQL RDS?
Basically, a DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances.

If you create a DB instance without specifying a DB parameter group, the DB instance uses a default DB parameter group. Each default DB parameter group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You can’t modify the parameter settings of a default parameter group.

So, if we try to edit the default parameter group, we will receive the following error:

Therefore, if we wish to edit and configure the parameter values, we will need to create a new parameter group and assign it to our database.

Pretty straightforward, we name our parameter group and add a description if we wish:

And we are able to edit it and save the changes:

To apply it on our MySQL instance, we need to modify and set the corresponding parameter group:

We may also modify our backup options and set a window that we didn’t do while creating the MySQL:

On the Events tab, we may see all the actions that have been performed on our instance:

We are also able to check the Recommendations regarding the performance and our queries if we have the enhanced monitoring is enabled:

And last but not least, on the Actions we may create a replica :

To sum up, this is how to set up a MySQL RDS and the options that we have.

Conclusion

We have seen MySQL PaaS on the top Cloud Providers so far and there is definitely much more to see on that department, so stay tuned!

I don’t need a hard disk in my computer if I can get to the server faster… carrying around these non-connected computers is byzantine by comparison.

Steve Jobs

References

• MySQL – The world’s most popular open source database
• Amazon RDS

• 

If you are dealing with data, and you most probably are if you are reading this, one of your biggest fears would be not to be able to retrieve them. In a world where data actually surround us, it is critical to be able to retrieve them fast and with the best consistency.

Thus, it is always a good idea to have high availability settings in place to avoid loosing your data.

However, most of the times, we may wish or we may need to save the database and our data, and be a DBA-hero. Not an easy task, and it may be smoother to just perform a backup-restore. Sadly, this is not always the case.

So, this is what we will be facing in this article, we are going to see what to do when there is a data corruption in MySQL and the steps we need to perform to try saving our database.

Before any task or operation, it is always a good idea to copy the already existing data at an OS level:

First, let’s stop MySQL:

And then:

Before attempting to bring back up MySQL, it is a good idea to execute an innochecksum to see the status of your database or of a specific table.

A few words on innochecksum, in case you are not familiar with it:

innochecksum prints checksums for InnoDB files. This tool reads an InnoDB tablespace file, calculates the checksum for each page, compares the calculated checksum to the stored checksum, and reports mismatches, which indicate damaged pages. It was originally developed to speed up verifying the integrity of tablespace files after power outages but can also be used after file copies. Because checksum mismatches cause InnoDB to deliberately shut down a running server, it may be preferable to use this tool rather than waiting for an in-production server to encounter the damaged pages.

So, in order to check a table, you may use a simple command like:

The status will be logged on file /tmp/log.txt, if the file doesn’t exist innochecksum can do this for you. And, the output will be something like the following:

page::41354; log sequence number:first = ; second =
Page:: uncorrupted

Innochecksum offers a variety of options, the most useful is to be able to choose a starting or an ending page, to save time:

Please be aware that innochecksum can be executed only on a Database that is not running. Otherwise, you are going to receive a lock error:

Once you have checked the tables and discovered the problematic table or tables, it is time to attempt to startup the server and see what we can do. Very important, on my.cnf it is imperative to add the following:

log_error_verbosity: The log_error_verbosity system variable specifies the verbosity for handling events intended for the error log. I always prefer to have this set as it is very helpful to identify various issues and at the same time to be able to check the progress on the error_log file.

innodb_force_recovery: The crash recovery mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. If server comes up with a value of 3 and below then the data can be recovered, most probably. Always start with 1 and increase one by one, if the server is not coming up with your previous choice.

Now that we have these settings on our configuration, it is time to start-up the server and hope for the best:

If we are lucky and the server does manage to come up with a value among 1 to 3, it is time to take a full dump of the table(s) that are corrupted. If you are using 8.0.22 version and above, then you may take the dump via MySQL Shell:

Otherwise, you may use the old way:

Once the backup is completed successfully, it is time to drop the corrupted table:

And then restore it from the dump we have taken:

Once the restoration is done, we need to restart the MySQL server, but this time without the innodb_force_recovery parameter. Simply, edit your configuration file and mark it as a comment:

Hopefully, this will work and our database will be up and running with all its’ data intact! Unfortunately, this is not always the case and the database won’t be able to come up for us to retrieve our files. In situations like these, we need to restore from an existing backup or from a replica, if they are available.

To avoid facing serious trouble with your data, condider having in place one (or better yet all) of the following:

• Full backups ( at a daily,weekly,monthly base)
• Incremental backups
• Binlog backups
• In sync replicas
• Innodb cluster

Additionally, in cases of a system crash, it is always helpful to set certain parameters to maintain data consistency. Let’s view the most basic ones:

• innodb_flush_log_at_trx_commit = 1 :

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches.
You can achieve better performance by changing the default value but then you can lose transactions in a crash.
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

• sync_binlog = 1 :

Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance
due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state.
This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.

• innodb_doublewrite = 1 :

The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files.
If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer
during crash recovery.

• relay_log_recovery = 1 :

If enabled, this variable enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position
to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the source then continues.

This global variable is read-only at runtime. Its value can be set with the –relay-log-recovery option at replica server startup, which should be used following an unexpected halt of
a replica to ensure that no possibly corrupted relay logs are processed, and must be used in order to guarantee a crash-safe replica.

• relay_log_purge = 1 :

Disabling purging of relay logs when enabling the –relay-log-recovery option risks data consistency and is therefore not crash-safe.

• innodb_directories :

Another parameter that can assist with corrupted and crash-recovery tablespaces is innodb_directories. Tablespace discovery during crash recovery relies on the innodb_directories setting to identify tablespaces referenced in the redo logs. Innodb_directories can be defined on the start-up and they can be used when moving your tablespaces while the server is offline.

On my.cnf:

At this point, it is wise to mention that the recovery time is also depended on the innodb_log_file_size.

Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O.

Larger log files also make crash recovery slower.

Conclusion

MySQL does offer a stability, however it is really hard to avoid a corruption or to not face having your server crashing at some point. If safety measurements are in place, take a deep breath and dive in to save your data.

When you crash and burn, you have to pick yourself up and go on and hope to make up for it.

Burt Reynolds

References

• innochecksum — Offline InnoDB File Checksum Utility

• InnoDB Startup Options and System Variables

MySQL Replication is a very common topology, widely used in various architecture.
People use it, among others, for High Availability, Read Scalability or Geographic Redundancy.

Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture.
Let’s say you are running MySQL 5.7 then you can easily setup a 8.0 instance as a replica of your 5.7.

Extending this idea it is also possible to replicate your MySQL 5.7 (or 8.0 obviously) to a MySQL Database Service (MDS) instance, the true MySQL PaaS on Oracle Cloud Infrastructure (OCI).

This is that story you are about to read now 🙂

Architecture

To make things clear, this is what the (final) architecture looks like:

• An OCI Compute instance where MySQL 5.7.33 & MySQL Shell 8.0.23 are installed
  • Public IP : 123.45.678.90
  • Private IP : 10.0.0.9
• A MDS instance
  • DB name : MDS2_FRA
  • Private IP : 10.0.1.23
• A replication Channel
  • Channel name : mysqlchannel_MDS2_FRA

Plan

How to proceed?

Let’s split the problem in sub-parts:

1. Create a compute & a MDS instances
2. Create on the source a dedicated user to the replication
3. Dump the MySQL instance running on the OCI compute for being migrated to MDS
4. Load the dump in MDS
5. Create a replication channel on MDS (from OCI to MDS)

Requirement

Before starting, please verify that your source follows the requirements.

Current limitations of MySQL Database Service Inbound Replication

• Only Row-based replication supported
• Only GTID-based replication is supported
• Multi-source replication is not supported
• Replication filters are not supported
• Changes to the mysql schema are not replicated and cause replication to stop
• Source and Replica must run with the same lower_case_table_names to avoid data transfer problems arising from letter case of database or table names in cross-platform topology
• The inbound applier runs under the privileges of the DB System’s admin user

The full & up to date list is available here.

Create a compute & a MDS instances

The creation of thee 2 objects is out of the scope of this article.
You’ll find the relevant resources following these above links:

You can start using OCI for free : Oracle Cloud Infrastructure Free Tier

Create a MySQL DB System is very easy : Getting Started with MySQL Database Service

You also need to have a running compute instance attached to a public subnet on the same VCN as the MySQL DB System : Creating a Compute Instance

Indeed MySQL 5.7 and MySQL Shell 8.0 are installed on this compute instance : Installing MySQL Shell on Linux. (obviously it works also if you are on Windows).

Create on the source a dedicated user to the replication

Assuming the prerequisites above are fine and the security lists are updated, we can now proceed.

Connect by SSH, to the compute instance where MySQL 5.7 is installed:

Note:
If you are using Ubuntu (the case here) the user is ubuntu.
For others GNU Linux systems, the user should be opc.

Connect to the MySQL 5.7 instance using MySQL Shell 8.0.23:

Then create the replication user with his relevant privileges:

We can check the user:

Dump the MySQL instance running on OCI for being migrated to MDS

Now we want to put the current MySQL 5.7 data on the MDS instance.

In this context the best tools to do this job are MySQL Shell utilities.
We will use the instance dump utility.

The backup output can be stored on a Object Storage Bucket – I will write an article on that.
But today, let’s keep it simple. We’ll store the backup locally on the compute instance.

This strategy makes sense if you have enough free disk space on the compute. Also it will be faster to restore the data on MDS with this strategy than having the dump on the object storage.

A good practice when attempting to import data from MySQL 5.7 into MDS is to use MySQL Shell’s upgrade checker utility util.checkForServerUpgrade() to check the schemas and if needed proactively fix any compatibility issues identified.

We’re good to go, so let’s create the backup directory:

Then dump our MySQL 5.7 instance using util.dumpInstance() :

Please note ocimds & compatibility options.
Details are available in the documentation.

Load the dump in MDS

Now we must restore our dump.
We will use the Dump Loading Utility.

From the compute instance :

connect to the MDS instance (10.0.1.23):

Then load the MySQL 5.7 instance’s dump using util.loadDump() :

Please note the ignoreVersion & updateGtidSet options.
Documentation is available here.

Create a replication channel on MDS

Here is the easiest part.
Most of the work will be done using the OCI console \o/

The feature we are looking for is named Channel.
You can find it by clicking on “MySQL” then “Channel” and finally push the “Create Channel” button

The following is pretty straightforward.
Below the main information:

Create in Compartment: Choose the right Compartment

Name (optional): ex. mysqlchannel_MDS2_FRA

Source Connection – Configure Connection to the MySQL Source
Hostname: 10.0.0.9

Username: rplAdmin
(from the replication user created on the source)

Password: Repl1234c@
(from the replication user created on the source)

SSL Mode: – set when create the replication user –
Required (REQUIRED)
Establish an encrypted connection.

Target – Configure the DB System target
Select a DB System: MDS2_FRA

Finally, push the Create Channel button

et voilà!

Wait for the Channel icon become green (ACTIVE)…

In the mean time, if you have the soul of a DBA, you can use some commands, on the MDS instance.

SHOW REPLICA STATUS

Using the replication channel name (default: replication_channel) you can also use this query:

Last but not least, there is Lefred’s favorite replication query (c’est cadeau):

Péroraison

MySQL replication has always been easy to setup.
This is still true with MySQL 8.0.
This is even more true with MySQL Database Service 🙂

Easy, flexible and powerful. This is exactly what your application need.

References

• MySQL Database Service
• Getting Started with MySQL Database Service
  • Options to Run MySQL Server in OCI (and use MySQL Analytics Service)
  • Migrate from on premise MySQL to MySQL Database Service
• MySQL Database Service – Inbound Replication
• MySQL Shell Utilities
• Oracle Cloud Infrastructure Free Tier
• Oracle Cloud Infrastructure (OCI)
• Overview of Object Storage
• MySQL Replication

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Cloud computing is becoming more and more famous among the IT circles and it is growing super fast. Industries are charmed by its flexible character and the avoidance of having their own infrastructure.
An easy solution that offers automation on every field and they can scale up depending on the needs of each user and company.

There are many Cloud providers, such as Oracle Cloud Infrastructure, Microsoft Azure, Amazon Web Services, etc…
In this article, we are going to see how to set up a MySQL Database instance on Microsoft Azure Cloud.

At this point, it is important to clarify that we are going to set up MySQL as a Service (PaaS).

So, before starting with the actual Database creation, we need:

• An Azure subscription, you may create a free Azure account that offers for the first month a $200 credit and for the first year the most wanted services.
• Have an available resource group.
• Have specifically access for MySQL.

Let’s sign in to Azure portal, https://portal.azure.com/ and you should see a screen like the following:

We need to verify that we have the correct resources available for our subscription, in order to set up the MySQL DB:

• From Azure Portal, go to Subscriptions
• Then, on Development Services and choose Resource Provider
• Scroll down and check if Microsoft.DBforMySQL is registered

If it is not registered, then click Register on the top left corner:

Once the registration is completed, we are ready for creating our DB.

To create the MySQL Database, we need to choose Azure Database for MySQL Servers:

By clicking the Add option on the top left, we are presented with two options, as it can be shown below:

• Single Server is a fully managed database service with minimal requirements for customizations of the database. The single server platform is designed to handle most of the database management functions such as patching, backups, high availability, security with minimal user configuration and control. The architecture is optimized to provide 99.99% availability on single availability zone. Single servers are best suited for cloud native applications designed to handle automated patching without the need for granular control on the patching schedule and custom MySQL configuration settings. MySQL available version is 8.0.15
• Flexible Server (Preview) is a fully managed database service designed to provide more granular control and flexibility over database management functions and configuration settings. In general, the service provides more flexibility and server configuration customizations compared to the single server deployment based on the user requirements. The flexible server architecture allows users to opt for high availability within a single availability zone and across multiple availability zones. Flexible servers also provide better cost optimization controls with the ability to start/stop your server and burstable SKUs, ideal for workloads that do not need full compute capacity continuously. MySQL version is 5.7 and 8.0.21 is on trial.

We choose Single Server for our start, and we are presented with the following screen:

We need to set accordingly:

Subscription: Make sure that you have choosen the corresponding subscription

Resource Group: Choose the proper resource group with the correct resources

Server name: This is actually the name of the MySQL Server

Data Source: You may create the new server from a Backup, here we choose none

Location: Choose the proper location that your resource group has access to

Version : Available versions 5.6,5.7 and 8.0

Compute+storage: Choose the best settings, we have Basic , General Purpose and Memory Optimized. The basic differences are among Basic & General Purpose/Memory Optimized.

Basic offers 2 cores and storage up to 1024GB.

General Purpose offers up to 64 cores and storage up to 16384GB.

Memory Optimized offers up to 32 cores and storage up to 16384GB.

All three available configurations offer Backup retention policy up to 35 days (default is 7 days).

General Purpose:

Admin username: Choose the proper name of the DB admin

Password and Confirm Password : Create a strong password for the DB admin

As soon as these settings are ready, we go to the Next:

Additional Settings:

We are able to select encryption:

And then moving on:

Tags : are name/value pairs that enable you to categorize and view consolidated billing by applying the same tag to multiple resources and resource groups. We won’t be organizing our resources with tags now so we are moving on to Review&Create to check the price and the options we have selected:

While our DB is being created, we see its progress:

Once ready, we are able to access the new resource that we created :

From this page, we may stop the server, reset the password , restore/delete and restart.

From the left side-bar, we may create alerts/metrics and to gather information.
The server logs are also available on this section, as well as the Replica option in case we need to set up a replica for our environment.

Same for the server settings:

One important note, before we connect officially to our Database.
We need to add the proper Firewall Rule to allow our Client to connect to the MySQL instance:

In order to connect to the Database via MySQL Workbench we need to use the Server name on the Hostname as well as the Username, like you may see on the following screenshot:

Finally, we are connected to the Database and we are able to execute any query we wish:

If you wish to delete the Database, Azure will give you a warning that the deletion is irreversible and you will need to provide MySQL Server’s name.

The same concept lies for the Flexible Server, but with a couple of differences:

Here, we need to specify the expected Workload type and based on that the most appropriate settings are being recommended and we also have Zone Redundancy available :

Backups offer the same retention policy as in Single Server:

To be able to connect, we need to set the Firewall Rules, either a specific Public address to access the Flexible MySQL Server or via a Virtual Network:

Flexible Servers offers synchronous replication when Zone Redundancy is enabled, however it does not offer read-only replicas and due to synchronous replication to another availability zone, primary database server can experience elevated write and commit latency.

We need to mention here that if a more updated version of MySQL is needed, then we will need to have a VM created.

Conclusion

Depending on your needs, Cloud computing can for sure serve them and most probably for a fair price. It offers a variety of services, so give it a try!

After all, it is no coincidence that our heads are up in the clouds.

References

https://www.mysql.com/

https://docs.microsoft.com/en-us/azure/mysql/select-right-deployment-type

https://docs.microsoft.com/en-us/azure/mysql/flexible-server/concepts-high-availability

MySQL Shell is a modern tool that can be used by both developers and database administrators. It is a tool that can be used with any of the following:

• SQL, Python, Javascript
• MySQL Document Store (NoSQL, X DevAPI)
• JSON Documents / SQL tables

In one of our previous articles – Setting up Replication with various methods for MySQL 8 – we reviewed how to create a replica with multiple tools.
Now, it is time to perform the same action but with MySQL Shell.

In general, MySQL Shell is used with MySQL InnoDB Cluster to deploy a MySQL Group Replication, with or without MySQL Router.
Here we will examine how to create an asynchronous replication.

First of all, we may download MySQL Shell via the following url:

https://dev.mysql.com/downloads/shell/

We will be using MySQL Shell version 8.0.22, that is currently the latest and includes extra features.

Let’s start!

On the source database, we connect to mysqlsh:

We may use option \sql or \py to use SQL or Python respectively instead of the JavaScript mode (\js):

For creating the dump we will be using the util.dumpInstance().
Please note that the outputUrl (target directory to store the dump files) must be an empty folder, otherwise you will receive an error like :

util.dumpInstance: Cannot proceed with the dump, the specified directory ‘/’ already exists at the target location / and is not empty. (ArgumentError).

If the folder doesn’t exist mysqlsh is smart enough to create the folder for us:

Now, let’s take a minute to review the options we are using:

dryRun: This won’t dump anything, it will print information on what will be dumped.

ocimds: Enable checks for compatibility with MySQL Database Service (MDS).

threads: How many threads to be used for dumping data chunks from the server.

compatibility: Apply MySQL Database Service compatibility modifications when writing dump files. Supported values: “force_innodb”, “strip_definers”, “strip_restricted_grants”, “strip_tablespaces”.

force_innodb – The MySQL Database Service requires use of the InnoDB storage engine. This option will modify the ENGINE= clause of CREATE TABLE statements that use incompatible storage engines and replace them with InnoDB.

strip_definers – strips the “DEFINER=account” clause from views, routines, events and triggers. The MySQL Database Service requires special privileges to create these objects with a definer other than the user loading the schema. By stripping the DEFINER clause, these objects will be created with that default definer. Views and Routines will additionally have their SQL SECURITY clause changed from DEFINER to INVOKER. This ensures that the access permissions of the account querying or calling them are applied, instead of the user that created them. This should be sufficient for most users, but if your database security model requires that views and routines have more privileges than their invoker, you will need to manually modify the schema before loading it.

strip_restricted_grants – Certain privileges are restricted in the MySQL Database Service. Attempting to create users granting these privileges would fail, so this option allows dumped GRANT statements to be stripped of these privileges.

strip_tablespaces – Tablespaces have some restrictions in the MySQL Database Service. If you’d like to have tables created in their default tablespaces, this option will strip the TABLESPACE= option from CREATE TABLE statements.

As we are using a database that contains other storage engines apart from InnoDB engine, we will be using option force_innodb, along with strip_restricted_grants and strip_definers, as we have routines with DEFINER.

So, here we go:

As this moves along, we may open a new connection to our target host, enabling the local_infile parameter so the dump can be imported and at the same time we disable innodb_redo_log as well.
Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite buffering:

Before proceeding, let’s discuss once again the options we are using:

/data_imp: is where the files will be located.

threads: The number of parallel threads to use to upload chunks of data to the target MySQL instance.

updateGtidSet: Apply the gtid_executed GTID set from the source MySQL instance, as recorded in the dump metadata, to the gtid_purged GTID set on the target MySQL instance.

skipBinlog: Skips binary logging on the target MySQL instance for the sessions used by the utility during the course of the import, by issuing a SET sql_log_bin=0 statement.

waitDumpTimeout: Setting this option activates concurrent loading by specifying a timeout (in seconds) for which the utility waits for further data after all uploaded data chunks in the dump location have been processed.

updateGtidSet is available from 8.0.22 version.
If you are using 8.0.21, then you will need to manually set the GTID by locating the gtidExecuted field in the @.json dump file in the dump metadata:

So, basically we have taken a dump from the source host and at the same time, restore it to the target host.
Once it is completed, and as MySQL Shell is really fast we won’t have to wait for that long, we log in to the target host and execute the following command:

And our replica is all set.

Apart from the util.dumpInstance(), we also have the options to use util.dumpSchemas() and util.dumpTables(), that was introduced in MySQL Shell 8.0.22, and offers the option for dumping specific schemas, tables or views.

It is also important to mention that the above-mentioned utilities are fully compatible with Oracle Cloud Infrastructure.

Conclusion

MySQL Shell is a really useful tool that offers a variety of functionalities.
It is super fast and saves us a lot of time when dealing with broken replication or with data transfer.

Try it, you will love it 🙂

References

• Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
  • https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
• Dump Loading Utility
  • https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
• loadDump()
  • https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/group__util.html#ga661e259905ddea494dbb8f62cf5f6342