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 …

MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. The X DevAPI enables you to work with both relational and document data, and MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7.

MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. These are the utilities to take a backup; let’s see some basic commands.

• util.dumpTables – Dump one or more tables from single database
• util.dumpSchemas – Dump one or more databases
• util.dumpInstance – Dump full instance
• util.loadDump – Restore dump

1. Single table …

In Percona Managed Services, one key aspect of managing the databases is configuring backups. Percona XtraBackup is one of the best tools for performing physical database backups.

It is a good practice to compress the backups to save costs on storage and to encrypt the backups so those can’t be used if the files are compromised as long as you keep your encryption keys safe!

Percona XtraBackup supports both compression and encryption of the backups. When the data is too big (tens of TB and more), the backups can take several hours or even days to complete. In order to speed up the backup process along with the compression and encryption, we can use multiple threads. We can specify the number of threads to be used for each operation (copy, compression, …

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 …