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 …

In this blog post, we will describe the improvements to Percona XtraBackup 8.0.33-28 (PXB), which significantly reduces the time to prepare the backups before the restore operation. This improvement in Percona XtraBackup significantly reduces the time required for a new node to join the Percona XtraDB Cluster (PXC).

Percona XtraDB Cluster uses Percona XtraBackup to do SST (State Snapshot Transfer) from one node to another. When a new node joins the cluster, SST is performed to receive the data from DONOR to the JOINER. JOINER uses PXB to stream the data directory from DONOR. JOINER must prepare the backup before using it. It is observed that when the DONOR has a huge number of tablespaces (one million),  XtraBackup on JOINER …

When it comes to backups, there are several options for saving backup files. You can choose to save them locally on the same server, stream them to different servers, or store them in object storage. Percona XtraBackup facilitates streaming through the use of an auxiliary tool called xbcloud.

STDOUT Datasink

This diagram displays the process of streaming a backup to object storage utilizing the current STDOUT datasink:

• XtraBackup spawns multiple copy threads. Each one will be reading a chunk of data from a specific file.
• Each copy thread will write the chunk of data to a pipe (aka STDOUT).
• Xbcloud will have a red thread that will be responsible for reading each chunk of data from STDIN. This chunk will be uploaded to object storage utilizing an async request, and a callback will be added to an …

Well, “easy” if you know just a tiny bit of C++.

MySQL is well known for its ease of use, being easy to install, easy to configure, and easy to maintain. What if there is something more that you’d like MySQL to do? How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL?

MySQL Loadable Functions would be the way to go. In this blog post, you’ll learn how to set up a build environment for compiling your own MySQL plugin to be loaded into MySQL as a function. Our function will implement a ULID generator using a C++ library from ChrisBove/ulid.

Creating the build environment

The first step is downloading the …

This blog was originally published in January 2022 and was updated in July 2023.

Working with hundreds of different customers, I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops.

A very common case is when a query uses a filter condition against a column that is involved in some kind of functional expression. An index on that column can not be used.

Starting from MySQL 8.0.13, functional indexes are supported. In this article, I will first explain an overview of indexes in MySQL and cover the MySQL CREATE INDEX before diving into showing what functional indexes are and how they work.

Introduction to MySQL Indexes