The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.

In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.

At the beginning, the mechanism, called mysql_old_password, was pretty insecure: it’s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the password column of …

The MySQL Document Store is an quick and easy to use NoSQL JSON Document Database that was designed to be easy to use.  But there is one thing that may not be obvious if you are working to modify multiple documents. 

So let us start with a very simple example collection of three documents.

>db.xyz.find()
{
    "a": 1,
    "_id": "00005eb2ba3c0000000000000004"
}
{
    "b": 2,
    "_id": "00005eb2ba3c0000000000000005"
}
{
    "c": 3,
    "_id": "00005eb2ba3c0000000000000006"
}
3 documents in set (0.0008 sec)

So we have three different documents and later determine we need to add the same key/value pair for all of them.

But the following will not work:

>db.xyz.modify().set("fizz","buzz")
…

As a Solutions Engineer at Percona, one of my responsibilities is to support our customers as they investigate new and emerging technologies. This affords me the opportunity to speak to many current and new customers who partner with Percona.

The topic of Kubernetes is becoming more popular as companies are investigating and adopting this technology. The issue most companies are encountering is having a stateful database that doesn’t fall victim to an environment tuned for ephemeral workloads. This obviously introduces a level of complexity as to how to run a stateful database in an inherently stateless world, as databases are not natively designed for that.

To make your life easier, as a part of the Percona Cloud-Native Autonomous Database Initiative, our engineering teams have built a …

This tutorial shows how you can install Nginx on an Ubuntu 20.04 LTS server with PHP 7.4 support (through PHP-FPM) and MySQL support (LEMP = Linux + Nginx (pronounced "engine x") + MySQL + PHP).

In Percona’s Managed Services, we start up and shut down MySQL servers all the time.  Over the years, we’ve seen various issues occur due to the way servers are shut down.  No matter if it is a situation where unexpected errors appear in the log or you’re stuck waiting for hours for a server to finishing shut down… Here are some steps we can take to minimize any trouble and prepare MySQL for shutdown.

This list of “super safe practices” was born from lessons we’ve learned in a wide variety of MySQL environments.

1. Stop Replication.

Under some (rare) circumstances, a slave may try to startup in the incorrect position. To help minimize this risk, stop the IO thread first so it’s not receiving new events.

STOP SLAVE IO_THREAD;

Wait for the SQL thread to apply all events, then stop it too.

SHOW SLAVE STATUS\G
STOP SLAVE SQL_THREAD;

This puts both of the …

On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.

Configuration

The binary log compression feature is controlled by two variables, one for enabling the feature and another to specify the compression level. These are summarized in the below table.

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

A second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. This is cleaner, but a little wordier.

We’ll get the keys in one table:

SELECT ord, keyname 
FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')
) AS keyTable;
 +------+----------+
 | ord  | keyname  |
 +------+----------+
 |    1 | RETRO    |
 |    2 | BOOSTER  |
 |    3 | SURGEON  |
 |    4 | GUIDANCE |
 |    5 | RECOVERY |
 +------+----------+

And the values in a second table:

SELECT ord, …

As Alkin and Ramesh have shown us in their Testing Percona XtraDB Cluster 8.0 with DBdeployer post, it is now possible to easily deploy an environment to test the features provided by the brand new release of Percona XtraDB Cluster 8.0.

We have also worked on creating a testing environment available for those that use Vagrant instead. Be it that it’s what you are used to working with, or that you want a proper VM for each instance, in particular, you can use the following commands to easily deploy a three-node cluster.

Requirements

Vagrant runs in Linux, Mac OS, and Windows, you just need to have the packages installed. Visit …

Establishing a many-to-many relationship between the tables in a database is usually done for ensuring efficient data processing and data integrity, as well as for database normalization and data analysis tasks. Since relational databases don’t allow implementing a direct many-to-many relationship between two tables, handling that kind of relationship can be an intimidating task. In […]

On Monday April 27th, MySQL released a much-anticipated patch release 8.0.20. Along with many bug fixes and improvements, a new property was introduced – binlog-transaction-compression. During our own internal testing we have discovered an incompatibility with our Continuent Tungsten products when this property is enabled.

The newly-released binlog-transaction-compression feature is really interesting because it compresses transaction payloads before being written into the binary logs, which in turn reduces the disk space overhead required for storage. I’m sure many users will be keen to implement this, however at this time the use of binlog-transaction-compression=ON will prevent Replication from functioning correctly.

This …