Once again, the latest version of MySQL 8.0 includes several contributions from the MySQL Community.

MySQL 8.0.31 was released on October 11th 2022.

I would like to thank all contributors on behalf of the entire Oracle MySQL team !

This new release contains patches from Facebook/Meta, Dennis Gao, Lou Shuai, Caza Zhang, Zhang Simon from Tencent, Dimitry Kudryavtsev, Rahul Malik from Percona, Alex Xing, Marcelo Altmann from Percona, Matthew Steeples, Adam Croot, Luis Pinto and Evgeniy Patlan from Percona for all his patches on MySQL Shell.

Here is the list of the above contributions and related bugs:

Replication

• #101056 – Ack_receiver may lost the slave semi-sync ack due to net timeout – Dennis Gao …

If like me you are an old experienced MySQL DBA, to analyze your MySQL workload, you certainly have used the slow query log with long_query_time set to 0.

The slow query log is a file that contains all the queries whose execution time is greater than the value of long_query_time. This file can be huge and uses up all available disk space on busy systems.

Constantly writing to that file can also add an overhead to the server.

For those reasons, the slow query log is not available in MySQL Database Service (HeatWave) in OCI.

Plan B ?

As a DBA, what are my options for finding the queries that need to be optimized? As usual, the queries that consume the most time are the ones that need some attention

It can be a very long query or a short query executed too many times.

Currently the MySQL DBAs use …

Apache Airflow is an open-source workflow management platform for data engineering pipelines.

Today we will see how we can deploy very easily Apache Airflow on a Compute instance in Oracle Cloud Infrastructure (OCI) using MySQL HeatWave Database Service (MDS).

I am sharing Terraform modules you can use to deploy in your architecture:

Architectures

With the modules you can deploy architectures like these:

with a single MDS instancewith MDS High Availabilitywith HeatWave Cluster

We will use the exact same modules built into a stack for OCI’s Resource Manager. This will allow us to deploy the architecture just in few clicks.

Deployment

We open in a browser the following GitHub repository: …

Recently I blogged about how to easily deploy a LAMP application to MDS.

Using the Terraform module (OCI Resource Manager’s Stack) you also have the possibility to choose the PHP version to install:

But as you should already know, not all versions support the latest default authentication method in MySQL 8.0: caching_sha2_password [1], [2], [3].

Most of the PHP applications are using mysqli and mysqlnd …

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, …