In a recent article, I explained how to test MySQL Database Service (aka MDS) on OCI without having to setup a VPN.

A VPN is the recommended way and of course using some man-in-the-middle exposing the MySQL Classic and X Protocol port is something you should absolutely avoid !

So, in this article we will see another way to test or use MDS without VPN but using a more secure option.

SSH

The solution uses a SSH tunnel between your machine (laptop, etc..) and OCI.

How does it work ?

We have a MDS instance (10.0.1.3), and a compute instance (10.0.0.2). The compute instance is in the public subnet and has a public IP assigned to it.

Then we open a SSH tunnel to the compute instance’s public IP that forward all traffic to a certain port on localhost (we will use 3406) …

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 …

If you plan for example to have a bastion host in the cloud to access all your MySQL servers and use only MySQL Shell, the first time you connect to one of the host, you will realize that MySQL Shell prompts for a password but doesn’t ask you to store it of not.

Example:

mysqlsh fred@192.168.5.94
Please provide the password for 'fred@192.168.5.94': ****
MySQL Shell 8.0.21
Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'fred@192.168.5.94'
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 16 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL 192.168.5.94:33060+ ssl JS >
Bye!

As you could see, no …

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

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:

2020-07-24T06:54:00.877128Z 46 [Warning] [MY-013360] [Server] 
Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in 
a future release. Please use caching_sha2_password instead'

The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to caching_sha2_password.

As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.

Of course we verified that all users had the plugin changed.

This is important when helping people (friends, colleagues, …

In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.

However that integer can have different types each having their size limitation, and they can also be signed or not.

Many people when creating a table use by default the following syntax do define their auto_increment primary key:

id int auto_increment primary key

This is a signed integer where the maximum value is 2,147,483,647.

It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.

And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)

MySQL Shell Plugin

Once again, MySQL Shell comes to the rescue with an easy plugin: …

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

MySQL 8.0.21 has been released today, wooohooo \o/

As usual, this release contains contributions and let me thanks all the contributors on behalf of the MySQL Team.

MySQL 8.0.21 includes contributions from Edgars Irmejs, Daniël van Eeden, Jeremy Cole, Wenfeng Shih, Billy O’Neal, Lou Shuai, Tsubasa Tanaka and Facebook.

Thank you all for your great contributions. MySQL is an Open Source project, GPL, and we accept contributions !

Here is the list of the contributions above:

• Filesort …

As proven again very recently by my colleague and friend Dimitri K, with the TPCC “Mystery”, sysbench made by a former colleague and friend Alexey K, is really the recommended framework to benchmark your system.

I use sysbench of course to benchmark MySQL but also my OS and hardware capabilities.

For those willing to benchmark MySQL 8.0 with sysbench using all supported authentication methods including of course the much more secure default one, caching_sha2_password, you need to compile sysbench using the MySQL libs.

And …

With this post we are reaching the end of our journey to HA for WordPress & MySQL 8.0 on OCI.

If you have not read the two previous articles, this is just the right time.

• MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI
• MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI – part II

We started this trip using the MySQL InnoDB ReplicaSet where only 2 servers are sufficient but doesn’t provide automatic fail-over.

In this article we will upgrade our InnoDB ReplicaSet to …