Percona XtraBackup

Percona XtraBackup is a common open-source warm backup software for MySQL and MariaDB that makes non-blocking backups for InnoDB and XtraDB storage engines. It works with MySQL or MariaDB servers. For more information about the tool and some of its features and benefits, see About Percona XtraBackup in the Percona XtraBackup documentation.

This tool uses the physical migration approach. It directly copies the MySQL or MariaDB data directory and the files within it. For large databases, such as those larger than 100 GB, this can provide a significantly better restoration time than some other tools. You create a backup of the on-premises source database, migrate the backup files to the cloud, and then restore the backup on the new, target database instance.

The following diagram shows the high-level steps involved in migrating a database by using an Percona XtraBackup backup file. Depending on the size of the backup file, there are two options available for transferring the backup to an Amazon Simple Storage Service (Amazon S3) bucket in the AWS Cloud.

The following are the steps for using Percona XtraBackup to migrate a database to the AWS Cloud:

1. Install Percona XtraBackup on the on-premises server. If you’re using Amazon Aurora MySQL version 2 or Amazon RDS, see Installing Percona XtraBackup 2.4. If you’re using Amazon Aurora MySQL version 3, see Installing Percona XtraBackup 8.0 in the Percona XtraBackup documentation.

2. Create a full backup of the source MySQL or MariaDB database. For instructions for Percona XtraBackup 2.4, see Full backup. For instructions for Percona XtraBackup 8.0, see Create a full backup.

3. Upload the backup files to an S3 bucket by using one of the following approaches.

   Approach 3A – For database backup files that are less than 10 TB, you can transfer the backup files over the internet by using an approved service or tool in your organization, such as the following:

   • AWS Site-to-Site VPN

   • AWS Client VPN

   • AWS Direct Connect

   • Amazon S3 File Gateway (For more information, see Using Amazon S3 File Gateway to transfer backup files in this guide.)

   • AWS Command Line Interface (AWS CLI)

   Approach 3B – For database backup files that are larger than 10 TB, use AWS Snowball Edge to transfer the backup files to the S3 bucket. For instructions, see the following:

   • Transferring files using the Amazon S3 interface in the Snowball Edge documentation

   • Snowball uploader script in GitHub

4. From the S3 bucket, restore the backup files to the target database instance. For instructions, see the following:

   • For Aurora MySQL-Compatible Edition, see Migrating data from MySQL by using an Amazon S3 bucket in the Amazon RDS documentation.

   • For Amazon RDS for MySQL or for Amazon EC2, see Importing data into a MySQL DB instance.

   • For Amazon RDS for MariaDB or for Amazon EC2, see Importing data into a MariaDB DB instance.

5. (Optional) You can set up replication between the source database and the target database instance. You can use binary log (binlog) replication to reduce downtime. For more information, see the following:

   • Setting the replication source configuration in the MySQL documentation

   • For Amazon Aurora, see the following:

     • Synchronizing the Amazon Aurora MySQL DB cluster with the MySQL database using replication in the Aurora documentation

     • Using binlog replication in Amazon Aurora in the Aurora documentation

   • For Amazon RDS, see the following:

     • Working with MySQL replication in the Amazon RDS documentation

     • Working with MariaDB replication in the Amazon RDS documentation

   • For Amazon EC2, see the following:

     • Setting Up Binary Log File Position Based Replication in the MySQL documentation

     • Setting Up Replicas in the MySQL documentation

     • Setting Up Replication in the MariaDB documentation

Advantages

• Because Percona XtraBackup uses a physical migration approach, the restore process is typically faster than tools that use a logical migration approach. This is because the performance is limited by the disk or network throughput rather than the compute resources necessary for data processing.

• Because the restore process is a direct copy of the files from the S3 bucket to the target database instance, Percona XtraBackup files typically restore faster than backup files created with other tools.

• Percona XtraBackup is adaptable. For example, it supports multiple threads to help you copy files faster and supports compression to reduce the size of the backup.

Limitations

• Offline backup is not possible because Percona XtraBackup must have access to the source database server.

• Percona XtraBackup can be used only on systems with identical system architectures. For example, it is not possible to restore a backup of a source database running on Intel for Windows Server to an ARM for Linux target server.

• Percona XtraBackup isn't supported for Maria DB version 10.3, and it is only partially supported for Maria DB version 10.2 and version 10.1. For more information, see Percona XtraBackup Overview: Compatibility with MariaDB in the MariaDB knowledge base.

• You cannot use Percona XtraBackup to restore a source MariaDB database to a target MySQL database instance, such as Amazon RDS for MySQL or Aurora MySQL-Compatible.

• The total volume of data and number of objects you can store in an S3 bucket are unlimited, however, the maximum file size is 5 TB. If your backup file exceeds 5 TB, you can split it up into multiple, smaller files.

• When the innodb_file_per_table setting is off, Percona XtraBackup doesn't support partial backups that use --tables, --tables-exclude, --tables-file, --databases, --databases-exclude, or --databases-file. For more information for Percona XtraBackup version 2.4, see Partial backups. For more information for Percona XtraBackup version 8.0, see Create a partial backup.

Best practices

• To improve the performance of the backup process, do the following:

  • Copy multiple files in parallel by using --parallel=<threads>

  • Compress multiple files in parallel by using --compress-threads=<threads>

  • Increase memory by using --use-memory=<size>

  • Encrypt multiple files in parallel by using --encrypt-threads=<threads>

• Ensure that there is sufficient space on the source server to take the database backup files.

• Generate the database backup with the Percona xbstream (.xbstream) format file. For more information, see The xbstream binary overview in the Percona XtraBackup documentation.