As some of you likely know, I have a favorable view of ZFS and especially of MySQL on ZFS. As I published a few years ago, the argument for ZFS was less about performance than its useful features like data compression and snapshots. At the time, ZFS was significantly slower than xfs and ext4 except when the L2ARC was used.

Since then, however, ZFS on Linux has progressed a lot and I also learned how to better tune it. Also, I found out the sysbench benchmark I used at the time was not a fair choice since the dataset it generates compresses much less than a realistic one. For all these reasons, I believe that it is time to revisit the performance aspect of MySQL on ZFS.

ZFS Evolution

In 2018, I reported ZFS performance results based on version 0.6.5.6, the default version available in Ubuntu Xenial. The present post is using …

“It would be nice to have an option, that would allow to suppress the DEFINER statement in the CREATE VIEW statements generated by mysqldump. This would help when transferring data structures between databases with different security models.” 

TLDR;

Use mysqlpump with option

--skip-definer

instead of

mysqldump

. The Story

This was requested as MySQL Bug #24680 on Nov 29, 2006. This feature request got large Community support. Even if we cannot see the number of people who voted for this request, the number of comments is impressive.

The request is very reasonable:

mysqldump

is widely used during application development and it is a very common practice to migrate database …

The new MyDumper 0.10.7 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

For this release, we have added several features like WHERE support that is required for partial backups. We also added CHECKSUM for tables which help to speed up the restore of large tables to take advantage of fast index creation, and more.

New Features:

• Adding metadata file per table that contains the number of rows #353
• Adding –where support #347 …

One of the new features introduced in MySQL 8.0.24 was the ability to log all SQL statements that are issued in the MySQL Shell to the system log. This is a useful feature that can greatly assist in tracking who did what on the system.

Usage

The simplest way to utilize the new Shell logging feature is to simply start the MySQL Shell with the syslog option enabled like so:

$> mysqlsh --syslog --sql root@localhost

From this point forward all SQL entered in the MySQL Shell will be logged to the system log. For example, the following SQL is entered into the Shell:

MySQL  localhost:33060+ ssl  SQL > show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

We can now check the system log and verify that the statement above was indeed …

MySQL 8 brought many highly anticipated features, with support for user roles, a new shell, a more robust data dictionary, and better SQL support, just to name a few. There are lesser-known new features, however, that aim to reduce overall DBA workload and streamline management processes – and one of these is support for dual passwords, first implemented in MySQL 8.0.14. User accounts are now permitted to have dual passwords, with a designated primary and secondary. This makes it possible to seamlessly perform user credential changes even with a large number of servers, or with multiple applications connecting to different MySQL servers.

Historically, a MySQL credential change had to be timed so that when the password change was made and propagated throughout the database nodes, all applications that use that account for connections had to be updated at the same time. This is problematic for many reasons, but with database and application …

Some of you may use MySQL’s asynchronous replication feature called Semisynchronous Replication (aka semi-sync), and now with the MySQL Semi-Sync Summary Dashboard + Percona Monitoring and Management (PMM), you can see the most important metrics! Refer to the Install & Usage steps for deployment details (note you need Replication Set defined!).

What is Semisynchronous Replication

When enabled, Semisynchronous Replication instructs the Primary to wait until at least one replica has received and logged the event to the replica’s local relay log before completing the COMMIT on a transaction. This provides a higher level of data integrity because now it is known that the data exists in two places. This feature ensures a …

Don’t Try This at Home!

Disclaimer: The procedure described in this blog post is not officially supported by Percona XtraBackup. Use it under your responsibility. I tested and used it successfully, but your mileage may vary.

Note from the author: Wikipedia defines clickbait as text (or a link) that is designed to attract attention and to entice users to read that online content, with a defining characteristic of being deceptive. Maybe the headline of this post is a bit deceptive, as there is no Percona XtraBackup for Windows, but I hope you will not feel deceived after reading this blog post, I just felt that it was funny to use a clickbait-style while writing it.

He Couldn’t Believe it When He Read That Post!

Pep had to migrate a client database from Windows to Linux …

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 it 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 with respect to the components or plugin when activated. ( …

In the previous release of our Percona Distribution for MySQL Operator, we implemented one interesting feature, which can be seen as “self-healing”: https://jira.percona.com/browse/K8SPXC-564.

I do not think it got enough attention, so I want to write more about this.

As it is well known, a 3-node cluster can survive a crash of one node (or pod, in Kubernetes terminology), and this case is very well handled by itself. However, if there is a problem with 2 nodes at the same time, this scenario is problematic for Percona XtraDB Cluster. Let’s see why this is a problem.

First, let’s review if the first node goes offline:

In this case, the cluster can continue work, because Node 1 and Node 2 figure out …