Performance Comparison of Local, Remote, and Percona XtraBackup 8.0 Backup Methods for MySQL

In MySQL daily operations, backup is an essential step, and the most commonly used tool is Percona XtraBackup. This article examines three key factors when using the tool: backup‑restore time, impact on primary server load during backup, and backup set file size.

Problem Background

The three factors are discussed in detail, emphasizing the need for short backup windows, minimal load on the primary server, and manageable backup file sizes, especially in environments such as finance where retention periods are strict.

1. Backup‑restore time

Shorter recovery time is preferred to avoid business interruption, especially during low‑traffic windows or emergency restores.

2. Impact on primary server load

Backup operations involve locking and file writes, which can increase I/O and potentially affect service stability.

3. Backup set file size

Larger instances generate larger backup sets, consuming significant storage; reducing size can lower costs.

For point 2, parameters such as --kill-long-queries-timeout , --ftwrl-wait-timeout , --ftwrl-wait-threshold , --use-memory , and --throttle can limit long queries, memory, and I/O, or backup from a replica.

The article focuses on backup time and backup set size, comparing three backup approaches:

Local backup

Cross‑machine remote backup

Percona XtraBackup 8.0

Local Test Environment

Binary packages were downloaded from the official Percona site; compatibility between XtraBackup and MySQL versions was verified.

Test Process

Before XtraBackup 8.0, the main program is xtrabackup with a soft link innobackupex . Example directory listing:

[root@localhost bin]# cd /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/
[root@localhost bin]# ll
lrwxrwxrwx 1 root root 10 May 3 2022 innobackupex -> xtrabackup
-rwxr-xr-x 1 root root 9963231 May 3 2022 xbcloud
... (other binaries) ...

XtraBackup supports streaming backups via the --stream option, offering tar and xbstream formats.

--stream=tar Scenario

Backup to local file without compression:

# Backup command
[root@localhost backup]# time /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/innobackupex \
    --defaults-file=/opt/mysql/etc/3310/my.cnf \
    --host=localhost --port=3310 --user=root --password='xxx' \
    --socket=/opt/mysql/data/3310/mysqld.sock \
    --stream=tar /data > /data/backup/data20240714.tar
... (output omitted) ...
real 0m22.780s

# Transfer file
[root@localhost backup]# time scp data20240714.tar root@xxx:/data/backup/
... 11GB transferred at 109.7MB/s ...
real 1m43.597s

Backup with compression (gzip):

# Backup command
[root@localhost data]# time /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/innobackupex \
    ... --stream=tar /data | gzip > /data/backup/data20240714.tar
... (output omitted) ...
real 6m26.829s

# Transfer file
[root@localhost backup]# time scp data20240714.tar root@xxx:/data/backup/
... 2.8GB transferred at 114.5MB/s ...
real 0m27.221s

Cross‑machine Backup (no compression)

# Backup command
[root@localhost restore]# time /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/innobackupex \
    ... --stream=tar /data | sshpass -p 'xxx' ssh root@xxx "cat - > /data/backup/data20240714.tar"
... (output omitted) ...
real 1m49.795s

--stream=xbstream Scenario

Local backup with compression:

# Backup command
[root@localhost backup]# time /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/innobackupex \
    ... --stream=xbstream --parallel=8 --compress --compress-threads=8 /data > /data/backup/data20240714.xbstream
real 0m22.789s

# Transfer file
[root@localhost backup]# time scp data20240714.xbstream root@xxx:/data/backup/
real 0m37.006s

Decompressing the xbstream file:

# Decompress
[root@localhost backup]# /data/urman-agent/bin/xbstream -x -C /data/restore/ < data20240714.xbstream
real 0m9.532s

# Remove original
[root@localhost backup]# /root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/innobackupex --decompress --remove-original /data/restore
real 0m21.597s

Percona XtraBackup 8.0 Scenario

From version 8.0.34 onward, ZSTD is the default compression algorithm and innobackupex is removed. Example command:

# Backup command (8.0)
[root@localhost restore]# time /root/backup/percona-xtrabackup-8.0.35-31-Linux-x86_64.glibc2.17/bin/xtrabackup \
    --defaults-file=/opt/mysql/my3310.cnf --host=localhost --port=3310 --user=root --password='xxx' \
    --socket=/opt/mysql/data3310/mysqld.sock --stream=xbstream --parallel=8 --compress --compress-threads=8 \
    --backup --target-dir=/data/backup | sshpass -p 'xxx' ssh root@xxx "/root/backup/percona-xtrabackup-2.4.26-Linux-x86_64.glibc2.12/bin/xbstream -x -C /data/restore/"
real 0m39.986s

Test Results Summary

Key conclusions:

When password‑less SSH or remote OS user access is possible, cross‑machine backup saves the most time.

The --stream=xbstream mode offers a good balance between backup size and duration and is recommended.

If disk space is extremely limited and longer backup time is acceptable, the --stream=tar combined with gzip provides the most compact backup.

References

[1] Percona XtraBackup download: https://repo.percona.com/yum/release

[2] Release notes for Percona XtraBackup 8.0.34: https://docs.percona.com/percona-xtrabackup/8.0/release-notes/8.0/8.0.34-29.0.html#release-highlights