Often while managing and creating new users, we use all the default options and tend not to use extra features provided by MySQL. These extra options could prevent a user from using all the resources and degrading the performance of MySQL. In this blog, we will discuss a few such features that will put resource restrictions on users.

max_user_connections

Sometimes, due to unprecedented growth or huge transactions, a single user makes too many connections, and the MySQL server gets starved of free connections. This blocks the DBA from logging into MySQL to fix it. To fix it in MySQL 5.7 and below, we have to do a restart. For MySQL 8, it can be done without a restart, but we have to configure the admin interface beforehand. You can read more about it here in Dealing With “Too Many Connections” Error in MySQL 8.

As a database administrator, have you ever been in a situation when your database confronted a brute force attack? A brute force attack can be launched against a user account in MySQL. MySQL replies with success or error based on supplied credentials, and the time required for the verification is almost the same in either case. Hence, an attacker can launch a brute force attack against a MySQL user account at a rapid rate and can try many different passwords.

According to cryptography, a brute-force attack consists of an attacker trying many passwords or passphrases with the hope of eventually guessing correctly. The attacker systematically checks all possible passwords and passphrases until the correct one is found.

It’s not just brute force attacks going on; the IT industry has recently seen a steady increase in distributed denial of service (DDoS) attacks. Have you also been targeted in such a type of connection flow on port …

At the end of 2021, I pushed the first Docker image to hub.docker.com. This was the first official image and since then, we have been improving our testing and packaging procedures based on Docker, CircleCI, and GitHub Actions. However, when I’m coding,  I’m not testing in Docker. But a couple of weeks ago, when I was reviewing an issue, I realized some interesting Docker use cases that I want to share.

Common use case

First, we are going to review how to take a simple backup with MyDumper to warm you up:

docker run --name mydumper 
     --rm 
     -v ${backups}:/backups  
     mydumper/mydumper:v0.14.4-7 
     sh -c "rm -rf /backups/data; 
          mydumper -h 172.17.0.5 
               -o /backups/data 
               -B test 
               -v 3 
               -r 1000 
               -L /backups/mydumper.log"

You will find the backup …

Historically MySQL is great in horizontal READ scale. The scaling, in that case, is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication.

However, those solutions do not offer the same level of scaling for writes operation.

Why? Because the solutions still rely on writing in one single node that works as Primary. Also, in the case of multi-Primary, the writes will be distributed by transaction. In both cases, when using virtually-synchronous replication, the process will require certification from each node and local (by node) write; as such, the number of writes is NOT distributed across multiple nodes but duplicated.

The main reason behind this is that MySQL is a relational database system (RDBMS), and any data that is going to be written in it must respect the RDBMS …

Sometimes crucial data sharing is avoided because of compliance rules, organizational policies, or numerous security concerns. The common use cases involve sharing pt-mysql-summary, pt-stalk, and other OS-related details to assist Support Engineers or any other third-party team troubleshoot database-related issues.

In this context, pt-secure-collect is a very important utility from Percona, which helps capture the required information securely and also provides aid in masking the existing information.

Pt-secure-collect helps in collecting, sanitizing, and encrypting data from various sources. By default, this utility collects the output with the help of pt-stalk, pt-summary, and pt-mysql-summary.

Let’s see how this tool …

Default settings can help you get started quickly – but they can also cost you performance and a higher cloud bill at the end of the month. Want to save money on your AWS RDS bill? I’ll show you some MySQL settings to tune to get better performance, and cost savings, with AWS RDS.

Recently I was engaged in a MySQL Performance Audit for a customer to help troubleshoot performance issues that led to downtime during periods of high traffic on their AWS RDS MySQL instances. During heavy loads, they would see messages about their InnoDB settings in the error logs:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4460ms. The settings might not be optimal. (flushed=140, during the time.)

This message is normally a side effect of a storage subsystem that is not capable of keeping up with the number of writes (e.g., IOPs) required by MySQL. This is …

In this blog post, we will see how to persist the password inside the ProxySQL mysql_users table in hashed format only. Also, even if someone stored the password in cleartext, we see how to change those into the hashed format easily.

Here we are just highlighting one of the scenarios during work on the client environment where we noticed that the ProxySQL mysql_users table had more than 100 user entries, but some of them were available/inserted into the clear text password, whereas some were inserted properly into hashed entries.

Before just explaining those simple commands that were used to fix those clear text entries into the hashed entry quickly, let’s see some more information about the ProxySQL mysql_users table and the password formats.

Password formats inside ProxySQL

ProxySQL is capable of storing passwords in two different formats within the mysql_users.password …

In this article, we will discuss errant Transaction /GTID and how we can solve them with the Orchestrator tool.

Orchestrator is a MySQL high availability and replication management tool that runs as a service and provides command line access, HTTP API, and Web interface. I will not go into the details of the Orchestrator but will explore one of the features that can help us solve the errant GTID in a replication topology.

What are errant transactions?

Simply stated, they are transactions executed directly on a replica. Thus they only exist on a specific replica. This could result from a mistake (the application wrote to a replica instead of writing to the source) or by design (you need additional tables for reports).

What problem can errant transactions cause?

The major problem it causes during a planned change in a MySQL replication topology is that the transaction is not present in the binlog and hence …

As we approach end of life for MySQL 5.7 later this year, many businesses are currently working towards upgrading to MySQL 8. Such major version upgrades are rarely simple, but thankfully there are tools that can help smooth the process and ensure a successful upgrade.

It should be noted that while the technical aspects of the upgrade process are beyond the scope of this blog post, it is crucial to create a testing environment to verify the upgrade before proceeding to upgrade your production servers, particularly with MySQL 8. 

As there is no procedure for downgrading from MySQL 8 other than restoring a backup, testing and validation are more critical than previous major version …

MySQL released version 8.0.33 on April 18th, featuring some attention-catching features.  This blog post is a quick review of the release notes looking for the exciting items, and comments in italics are solely my own.

User-defined collations are now deprecated and will be removed in a future version.  This is probably not a show-stopper for most and probably a scary situation for those dependent on them as there may not be an alternative.  Hopefully, UTF8MB4 is good enough.

The Performance Shema now has a Server Telemetry Traces service. This interface provides plugins and components a way to retrieve notifications related to SQL statements’ lifetime. We are directed to the Server telemetry traces service section in the MySQL Source Code documentation

The SSL library has been updated to OpenSSL version …