As you know, the best way to perform logical dump for MySQL is to use MySQL Shell Dump & Load utilities. This is the most powerful option as it can dump and load in parallel (it also include many options to migrate to MDS very easily and supports OCI Object Store too).

One of the main question I receive related to MySQL Shell utility is related to the use of MySQL Shell in non-interactive mode with parameters requiring arrays.

What does that mean ?

For example if you want to dump a MySQL instance but you want to exclude some tables, you have an option called excludeSchemas and it expect an array of strings with the list of schemas you want to exclude in the dumb.

In interactive mode, this is how we use it:

JS> util.dumpInstance("/tmp/dump", {excludeSchemas: 
    ["mysql_innodb_cluster_metadata", "fred_test"], 
    threads: 8, showProgress: true})

However this notation is not …

Today we will see how to use MySQL Database Service aka MDS with WordPress.

To achieve this easy task, we will use the architecture we already deployed in this article.

We have then two Compute Instances on OCI, 1 running WordPress (Apache and PHP) and one running MySQL 8.0.

The Plan

This is how we will proceed to migrate to MDS with minimal maintenance time, we will:

1. create a MDS instance
2. verify if the database is ready to act as replication source
3. dump the MySQL instance running on OCI for being migrated to MDS.
4. load the dump in MDS
5. create a user dedicated to the replication
6. create a replication channel on MDS (from OCI to MDS)
7. modify WordPress config to point to MDS

Create a MDS …

Please note that this is not recommended for any production purpose, don’t expose MySQL traffic to the Internet !

MySQL Database Service is now available, take a look at this nice introduction video:

It is usually a very bad idea to expose a database on a public IP (MySQL or any other database ). MDS doesn’t allow you to implement such bad security practice.

The best way to use your MySQL instance is to use a VPN as I explained in this article.

However, this can be restrictive for a simple test. In this post I will illustrate how you can setup a MDS instance and test it without having to setup any kind of VPN. Of course I recommend you to use such practice only for testing …

I wanted to be brave and I installed MySQL Group Replication manually…. it was painful !

Then I realized that managing those servers and especially deal with MySQL Routers was even more painful !

What are my options now ? Is there a solution or do I need to restart from scratch ?

Asking the answer is already answering it… and once again MySQL Shell at the rescue.

MySQL Group Replication

I’ve configured everything manually. I also loaded group_replication and clone plugins and finally after having bootstrapped my Group here is what I have:

mysql> select member_host, member_port port, member_state state, 
       member_role role, member_version version 
       from performance_schema.replication_group_members;
+-------------+------+--------+-----------+---------+
| member_host | port | state  | role      | version | …

In MySQL 8.0.21, we can see who and how mysqld was stopped and when the process ended.

Let’s have a look at different scenarios in this article.

SHUTDOWN SQL statement

The first test is to initiate the shutdown of MySQL from a SQL client:

mysql> shutdown;

In the error log (log_error_verbosity is set to 2, the default), we can see:

2020-07-14T07:17:28.865274Z 10 [System] [MY-013172] [Server] 
          Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.21).
2020-07-14T07:17:31.258884Z 0 [System] [MY-010910] [Server] 
         /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL.

We can easily identify when the server started the shutdown process and when it finished.

Stopping using systemd

Today is a good day for me. Today I tweeted that MySQL after 25 years is still GPL and same for all important components: the server, the shell, the router, the clone plugin, workbench !

To be honest, Oracle has been a very good steward for MySQL so far, PZ and Kaj admitted it too. This exactly what I was thinking when I joined the MySQL family and I’m still convinced about it after 4 years… and never as much as now !

If you know me, you should know that for more than 20 years I always praised and defended Open Source! All kind, not only MySQL! I started of course with Linux and then a huge amount of projects where I dedicated time, docs, code, bug reports, chats… As you know I also worked for Stone-IT, X-tend, …

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 …

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.

…

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 …

What a great event ! I got very positive feedback for this extended preFOSDEM MySQL Day 2020.

Before my summary, I wanted to thank again all speakers and attendees. Without you this event won’t even exist. And thank you to ICAB for hosting us yet another time !

As you may know, we had 2 parallel tracks for 2 consecutive days. We had very valuable technical content from the MySQL developers and also from the external community members who talked about their favorite database.

There was a large interest in MySQL NDB Cluster and it was obvious that the nice presentations we had, encouraged people to try the worlds highest performance open source in-memory database (and they can even test it on their laptop with DBDeployer). The NDB users were also happy with the new stuff and impressive numbers that Bernd …