When we need to upgrade from MySQL 5.7 to MySQL 8, we could choose to do an in-place upgrade or set up another MySQL 8 server(s) from the existing running MySQL 5.7 replica.

This article will explain how to set up MySQL 8 as a replica from an existing MySQL 5.7 server with Percona XtraBackup.

In my lab, we have two test nodes:

PRIMARY:  mysql57 (192.168.56.111)    with   Percona Server 5.7 and  xtrabackup 2.4 installed
REPLICA:  mysql8    (192.168.56.113)   with  Percona Server 8  and   xtrabackup 8.0 installed

0. Pre-flight check with MySQL upgrade checker utility

Let’s run the MySQL upgrade checker utility to verify whether MySQL 5.7 server instances are ready for an upgrade.

MySQL  localhost  JS > util.checkForServerUpgrade('root@localhost:3306', {"password":"####", …

Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with ​Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.

Type of locks taken by Percona XtraBackup

Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is …

Amazon instance profiles are used to pass IAM roles to an EC2 instance. This IAM role can be queried using EC2 instance metadata to access an S3 bucket. Please check Amazon’s Official Documentation for more information.

Today we are happy to announce that starting with Percona XtraBackup 8.0.31-24, xbcloud can read instance metadata and fetch credentials from an instance profile, utilizing it to authenticate against an S3 bucket. Xbcloud is a tool part of Percona XtraBackup and allows you to upload and download backups to Amazon S3 storage.

How it works

Configure your EC2 instance …

Recently, we performed a database engine major version upgrade in one of our customers’ environments from MySQL 5.7.26 to 8.0.27. After this version upgrade, we experienced issues with backups and replication for one of the nodes.

In this article, I will explain these issues in detail and recommend a way to take backups from a replication environment.

To begin with, we upgraded all the database nodes from 5.7.26 to 8.0.27 and as a recommended way we have a backup set-up from one of the replica nodes. Physical backups are being taken using Percona XtraBackup (PXB) so it does not lock the database during the backup.

With MySQL 5.7, a backup was taken using PXB 2.4. Due to the new data dictionaries, redo log and undo log in MySQL 8.0, we also upgraded PXB to 8.0.27 to avoid …

Taking a MySQL backup using Percona XtraBackup (PXB) consists of basically two steps: 1) take the backup and 2) prepare the backup.

Briefly speaking, taking a backup means that PXB will copy all of the files from your instance and transfer them to another location. While it does the copy, it spawns a thread that will monitor the InnoDB redo log (WAL/transaction log) and store a copy of all the new redo log entries generated by the server during the backup.

Before restoring the backup into a new instance, users have to prepare the backup. This operation is the same as the crash recovery steps that the MySQL server does after a server crash.

It consists of reading all the redo log entries into memory, categorizing them by space id and page id, reading the relevant pages into memory, and checking the LSN number on the page and on …

Having a backup of your database is like insurance, you have to pay a monthly price to ensure you have a service available when you need to. When talking about backups, the storage required to keep your backups is what comes into factor when talking about price, the bigger your backup, or the bigger the retention period, the more it will cost.

Compressing your backups is a common practice to reduce this cost. Currently, Percona XtraBackup (PXB) has support for two compression algorithms: quicklz (which is an abandoned project and will soon be deprecated in PXB) and LZ4.

Today we are glad to introduce support for a new compression algorithm in Percona XtraBackup 8.0.30 – …

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.

Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column …

Earlier last week, Oracle released their Q2 releases series. Unlike previous releases, backward compatibility has now been broken with previous versions of MySQL.

MySQL 8.0.29 extended the support for the online DDL algorithm INSTANT. Prior to 8.0.29 only adding columns to the end of the table was supported.

In 8.0.29, this functionality was extended to allow the INSTANT algorithm the ability to add columns in any position of the table as well to drop columns. This new functionality required the redo log version to increase and new redo log types to be added, thus making it incompatible with older versions of the MySQL server and also older versions of Percona Xtrabackup. Please note that an in-place minor version downgrade of …

KMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management.

Percona Server for MySQL 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below.

Install Hashicorp Vault Enterprise

We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable …

A little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here:

Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup …