In this blog, we will test a new security feature added in ProxySQL 2.0.9. Since a time ago, we have had the ability to block queries using mysql_query_rules table matching a group of queries using reg exp like a blacklist. Check out a previous blog for how to config “ProxySQL Firewalling” using the mysql_query_rules table.

You can improve a whitelist using the mysql_query_rules table, but it is difficult if you have hundreds of queries.

ProxySQL 2.0.9 introduces two new tables for the firewall whitelist algorithm:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('mysql_firewall_whitelist_rules','mysql_firewall_whitelist_users') ORDER BY name;
+--------------------------------+
| tables                         | …

Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.

• In MySQL community versions if you want to mask your data, You can go with a Maxscale load balancer.
• They introduced a new masking filter on the Maxscale 2.1 version.
• My colleague Prasanth written about column-level data masking. For reference click here Column Level Data Masking.
• In MySQL 8.0 Enterprise they have introduced a new security feature called data masking.
• This feature was introduced in MySQL 8.0.13 Enterprise Edition and it provides data masking and de-identification capabilities.

What is Masking ?

• Transformation of existing data to mask it and remove …

There are many users of MySQL in Cloud environment regions. It’s ideal that those MySQL instances are implemented with only private-ips in the respective Subnet. No public-ips. For that matter, all cloud infrastructure resources are ideally placed behind networks that provision only private-ips.  This limits any front-end attack surface for an application stack.  Certainly the… Read More »

In the last blog post of this series, we discussed in detail how Master Key encryption works. In this post, based on what we already know about Master Key encryption, we look into how Master Key rotation works.

The idea behind Master Key rotation is that we want to generate a new Master Key and use this new Master Key to re-encrypt the tablespace key (stored in tablespace’s header).

Let’s remind ourselves what a Master Key encryption header looks like (it is located in tablespace’s header):

From the previous blog post, we know that when a server starts it goes through all encrypted tablespaces’ encryption headers. During that, it remembers the highest KEY ID it read from all the encrypted tablespaces. For instance, if we have three tables with KEY_ID = 3 and one table with KEY ID = 4, it means that …

We will have a look at a new feature in MySQL 8.0 called binlog encryption. This feature is available from the MySQL version 8.0.14 or above.

Our previous blogs discussed about table space encryption in MySQL and Percona servers. In Mydbops, we are giving high importance about achieving security compliances.

The binary log records changes made to the databases so that it can be used to replicate the same to the slaves and also for the point in time recovery (PITR). So, it means that if someone has access to the binary logs, they can reproduce our entire database in many forms. As a DBA, we need to make sure that the binary log files are protected from users who are having access to the file system and also, log files need …

I'd like to continue reviewing MySQL bug reports from Community users that I considered interesting and subscribed to. Unlike in the previous post in this series, I am not going to check test cases on any competitor product, but will use only recently released MySQL 5.7.29 and 8.0.19 for checks, if any. This time I'll concentrate on bugs reported in November 2019.

As usual, I mostly care about optimizer, InnoDB and replication related bugs. Here is the list:

• Bug #97476 - "Range optimizer skips rows". This bug reported by …

The Case

Securing MySQL is always a challenge. There are general best practices that can be followed for securing your installation, but the more complex setup you have the more likely you are to face some issues which can be difficult to troubleshoot.

We’ve recently been working on a case (thanks Alok Pathak and Janos Ruszo for their major contribution to this case) where MySQL started becoming unavailable when threads activity was high, going beyond a threshold, but not always the same one.

During that time there were many logs like the following, and mysqld was becoming unresponsive for a few seconds.

2019-11-27T10:26:03.476282Z 7736563 [Note] Got an error writing communication packets
2019-11-27T10:26:03.476305Z 7736564 [Note] Got an error writing …

Starting with Percona Server for MySQL 8.0.17, Percona Server ships with a data masking plugin, using the same API as the MySQL Enterprise Masking and De-identification feature. This plugin was developed by Francisco Miguel Biete and was submitted to Percona Server as a community contribution. He also wrote an excellent post describing his contribution for Percona’s Community Blog titled Percona Server for MySQL 8.0 – New Data Masking Feature and the full list of functions can be found in the documentation of the MySQL Enterprise plugin. …

In the previous blog post of this series, MySQL Encryption: Talking About Keyrings, I described how keyrings work. In this post, I will talk about how master key encryption works and what the pros and cons are of using envelope encryption such as a master key.

The idea behind envelope encryption is that you use one key to encrypt multiple other keys. In InnoDB, this “one key” is the master encryption key and the “multiple other keys” are the tablespace keys. Those tablespace keys are the ones that are actually used to encrypt tablespaces. Graphically it can be presented like this:

The master key resides in the keyring, while encrypted tablespace keys reside in tablespace headers (written on page 0 of a tablespace). In the picture above:

Table A is encrypted with key 1. Key 1 is encrypted …

It has been possible to enable Transparent Data Encryption (TDE) in Percona Server for MySQL/MySQL for a while now, but have you ever wondered how it works under the hood and what kind of implications TDE can have on your server instance? In this blog posts series, we are going to have a look at how TDE works internally. First, we talk about keyrings, as they are required for any encryption to work. Then we explore in detail how encryption in Percona Server for MySQL/MySQL works and what the extra encryption features are that Percona Server for MySQL provides.

MySQL Keyrings

Keyrings are plugins that allow a server to fetch/create/delete keys in a local file (keyring_file) or on a remote server (for example, HashiCorp Vault). All keys are cached locally inside the keyring’s cache to speed up fetching keys. They can be separated into …