I already covered some information related to users password recently (see this post and this one).

Today, let’s have a look at the password management features available in MySQL 8.0.

We will cover the password management in MySQL in 5 sections:

• validation
• expiration
• reuse policy
• verification policy
• dual active passwords

Validation

Password validation means that a new password must comply with some policy to be sure the password is not weak. This job is handled by a component in MySQL 8.0: validate_password.

To be able to use that component, you need to install it. You can verify if it’s …

It seems, that this is a question that regularly shows up in forums or stackoverflow.

To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command (ERROR 1410 (42000): You are not allowed to create a user with GRANT).

This means that to grant some privileges, the user must be created first.

Let’s create a user ‘user1‘ with ‘ChangeMe‘ as password that the user will have to change:

mysql> create user 'user1' identified by 'ChangeMe' password expire;
Query OK, 0 rows affected (1.35 sec)

Let’s try to connect to MySQL using that new created user:

$ mysql -u …

We saw in the previous post that MySQL likes memory. We also saw how to perform operating system checks and some configuration changes for Swap and NUMA.

Today, we will check what MySQL server can tell us about its memory usage.

Introduced in MySQL 5.7 and enabled by default in MySQL 8.0, the Performance_Schema‘s Memory instrumentation allows us to have a better overview of what MySQL is allocating and why.

Let’s check on our MySQL server using SYS:

Pay attention that there is a bug related to how InnoDB Buffer Pool statistics are accounted in Performance_Schema. This is fixed in 8.0.13. …

As you may know, sometimes MySQL can be memory-hungry. Of course having data in memory is always better than disk… RAM is still much faster than any SSD disk.

This is the reason why we recommended to have the working set as much as possible in memory (I assume you are using InnoDB of course).

Also this why you don’t want to use Swap for MySQL, but don’t forget that a slow MySQL is always better than no MySQL at all, so don’t forget to setup a Swap partition but try to avoid using it. In fact, I saw many people just removing the Swap partition… and then OOM Killer did its job… and mysqld is often its first victim.

MySQL allocates buffers and caches to improve performance of database operations. That process is explained in details in the manual.

In this article series, I will provide you some …

Alexey made this amazing tool that the majority of MySQL DBAs are using, but if you use sysbench provided with your GNU/Linux distribution or its repository on packagecloud.io you won’t be able to use it with the new default authentication plugin in MySQL 8.0 (caching_sha2_password).

This is because most of the sysbench binaries are compiled with the MySQL 5.7 client library or MariaDB ones. There is an issue on github where Alexey explains this.

So if you want to use sysbench with MySQL 8.0 …

Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example:

In April, I already posted an article on how to upgrade safely your MySQL InnoDB Cluster, let’s review this procedure now that MySQL 8.0.12 is out.

To upgrade all the members of a MySQL InnoDB Cluster (Group), you need to keep in mind the following points:

• upgrade all the nodes one by one
• always end by the Primary Master in case of Single Primary Mode
• after upgrading the binaries don’t forget to start MySQL without starting Group Replication (group_replication_start_on_boot=0)
• to run mysql_upgrade

Let’s see this in action on the video below:

As you could see, this is quick and easy.

…

We received feedback about how a member should act when leaving the group. And the majority of users wanted that when a node drops out of the group, it should kill all connections and shutdown. I totally agree with that behavior and it’s now the default in MySQL 8.0.12.

This new feature is explained in WL#11568.

Before this change, the server goes into super read only mode when dropping out of the group and allows users connected to this server or new connections (if you don’t use the router) to read old data.

Let’s check this out in the following video:

So now in MySQL 8.0.12, there is a mnew option called …

After having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after having illustrated how to use Pacemaker, this article explains how to setup HA for MySQL Router using keepalived.

Keepalived is very popular, maybe because it’s also very easy to use. We can of course use 2 or more servers. The principle is the same as on the previous articles, if the router dies, the virtual IP used by the application server(s) to connect to MySQL is sent to another machine where mysqlrouter is still running.

Let’s have a look at the configuration, in this case we use 2 machines, mysql1 and …

This article will explain how to setup HA for MySQL Router as described in a previous article about where should the router stand.

For this setup, I will use Pacemaker (part of RedHat High Availability Add-on and available on RHEL, CentOS, Oracle Linux, …).

Of course we need a MySQL InnoDB Cluster but we won’t really use it for the HA setup of the MySQL Router.

Installing Pacemaker

The first step is to install pacemaker on all the machines we will use for our “MySQL Router Cluster”:

# yum install pacemaker pcs resource-agents

Now we need to start the pcsd service and enable it at boot (on all machines):

# systemctl start pcsd.service 
# systemctl enable pcsd.service

It’s time now to setup authentication, this operation is again …