Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?

If you enable the status for the  INNODB_METRICS table in INFORMATION_SCHEMA, you will be able to query CPU information.

First, check if those status are enabled:

MySQL> SELECT name, subsystem, status 
FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
+---------------+-----------+----------+
| name          | subsystem | status   |
+---------------+-----------+----------+
| cpu_utime_abs | cpu       | disabled |
| cpu_stime_abs | cpu       | disabled |
| cpu_utime_pct | cpu       | disabled |
| cpu_stime_pct | cpu       | disabled |
| cpu_n         | cpu       | disabled |
+---------------+-----------+----------+
5 rows in set (0.00 sec)

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

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 …

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 …

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

As you may have noticed, MySQL 8.0.16 has been released today !

One of the major long expected feature is the support of CHECK contraints .

My colleague, Dave Stokes, already posted an article explaining how this works.

In this post, I wanted to show how we could take advantage of this new feature to validate JSON values.

Let’s take the following example:

So we have a collection of documents representing rates from a user on some episodes. Now, I expect that the value for the rating should be between 0 and …

Everywhere I go to present MySQL InnoDB Cluster, during the demo of creating a cluster, many people doesn’t understand why when I’ve 2 members, my cluster is not yet tolerant to any failure.

Indeed when you create a MySQL InnoDB Cluster, as soon as you have added your second instance, you can see in the status:

    "status": "OK_NO_TOLERANCE",      
    "statusText": "Cluster is NOT tolerant to any failures.",

Quorum

Why is that ? It’s because, to be part of primary partition (the partition that holds the service, the one having a Primary-Master in Single Primary Mode, the default mode), your partition must reach the majority of nodes (quorum). In MySQL InnoDB Cluster (and many other cluster solutions), to achieve quorum, the amount of members in a partition must be > (bigger) than 50%.

So when we have 2 nodes, if there is a network issue between the …

Why migrating to MySQL 8.0 ?

MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this blog (see these TOP 10 for features for developers and this TOP 10 for DBAs & OPS).

Not anymore a drop in replacement !

…