Let’s continue our series of using OCI to install popular Open Source programs with MySQL 8.0. After WordPress, Drupal, it’s not the Moodle’s turn!

This is a request from my Japanese colleague Machiko

We will use two different compute instances:

• the application server (apache, PHP and Moodle)
• the database server (MySQL 8.0)

The application server will be reachable from the Internet on port 22 (ssh) and 80 (http). The database server won’t be reachable from outside, we will have to use the application server as jumphost to install the server.

This is an overview of the proposed architecture:

Deploying 2 compute instances

The first step is to install 2 compute instances, one in the Public Subnet and one in the Private Subnet.

We first start with the application server (called myMoodle):

As the second instance will be located …

Last Tuesday, it was the very first session of DB AMA, Morgan Tocker made a nice presentation of MySQL Performance_Schema and illustrated it with some nice queries to get Meta et Data Locks.

As those queries were not that simple to write or at least to remember, I thought it might be a good idea to add them to MySQL Shell, the best MySQL DBA Tool !

I’ve then added a new method to the check plugin: getLocks().

Let’t see it in action:

As you can see, this is a small extension that can offers you a better view of what’s locked per transaction.

You can find several MySQL Shell Extension directly on github: …

In the previous post, we setup a Drupal website with MySQL as backend on a different compute instance in a different subnet.

Today we will increase the High Availability of our Database by transforming this single MySQL instance into a MySQL InnoDB Cluster!

The architecture will be the following:

We will use 2 different Availability Domains (AD), of course we could have used 3, it’s up to you.

We will install MySQL Router on the Application Server as advised, and we will modify our Drupal’s configuration to connect locally on the R/W port of the MySQL Router.

You will see that this is very easy to achieve, the most …

MySQL 8.0.19 introduced the ability to lock an account on a MySQL instance after too many failed logins.  This is not turned on by default, the locking period is measured in days (more below), and I would urge caution as not to 'denial of service' yourself out of your server.

Syntax CREATE USER 'foobar'@'localhost'
IDENTIFIED by 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

DataAnd if you look at the mysql.user entry you will see

User_attributes: {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}}

Example$mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: …

My previous post related on how to install WordPress on Oracle Cloud seemed to be very popular. To continue the exploration of OCI, today we will install Drupal.

This time, even if it’s possible to use the Free Trier like in the WordPress post, I will use a pay account to have the possibility to split the Web Server and the Database into two different subnets. Both instances will have their own subnet and only the Webserver will be reachable directly from the Internet. MySQL will be installed on a private subnet that could reach Internet only via a NAT gateway.

This is an overview of the proposed architecture:

At the end of the blog you will also be able to see this process in video.

…

MySQL has come up with window function in latest GA MySQL 8.0 . It is a major leap in SQL for MySQL. This presentation provides an overview to window function in MySQL 8.0.

Window functions in MySQL 8.0 from Mydbops Window Function in MySQL 8.0

Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.

• In MySQL community versions if you want to mask your data, You can go with a Maxscale load balancer.
• They introduced a new masking filter on the Maxscale 2.1 version.
• My colleague Prasanth written about column-level data masking. For reference click here Column Level Data Masking.
• In MySQL 8.0 Enterprise they have introduced a new security feature called data masking.
• This feature was introduced in MySQL 8.0.13 Enterprise Edition and it provides data masking and de-identification capabilities.

What is Masking ?

• Transformation of existing data to mask it and remove …

Today we will see how to install WordPress in Oracle Cloud (OCI).

We will use Oracle Cloud’s Free Trier and deploy 2 compute instances:

• the webserver (apache, PHP and WordPress)
• the database server (MySQL 8.0)

We will use one public IP to reach our site and our two servers will communicate using their own private network:

Deploying 2 compute instances

Let’s start by creating 2 compute instances:

We will use the Oracle Linux 7.7 on Micro Instances:

As I am in Belgium, I use the Frankfurt datacenter.

When our instances are deployed, we can find the public IP of one of them. This is the one we will use as webserver (you can free the public IP on the other one, the MySQL one):

At the creation, we entered a ssh key to access …

As you know, Oracle Cloud Infrastructure (aka OCI) becomes more and more popular. You can find OCI data centers almost everywhere and the price is very attractive.

Something very interesting is the possibility to have different availability domains but also different fault-domains in the same availability one !

Oracle provides a lot of modules to deploy your architecture on OCI using Terraform. Those are called oracle-quickstart repos. You can find them on Github.

There is an official Reference Architecture for MySQL InnoDB Cluster in OCI that I encourage you to read before deploying your MySQL HA solution …

Database schema change is becoming more frequent than before, Four out of five application updates(Releases) requires a corresponding database change, For a DBA schema change is a more often a repetitive task, it might be a request from the application team for adding or modifying columns in a table and many more cases.

MySQL supports online DDL from 5.6 and the latest MySQL 8.0 supports instant columns addition.

This blog post will look at the online DDL algorithms inbuilt which can be used to perform schema changes in MySQL.

DDL Algorithms supported by InnoDB is,

• COPY
• INPLACE
• INSTANT ( from 8.0 versions)

INPLACE Algorithm:

INPLACE algorithm performs operations in-place to the original table and avoids the table copy and rebuild, whenever possible.

If the INPLACE algorithm is specified with the …