The clone plugin was introduced in MySQL 8.0.17 and offers a convenient method for cloning data from either a local or remote MySQL server instance. This cloning process creates a physical snapshot of the data stored in InnoDB, including schemas, tables, tablespaces, and data dictionary metadata. The clone plugin allows for easy provisioning of MySQL servers by generating a fully functional data directory. In this blog post, we will explore the steps to configure a new MySQL replica using the clone plugin.

Installation of the Clone Plugin

To load the clone plugin during server startup, you can utilize the –plugin-load-add option and specify the library file containing the plugin. Ensure that you add this option each time the server starts when using this plugin-loading method. To make this configuration change, add the following lines to your my.cnf file, adjusting the plugin library file name extension according to your …

As businesses grow and develop, the requirements that they have for their data platform grow along with it. As such, one of the more common questions I get from my clients is whether or not their system will be able to endure an anticipated load increase. Or worse yet, sometimes I get questions about regaining normal operations after a traffic increase caused performance destabilization.

As the subject of this blog post suggests, this all comes down to proper capacity planning. Unfortunately, this topic is more of an art than a science, given that there is really no foolproof algorithm or approach that can tell you exactly where you might hit a bottleneck with server performance. But we can discuss common bottlenecks, how to assess them, and have a better understanding as to why proactive monitoring is so important when it comes to responding to traffic growth.

Hardware considerations

The first thing we have to consider …

In InnoDB, the undo log and the redo log are two indispensable components that play a vital role in maintaining data integrity and ensuring transactional consistency. Both logs are crucial in the ACID (Atomicity, Consistency, Isolation, Durability) properties of database systems. Additionally, they are essential for the Multi-Version Concurrency Control (MVCC) mechanism. In this blog post, we will delve into the differences between the InnoDB undo log and redo log, exploring their significance and providing code examples to illustrate their usage.

InnoDB Undo Log

The undo log, also known as the rollback segment, is a crucial part of the InnoDB storage engine. Its primary purpose is to support transactional consistency and provide the ability to …

This blog was originally published in July 2017 and was updated in August 2023.

It’s a pretty common question around here, so let’s see what we can do about that.

So, What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer.

When partitioning in MySQL, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, and DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along …

The MySQL 5.7 End of Life is now two months away. Those of you who have waited may be wondering which is the best way to upgrade – an In-Place or Logical upgrade. An In-Place upgrade is where the binaries for 5.7 are replaced with the binaries for 8.0, and the upgrade is made to an existing instance. The Logical option is a new server that is loaded with the old data from a backup.

Generally, the In-Place upgrade will be faster as you do not have to perform a backup on the old instance and then run a restore on the new platform. Those with very large instances should choose this option.  And remember you should be using the four-byte UTF8MB4 character set, which most likely means you will be using more disk space, so arrange for the extra space BEFORE starting the upgrade.

Logical …

Restoring a MySQL database backup is a crucial task that can sometimes be time-consuming, especially for large databases. Monitoring the progress of the restore process is essential to estimate completion time and ensure everything is proceeding smoothly.

In this blog post, we will explore two distinct and effective methods to calculate the percentage progress of the MySQL restore process.

• Linux native Input/Output (I/O) statistics
• Pipe viewer utility

By following these approaches, we can effectively monitor the restoration process and manage your MySQL database restoration efficiently.

Monitoring MySQL database restore progress using Linux native Input/Output (I/O) statistics

We would use the /proc/<pid>/io file, which contains the IO statistics for each running process. For this particular case, we would use the pid of the MySQL restore process, which can provide …

In the world of database management, ALTER TABLE operations are a crucial part of modifying database structures. MySQL, a popular database management system, offers online operations since version 5.6, providing a convenient way to perform these alterations without locking the table. However, there are caveats. In this blog, we’ll explore the process of altering VARCHAR columns online, delving into insights gained while expanding the size of such columns.

To kick start our journey, let’s consider a table definition that requires the expansion of a VARCHAR column named “_varchar” to accommodate more data. Here’s the original table definition:

DROP TABLE IF EXISTS varchar_alter;
CREATE TABLE `varchar_alter` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `_varchar` VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

We execute the initial ALTER TABLE command:

mysql> ALTER TABLE test.varchar_alter CHANGE COLUMN …

Dynamic SQL is a desirable feature that allows developers to construct and execute SQL statements dynamically at runtime. While MySQL lacks built-in support for dynamic SQL, this article presents a workaround using prepared statements. We will explore leveraging prepared statements to achieve dynamic query execution, parameterized queries, and dynamic table and column selection.

Understanding prepared statementsMySQL support for server-side prepared statements, leveraging the efficient client/server binary protocol. A prepared statement is a functionality designed to execute identical or similar SQL statements repeatedly, achieving optimal efficiency in database operations.

Advantages of Prepared Statements

1. Reduced Parsing Overhead: Prepared statements minimize the overhead of parsing SQL queries each time they are executed. This is especially advantageous in database applications that …

On July 18th, Oracle released its first pair of MySQL LTS and Innovation releases. (For more, check out A Quick Peek at MySQL 8.0.34 and MySQL 8.1.0.) These releases were announced several months ago and gradually detailed closer to the release date. Today, we know from Oracle’s official blog post what to expect, as well as what the cadence and scope of the upcoming releases will be.

So, the next immediate question that comes to mind is: Is Percona going to follow the same release pattern as “upstream“ MySQL?

The short answer is “yes.”

We are proud to say that over the last several years, Percona delivered on its promise of providing the MySQL community a …

This is a Quick Peek at what Oracle delivered with MySQL 8.0.34 and 8.1.0 on July 17th, 2023. MySQL previously released software quarterly as part of a continuous delivery system where new features were made available. Many people loved access to the new features even if they could be risky, but others wanted stability and bug fixes only. Now there is a long-term support (LTS) version for the more conservative and the risk-averse folk that will have a roughly two-year lifespan between major releases.

The release with the new features will be called the Innovation series, and MySQL 8.1.0 is the first of these releases. And the LTS will be based on 8.0.34. Please note that comments in italics are mine and do not reflect the views of Percona and possibly not anyone else.

Quick reminder

The End of Life for MySQL 8.0 is in April of 2026, and the …