In this article, we will demonstrate how to achieve asynchronous replication automatic source failover when our replica is a Percona Server for MySQL (PS) and the source is a Percona XtraDB Cluster (PXC) cluster, using virtual IP (VIP) managed by Keepalived.

Let us consider our architecture below with async replication from PXC to Percona Server for MySQL:

PXC               PS-MySQL
==============    ==============
node1      +----> node4
node2      |       
node3 -----+

Our goal is to set node3 as the async replication primary source. Then, upon failure, VIP can move to node2 and then node1 when node2 also fails. Once node3 becomes available again, the virtual IP should come …

Ensuring the security and resilience of your data hinges on having a robust backup strategy, and Percona XtraBackup (PXB), our open source backup solution for all versions of MySQL, is designed to make backups a seamless procedure without disrupting the performance of your server in a production environment.

When combined with the versatility of Docker containers, it becomes a dynamic duo, offering a scalable approach to data backup and recovery. Let’s take a look at how they work together.

Working with Percona Server for MySQL 8.1 and PXB 8.1 Docker imagesStart a Percona Server for MySQL 8.1 instance in a Docker container

Percona Server for MySQL has an official Docker image hosted on Docker Hub. For additional details on how to run an instance in a Docker environment, refer to this section in …

During an interview or while having general discussions, I have found some funny responses that can be easily classified as “Wrong Answers,” but at times, they’re thought-provoking or involve a deep meaning within. This blog is regarding some of the usual MySQL database conversations and responses, which can appear “wrong” or “funny,” but there’s actually more to them. I will share a selection of such seemingly “wrong” or whimsical responses and take a closer look at the valuable lessons and perspectives they offer.

Let the “MySQL Interview” begin.

Q: How will you improve a slow query?

A: Let’s not execute it at all. A query avoided is a query improved.

While this is a fact, we should carefully consider whether a query is necessary before executing it. Avoiding unnecessary queries and fetching only the required data can significantly optimize the query’s performance.

An …

Oracle recently made their quarterly releases with MySQL 8.0.35 and MySQL 8.2. This blog post is a quick look at the release notes to see what these new versions bring to the community. You’ll want to keep an eye on the deprecations in particular because some long-accepted behavior, including wildcards, will change eventually.

We get 83 and 50 bug fixes, respectively. We also get 17 and 33 deprecations. Those are good things, but those of us looking for an exciting new feature, function, or expansion are going to have to shrug and hope that the December release is more fruitful than October’s.

Sadly, the only really groundbreaking new code is the transparent read/write split built into the 8.2 MySQL Router. Any comments in italics are mine and not the …

This blog was originally published in January 2021 and updated in November of 2023.

As businesses and applications increasingly rely on MySQL databases to manage their critical data, ensuring data reliability and availability becomes paramount. In this age of digital information, robust backup and recovery strategies are the pillars on which the stability of applications stands.

In this blog, we will review all of the potential MySQL backup and restore strategies, the cornerstones of any application. When it comes to making the best choices for your specific setup, such as your topology and MySQL version, there are several options to explore, each requiring us to consider pertinent questions for informed decision-making.

Why Do MySQL Backups Matter?

MySQL backups play a pivotal role in safeguarding the integrity of your data, providing defense against various unforeseen calamities, hardware malfunctions, …

This blog post discusses using the  CLONE INSTANCE  command with the safety option  DATA DIRECTORY  when you do not have enough disk space to store two datasets.

In my previous blog post on the CLONE INSTANCE  command, The MySQL Clone Plugin Is Not Your Backup, I mentioned that using the option DATA DIRECTORY  helps to avoid situations where you need to re-initialize replica and clone-related settings from scratch in case of the clone operation failure.

MySQL Clone plugin simplifies new replica provisioning but does not simplify server restore after failure unless you are ready to re-install your MySQL instance from scratch.

However, when you clone a replica that already has a huge dataset, you may not have enough space for two datasets: one from the source …

Visual Studio Code (VS) supports memory dump debugging via C/C++ extension: https://code.visualstudio.com/docs/cpp/cpp-debug#_memory-dump-debugging. When MySQL generates a core file, the VS code simplifies the process of debugging. This blog will discuss how to debug the core file in VS code.

Installing c/c++ extension

We need to install the c/c++ extension. Here are the instructions for doing so. In Linux, you can press control-p, paste the command below, and then press enter.

ext install ms-vscode.cpptools

Once we install the c/c++ extension, we can find it in extensions.

Finding the binary file of MySQL

To open the core file, the GDB requires the binary file of MySQL. You can use the …

A common practice among DBAs and developers is to copy table data and .frm files from the data dictionary. They often set up batch jobs to automate the recovery of these tables. This capability is also utilized in disaster recovery scenarios, where individuals well-versed in .frm files can reconstruct their metadata as needed.

In MySQL 8.0, the information is presented within serialized objects within the dictionary. In the case of InnoDB tablespaces, this information is incorporated into the tablespace itself, creating a fusion of metadata and data primarily to enhance performance. MySQL writes a .sdi file to accommodate the serialized dictionary information for storage engines that lack support for this functionality.

Purpose of .sdi files

Serialized dictionary information (SDI) files store serialized metadata about various database objects, such as tables, indexes, and other schema-related details. This serialized data …

This blog post discusses roles in MySQL 8.0, which are named collections of privileges. Like user accounts, roles can have privileges granted and revoked as required.

Typically, we have multiple users with the same set of privileges. Previously, the only way to grant and revoke privileges to multiple users was to change the privileges of each user individually, which was time-consuming. To make it easier, MySQL provided a new object called role. A role is a named collection of privileges.

Here are the primary SQL commands that we will be discussing in relation to managing MySQL roles:

CREATE ROLE and DROP ROLE create and remove roles.
GRANT and REVOKE assign privileges to revoke privileges from user accounts and roles.
SHOW GRANTS displays privilege and role assignments for user accounts and roles.
SET DEFAULT ROLE specifies which account roles are active by default.
SET ROLE changes the active roles within the current session.
The …

Performing an operation is always challenging when dealing with K8s.

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. You can perform a DIRECT ALTER, use a tool such as pt-osc, or even, for certain cases where async replication is in use, perform changes on replicas and failover.

In this blog post, I’ll provide some thoughts on how schema changes can be performed when running MySQL on Kubernetes

I won’t focus on DIRECT ALTERs as it is pretty straightforward to apply them. You can just connect to the MySQL service POD and perform the ALTER.

But how can we apply changes in more complex scenarios where we may want to benefit from pt-osc, gain better control over the operation, or take advantage of the K8s features?

One convenient way that I’ve found …