This blog post discusses the limitations of the MySQL Clone plugin.

The MySQL clone plugin significantly simplifies the process of replica provisioning. All you need to do is:

• Ensure that the source server has binary logs enabled
• Grant appropriate permissions
• Execute the CLONE INSTANCE  command on the recipient

This works extremely easily when you provision a new replica that doesn’t have any data.

Due to its simplicity, you may want to use the clone plugin instead of a backup to restore a server that survives data inconsistency or corruption. E.g., after crash.

However, if you have data on your replica, you need to consider how you will recover if the CLONE INSTANCE  command fails with an error.

CLONE INSTANCE  command, by default, works as follows:

• Checks prerequisites on the replica
• Wipes out data …

As MySQL database administrators, we are well aware of the significance of implementing a primary key within a table. Throughout our careers, most of us have encountered situations where the absence of a primary key has led to operational challenges. Primary keys play an indispensable role in sound database design by uniquely identifying individual rows and significantly enhancing data retrieval, manipulation, and overall system performance.

From the MySQL documentation:

The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value.

It is common for tables to be inadvertently created without a primary key, often leading to regrettable consequences that we only recognize when issues …

ProxySQL is a high-performance SQL proxy, which runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.

Here, we will consider ProxySQL configured for async replication. Even when a replica is broken/stopped, ProxySQL still routes connections to replicas. It can be overcome by setting the appropriate value for mysql-monitor_slave_lag_when_null

…

As a MySQL database administrator, you’re likely familiar with the SHOW REPLICA STATUS command. It is an important command for monitoring the replication status on your MySQL replicas. However, its output can be overwhelming for beginners, especially regarding the binary log coordinates. I have seen confusion amongst new DBAs on which binary log file and position represent what in the replication.

In this guide, we’ll simplify the SHOW REPLICA STATUS output, focusing on the critical binary log coordinates essential for troubleshooting and managing replication.

The key binlog coordinates

Before we delve into the output, let’s understand the key binlog coordinates we’ll be working with:

• Master_Log_File: This is the name of the primary binary log file that the I/O thread is currently reading from.
• Read_Master_Log_Pos: It represents the …

If you’re a DBA, one of your “easiest” tasks is to stop/start MySQL during a maintenance window, but even that could lead to unwanted scenarios if you modify some dynamic parameters at some point in your instance.

Here’s a brief story of how this could happen, to make it clearer:

You’re a DBA managing a few MySQL servers. The application using one of them starts having issues on a Friday night, right before it’s time to leave; after a quick check, you notice the app is requesting more connections, and the hotfix is to up max connections; you change them dynamically, the fire is off, and so are you. Let your future self worry about finding the root cause and fixing it properly the following Monday.

But life happens; Monday is here with new challenges, and you already forgot about the connections issue… A few months later, a restart for MySQL is required, and surprise, surprise, right after …

In the world of data backup and security, physical backups play an extremely important role. Physical backup methods are faster than logical because they involve only file copying without conversion. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.

Physical backups are the backups that consist of raw copies of the directories and files that store database contents. In addition to databases, the backup can include any related files, such as log or configuration files. Now, since backup speed and compactness are important for busy, important databases, Percona’s open source physical backup solution – Percona XtraBackup (PXB), takes into account all these aspects and benefits MySQL world with its exceptional capabilities!

This blog post will walk you through how PXB …

MySQL is an outstanding open source transactional database used by most web-based applications and is very good at handling OLTP workloads. However, modern business is very much dependent on analytical data. ClickHouse is a columnar database that handles analytical workloads quickly. I recommend you read our previous blog, Using ClickHouse as an Analytic Extension for MySQL, from Vadim to know more about this.

In this blog post, I will show you how to replicate MySQL data in real-time to ClickHouse. I am going to use the MaterializedMySQL engine for this purpose. The blog post contains the following topics.

• MaterializedMySQL Engine – Overview
• Prerequisites for the Replication
• Replication setup
• Replication testing
• Understanding ReplacingMergeTree
• What …

Schema changes are required to add new features or to fix bugs in an application. However, there is no standard procedure to make the changes in a quick and safe manner. If the changes are not made considering the necessary precautions, you may face unwanted outages on the database that can cause serious problems to your business. In this blog post, I will delve into the most important things to consider while preparing a schema change.

Table size and concurrency

When assessing a schema change, one of the most important things to consider is the table size and concurrency. For small tables, the ALTER operation usually takes a few milliseconds up to a few seconds. Here is where concurrency plays another important role: if the table has periods of low concurrency during the day and the application allows having it locked for a few seconds or minutes, …

The auto-increment feature, which generates unique values for primary key columns, is an integral part of the database’s design. With the release of MySQL 8, a notable enhancement was introduced to the auto-increment counter. Compared to MySQL 5.7, this enhancement ensures that the maximum auto-increment counter value persists between server restarts, providing enhanced consistency and reliability in data management. In this article, we will look into this enhancement, compare MySQL 5.7 with MySQL 8, and provide practical examples to demonstrate the differences.

Auto-increment in MySQL 5.7

In MySQL 5.7, the auto-increment counter works as follows: when a new row is inserted into a table with an auto-increment column, the counter increments by 1, and the generated value is used for the primary key of the inserted row. This counter value is stored in memory and is not persistent across server restarts. As a result, if the server …

This post was originally published in June 2020 and was updated in September 2023.

While there is no magic bullet for MySQL performance tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

What are the Benefits of MySQL Performance Tuning?

MySQL performance tuning offers several significant advantages for effective database management and optimization. Let’s explore these benefits in …