If you just migrated to MySQL 8.0, you may have seen that the default authentication plugin has been changed to a more secure one: caching_sha2_password and I’ve already written some articles about it.

Now let’s discover how Perl users can deal with MySQL 8.0.

The driver to use MySQL with Perl is perl-DBD-MySQL. MySQL 8.0 is supported but the new authentication plugin might not be. This depends of the mysql library linked during compilation of the module.

problem connecting to MySQL 8.0

The error you may encounter is the following:

DBI connect('host=localhost','fred',...) failed: Authentication plugin
 'caching_sha2_password' cannot be loaded:  …

MySQL 8.0 upgrade checklist

Recently one of our customers in Fintech. business (among the largest one in the Asia) wanted to upgrade from MySQL 5.7 to MySQL. 8.0. and they approached us for a safest and durable MySQL upgrade strategy, roadmap and execution. In Fintech. business every transaction needs to durable from statutory regulatory compliance perspective and we at MinervaDB never wanted to go for unplanned / easy in-place MySQL 8.0 upgrade method here without proper pre-migration audit, We wanted to list down in detail what are the possible scenarios this MySQL 8.0 upgrade will fail and the compatibility issues between MySQL 5.7 and MySQL 8.0. Thankfully Upgrade Checker utility that comes with MySQL Shell 8.0 can be executed against MySQL 5.7 server to confirm upgrade readiness, We have written a blog on MySQL Shell 8.0 Upgrade Checker …

Tweet

The sys schema was created to make it easier to use the Performance Schema. This included several functions, for example to convert the picoseconds used by the Performance Schema into human readable strings. In MySQL 8.0.16, three of these functions have been implemented as native functions in MySQL Server.

Why do away with the sys schema functions? There are two reasons: performance and ease of use. The native functions are written in C++ like the rest of the server whereas the sys schema functions were written as stored functions. Function written in C++ are inherently faster than stored functions. Additionally, that the functions are native means you no longer need to prefix them with sys. to tell MySQL where …

To operate more easily a MySQL Group Replication (InnoDB Cluster), the Group Replication plugins provides some UDFs.

If you have read the recent article from Tiago Vale about the Group Replication Communication Protocol, you may have heard about two new UDFs allowing to get or set  the communication protocol.

So what are all the UDFs provided with the Group Replication and what’s their purpose ?

SELECT UDF_NAME FROM performance_schema.user_defined_functions 
WHERE UDF_NAME LIKE 'group_repl%';
+-------------------------------------------------+
 | UDF_NAME                                        |
 +-------------------------------------------------+
 | group_replication_get_communication_protocol    |
 | group_replication_get_write_concurrency         |
 | group_replication_set_as_primary                | …

At the end of the month, some engineers of the MySQL Team will be present in Austin, TX !

We will attend the first edition of Percona Live USA in Texas.

During that show, you will have the chance to meet key engineers, product managers, as well as Dave and myself.

Let me present you the Team that will be present during the conference:

The week will start with the MySQL InnoDB Cluster full day tutorial by Kenny and myself. This tutorial is a full hands-on tutorial where we will start by migrating a classical asynchronous master-replicas topology to a new MySQL InnoDB Cluster. We will then experience …

As explained in this previous post, it’s now (since 8.0.16) possible to use the MySQL Shell Reporting Framework to monitor MySQL InnoDB Cluster.

Additionally, when a member of the MySQL InnoDB Cluster’s Group leaves the group for any reason, or when a new node is added from a backup, this member needs to sync up with the other nodes of the cluster. This process is called the Distributed Recovery.

During the Distributed Recovery, the joiner receives from a donor all the missing transactions using asynchronous replication on a dedicated channel.

It’s of course also possible to monitor the progress of this recovery process by calculating how many transactions have …

Tweet

MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.

In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.

The built-in MySQL Connector/Python connection attributesConnection Attributes in MySQL Server

The …

With MySQL Shell 8.0.16, a new very interesting feature was released: the Reporting Framework.

Jesper already blogged about it and I recommend you to read his articles if you are interested in writing your own report:

• https://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/
• https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/

I this post, I will show you one user-defined report that can be used to monitor your MySQL InnoDB Cluster / Group Replication.

Preparation

Before being able to use the report, you need to download 2 files. The first one is the …

Tweet

MySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”.

Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are:

• mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location …

This post is just a reminder on how to find which settings have been set on MySQL Server.

If you have modified some settings from a configuration file or during runtime (persisted or not), these two queries will show you what are the values and how they were set. Even if the value is the same as the default (COMPILED) in MySQL, if you have set it somewhere you will be able to see where you did it.

Global Variables

First, let’s list all the GLOBAL variables that we have configured in our server:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED';

This is an example of the output:

Session Variables

And now the same query for the session variables:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, …