MySQL databases keep getting larger and larger. And the larger the databases get, the harder it is to backup and restore them.  MyDumper has changed the way that we perform logical backups to enable you to restore tables or objects from large databases. Over the years it has evolved into a tool that we use at Percona to back up petabytes of data every day. It has several features, but the most important one, from my point of view, is how it speeds up the entire process of export and import.

Until the beginning of this year, the latest release was from 2018; yes, more than two years without any release. However, we started 2021 with release v0.10.1 in January, with all the merges up to that point and we committed ourselves to release every two months… and we delivered! …

MySQL Group Replication is a plugin that helps to implement highly available fault-tolerant replication topologies. In this blog, I am going to explain the complete steps involved in the below two topics.

• How to convert the group replication member to an asynchronous replica
• How to convert the asynchronous replica to a group replication member

Why Am I Converting From GR Back to Old Async?

Recently I had a requirement from one of our customers running 5 node GR clusters. Once a month they are doing the bulk read job for generating the business reports. When they are doing the job, it affects the overall cluster performance because of the flow control issues. The node which is executing the read job is overloaded and delays the certification and writes apply process. The read job queries can’t be split across the cluster.  So, they don’t want that …

While I was working on my grFailOver POC, I have also done some additional parallel testing. One of them was to see how online DDL is executed inside a Group Replication cluster.

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Checking the Group Replication (GR) official documentation, I was trying to identify if any limitation exists, but the only thing I have found was this:

“Concurrent DDL versus DML Operations.  Concurrent data definition statements and data manipulation statements executing against the same object but on different servers is not supported when using multi-primary mode. During execution of Data Definition Language (DDL) statements on an object, executing concurrent Data Manipulation Language (DML) on the …

Where x is >= 22 😉

The Problem

There are few things your data does not like. One is water and another is fire. Well, guess what:

If you think that everything will be fine after all, take a look:

Given my ISP had part of its management infrastructure on OVH, they had been impacted by the incident.

As you can see from the highlight, the ticket number in three years changes very little (2k cases) and the date jumps from 2018 to 2021. On top of that, I have to mention I had opened several tickets the month before that disappeared. 

So either my ISP was very lucky and had very few cases in three years and sent all my tickets to /dev/null… or they have lost THREE YEARS of data.   

Let us go straight to the chase; they have lost …

This blog provides high availability (HA) guidelines using group replication architecture and deployment recommendations in MySQL, based on our best practices.

Every architecture and deployment depends on the customer requirements and application demands for high availability and the estimated level of usage. For example, using high read or high write applications, or both, with a need for 99.999% availability.

Here, we give architecture and deployment recommendations along with a technical overview for a solution that provides a high level of high availability and assumes the usage of high read/write applications (20k or more queries per second).

Layout

Components

This architecture is composed of two main layers:

• Connection and distribution layer
• RDBMS …

Chasing bugs can be a tedious task, and multi-threaded software doesn’t make it any easier. Threads will be scheduled at different times, instructions will not have deterministic results, and in order for one to reproduce a particular issue, it might require the exact same threads, doing the exact same work, at the exact same time. As you can imagine, this is not straightforward.

Let’s say your database is crashing or even having a transient stall.  By the time you get to it, the crash has happened and you are stuck restoring service quickly and doing after-the-fact forensics.  Wouldn’t it be nice to replay the work from right before or during the crash and see exactly what was happening?

Record and Replay is a technique where we record the execution of a program allowing it to be replayed over and over producing the same result. Engineers at …

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command.

PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).

However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, …

Nobody likes change, especially when that change may be challenging.  When faced with a technical challenge, I try to remember this comment from Theodore Roosevelt: “Nothing in the world is worth having or worth doing unless it means effort, pain, difficulty.”  While this is a bit of an exaggeration, in this case, the main concept is still valid.  We shouldn’t shy away from an upgrade path because it may be difficult.

MySQL 8.0 is maturing and stabilizing.  There are new features (too many to list here) and performance improvements.  More and more organizations are upgrading to MySQL 8 and running it in production, which expedites the stabilization.  While there is still some significant runway on 5.7 and it is definitely stable (EOL slated for October 2023), organizations need to be preparing to make the jump if they haven’t already. 

What Changed?

So …

In past versions of MySQL, there was often an ‘upgrade dance’ that had to be performed in starting up a newly upgraded MySQL instance with the previous version configuration file. In some cases a few deprecated options might no longer be supported in the newer server version, triggering an error and a subsequent shutdown moments after starting. The same thing can happen even outside of upgrade scenarios if a configuration change was made with a mistake or typo in the variable name or value.

As of MySQL 8.0.16 and later, there is now a ‘validate-config’ option to quickly test and validate server configuration options without having to start the server. Once used, if no issues are found with the configuration file, the server will exit with an exit code of zero (0). If a problem is found, the server will exit with an error code of one (1) for the first occurrence of anything that is determined to be invalid.

…

Optimizing MySQL performance and troubleshooting MySQL problems are two of the most critical and challenging tasks for MySQL DBAs. The databases powering applications need to be able to handle changing traffic workloads while remaining responsive and stable in order to deliver an excellent user experience. Further, DBAs are also expected to find cost-efficient means of solving these issues.

In this webinar, we will demonstrate the advanced options of Percona Monitoring and Management V.2 that enable you to solve these challenges, which are built on free and open-source software. We will look at specific, common MySQL problems and review them.

Please join Peter Zaitsev on Wednesday, April 14th, 2021, at 11 am EDT for his webinar Optimize and Troubleshoot MySQL using Percona Monitoring and Management (PMM).

…