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 …

In the previous part, we created our application and our 2 functions.

Now we need to create an API Gateway to be able to call these functions from outside OCI. Using a third party scheduler, our laptop, etc…

Before creating the gateway, we need to create some policy to allow the API Gateway to call our functions. I’ve tried to create the policy after, it seems to not work as expected.

Security

Dynamic Group

We need to create a Dynamic Group that will match our gateway:

The matching rule is checking the type of resource that should be ApiGateway and my compartment’s id:

All {resource.type='ApiGateway', resource.compartment.id = 'ocid1.compartment.oc1..xxxxxxxxx'}

Policy …

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 …

MySQL 8.0.30 was released on 26-07-2022. It has a good list of features.

• Generated Invisible Primary Key
• Dynamic Redo log Sizing
• Instruments to monitor group replication memory
• Support for Ubuntu 22.04

A most notable feature for Database Administrators and database reliability engineers is Dynamic redo logging.

The more widely adopted open-source backup tool is Xtrabackup. The change in the redo log design has affected this hot backup tool.

2022-10-04T18:40:08.211998+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2022-10-04T18:40:08.212264+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: …

Let’s continue our journey of deploying the MySQL Database System on OCI with Terraform.
This time we will see how we can use a backup (see [1] and [2]) as a source (initial data) for a new instance.

Within the oci_mysql_mysql_db_system it’s possible to define a source detailing how to provision the initial data of the db system.

Let’s deploy a new MySQL Database Instance using a manual backup we made earlier:

We can also find the backup’s ocid using the oci cli command:

 $ oci mysql backup list …

Today I would share a nice book related to MySQL 8.0 for our Chinese users.

The original title of this book by Scott Yao, is MySQL 8.0 运维与优化

I had the chance to read parts of the book before it was published to have my comment on the back cover.

This book is very detailed with many examples on how to administer a MySQL server.
It covers the new features of MySQL 8.0 and Scott illustrates perfectly how to take advantage of them.

Reading the book, it is obvious that the author is an experienced MySQL DBA and is sharing his experience that many junior DBAs can certainly benefit from.

This is a very technical book, as I like, I really recommend it to all Chinese people and I hope to see it translated into English too.

And finally I would thanks Scott for having sent to me a hard signed copy of the …

Recently I wrote an article on how to define a backup policy for MySQL Database Service in OCI using Terraform. We saw that it was possible to define tags (defined_tags and freeform_tags) in the backup_policy section of a oci_mysql_mysql_db_system resource.

However, it seems that these tags are never used for manual or automated backups in the MySQL Database Service backup process. In the strategy implemented on OCI, the backups inherit the tags from the MySQL DB system itself.

This means that if you want to have some custom tags on your backups, you need to specify them in the MySQL Database resource like this (on line 12):

resource "oci_mysql_mysql_db_system" "MDSinstance" {
    admin_password = var.admin_password
    admin_username = …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting time …

With Terraform OCI provider 4.90.0 (released August 24th, 2022), we have added the possibility to specify the value of editable configuration variables directly from Terraform.

These variables must be defined in a resource of type oci_mysql_mysql_configuration like this:

resource "oci_mysql_mysql_configuration" "mds_mysql_configuration" {
        #Required
        compartment_id = var.compartment_ocid
    shape_name   = var.mysql_shape

         …

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.