Just a quick how-to, to download, compile and use Sysbench on your RHEL/OL Linux box to test MySQL Server (note: compiling and using Sysbench is no mistery, the README file has all the instructions clearly explained, but I write this little guide to setup and use this tool in minutes).

Download and compile

The repository is hosted at: https://github.com/akopytov/sysbench. You can choose to download the latest master branch as usual or the latest stable. I have picked the latest stable. In order to compile and use certain MySQL library version, make sure mysql_config (used by sysbench auto configure to determine what libraries must be used) and a whole MySQL distribution is installed and/ or in the PATH.

If you have MySQL headers and libraries in non-standard locations (and no …

This quickstart shows you how to use the Azure portal to deploy a dedicated Percona Server for MySQL 8.0 drop-in replacement for MySQL that provides superior performance, scalability, and instrumentation. It also shows you how to connect to the server.

Prerequisites

Existing Azure subscription. If you don’t have a subscription, create a free Azure account before starting.

Create Percona Server for MySQL 8.0 Azure Instance

1. Go to the Azure portal to create a MySQL database using Percona Server for MySQL 8.0 image. Search for and select Percona Server for MySQL 8.0:
2. On the Percona Server for MySQL 8.0 page, press Create:
…

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );

INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM( …

For performance, having a warm InnoDB Buffer Pool is very important. What does that mean ?

A warm buffer pool means that the most used pages (working set) required by the production workload are already loaded in memory (in the buffer pool). If so, MySQL doesn’t need to read the pages from disk every time it requires the most used page and speeds up the process when the needed data is already in memory.

When you start MySQL, by default the InnoDB Buffer Pool is cold and the warm up process can even take days sometimes…

So, you can already deduce that restarting mysqld is a source of having a cold Buffer Pool as it will start empty. Another reason to have a non optimal Buffer Pool is to load it unnecessary pages. This can happen during a logical dump or load. If you regularly do a mysqldump for example (don’t forget that MySQL Shell dump & load is better if you do logical dumps, but introduces also the …

Starting with the release of proxysql-admin 2.0.15,  the proxysql-admin 2.x series can now encrypt the credentials needed to access proxysql and cluster nodes. This only applies to the proxysql-admin configuration, this does not change the ProxySQL config, so those credentials are still unencrypted.

The credentials file is the unencrypted file containing the usernames, passwords, hostnames, and ports needed to connect to ProxySQL and PXC (Percona XtraDB Cluster).

The proxysql-login-file tool is used to encrypt the credentials file. This encrypted file is known as a login-file. This login-file can then be used by the proxysql-admin and proxysql-status scripts.

Note: This feature requires OpenSSL v1.1.1 and above (with the exception of Ubuntu 16.04). Please see the …

In my talk at Percona Live 2021, “Creating Chaos in Databases”, I discussed how creating a controlled interruption in available resources (I used primary pod and network interruptions) allows us to test the stability of a database, and in our case, Percona XtraDB Cluster.

I also mentioned in the talk that my testing led to diagnosing a few unpleasant bugs, namely:

• PXC-3437: Node fails to join in the endless loop
• PXC-3580: Aggressive network outages on one node makes the whole cluster unusable
• …

I recently learned of some odd behavior using MySQL BETWEEN operator queries, filtering by a DATETIME column. I wrote about this over on Medium so I am sharing the post for any readers here who are interested…

Image by _Alicja_ from Pixabay 

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit …

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.

The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.

You build the little model with the following …

When organizing things helps to simplify life.

In the previous article, we start to explore dynamic privileges and the interaction with static ones. We also saw how to remove SUPER privilege from a DBA account. 

What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly.

Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes. 

Instead, we can use ROLES to group, assign, and revoke the correct privileges in a much easier way.

This is becoming even more important in MySQL with the advent of dynamic privileges.

What should we do to correctly use ROLES? Well first of all design.   …

When trying to make things better, make our life very complicated.

I was working on a Security Threat Tool script, when I had to learn more about the interaction between static and dynamic privileges in MySQL 8.

Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance the FLUSH operation now has dedicated Privileges and by scope. 

Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change in respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic)

Static privileges are the classical privileges available in MySQL …