Remote MySQL Backup with mysqlbackup and Percona XtraBackup

Introduction

The author, a DBA team member, revisits remote backup concepts after a previous article on mysqlbackup, and explores whether backup data can be streamed directly to a remote MySQL server.

Test Objectives

The goal is to back up to a remote server for disaster recovery, to save local disk space, to verify backup usability, and to create a replica by restoring directly on the remote host.

Environment Information

Two MySQL instances (A and B) are used, each running MySQL 5.7.25 on separate hosts (qin_2 and qin_4). The table below lists hostnames, IPs, instance names, versions, ports, backup users, and tool versions.

Hostname

IP

Instance

Version

Port

Backup User

mysqlbackup Version

XtraBackup Version

qin_2

10.186.64.13

MySQL A

5.7.25

3306

user_A

4.1.4

2.4.5

qin_4

10.186.64.16

MySQL B

5.7.25

3306

None

4.1.4

2.4.5

Test Scenario 1 – mysqlbackup

Introduction: mysqlbackup can stream backup data to a remote host and optionally restore it there. The backup-to-image option with --backup-image=- sends the backup to STDOUT.

Backup command (stream to remote host):

shell> mysqlbackup --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --user=user_A --password=password_A --socket=/opt/mysql/data/3306/mysqld.sock \
    --backup-image=- --backup-dir=/tmp/backup1 backup-to-image \
    | ssh [email protected] 'cat > /root/backups/my_backup.img'

# backup parameters
#   backup-image=- : send data to stdout
#   backup-dir=/tmp/backup1 : temporary directory (must not exist)
#   Ensure /root/backups exists on the remote host

After the command, the backup file appears on qin_4 under /root/backups . The restoration steps are:

# Stop and clean the remote instance
shell> systemctl stop mysqld_3306 && rm -rf /opt/mysql/data/3306/* \
    && rm -rf /opt/mysql/log/*

# Restore the backup image
shell> mysqlbackup --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --backup-dir=/root/backups --backup-image=/root/backups/my_backup.img \
    --datadir=/opt/mysql/data/3306 copy-back-and-apply-log

# Fix ownership
shell> chown -R actiontech-mysql:actiontech-mysql /opt/mysql/data/3306 \
    && chown -R actiontech-mysql:actiontech-mysql /opt/mysql/log/*

# Start the instance
shell> systemctl start mysqld_3306

Replication is then configured with:

mysql> CHANGE MASTER TO MASTER_HOST='10.186.64.13',
    MASTER_USER='universe_op',
    MASTER_PASSWORD='_Uq0%+58vv617$6N',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
mysql> START SLAVE;

Test Scenario 2 – mysqlbackup (backup & restore in one step)

The backup is streamed to the remote host and immediately restored using a single pipeline.

# Clean previous data on qin_4
shell> systemctl stop mysqld_3306 && rm -rf /opt/mysql/data/3306/*

# Backup on qin_2 and pipe to remote restore
shell> mysqlbackup --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --user=user_A --password=password_A --socket=/opt/mysql/data/3306/mysqld.sock \
    --backup-dir=/tmp/backup2 --backup-image=- --compress backup-to-image \
    | ssh [email protected] "mysqlbackup --backup-dir=/root/backup_tmp \
    --datadir=/opt/mysql/data/3306 --uncompress --backup-image=- copy-back-and-apply-log"

Test Scenario 3 – Percona XtraBackup (tar stream)

Percona XtraBackup can also stream backups using --stream=tar . SSH key‑based authentication is required.

# Generate and copy SSH key
shell> ssh-keygen
shell> ssh-copy-id [email protected]

# Backup command (tar stream)
shell> innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --user=user_A --password=password_A --socket=/opt/mysql/data/3306/mysqld.sock \
    --no-timestamp --stream=tar /tmp/backup_xtrabackup \
    | ssh [email protected] "cat - > /root/xtrabackup_$(date +%Y%m%d).tar"

After transferring, the tar file is extracted on the remote host and restored with innobackupex --apply-log and --move-back , followed by ownership correction and instance start.

Test Scenario 4 – Percona XtraBackup (xbstream)

The xbstream format is also supported.

# Create remote directory
shell> ssh [email protected] "mkdir -p /root/xtrabackup_20210402"

# Backup with xbstream
shell> innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --user=user_A --password=password_A --socket=/opt/mysql/data/3306/mysqld.sock \
    --no-timestamp --compress --stream=xbstream /tmp/backup_xtrabackup \
    | ssh [email protected] "xbstream -x -C /root/xtrabackup_20210402"

# Decompress and apply log
shell> innobackupex --decompress /root/xtrabackup_20210402
shell> innobackupex --apply-log /root/xtrabackup_20210402

# Remove *.qp files and move data back
shell> find /root/xtrabackup_20210402 -name "*.qp" | xargs rm -f
shell> innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --move-back /root/xtrabackup_20210402

# Fix ownership and start instance
shell> chown -R actiontech-mysql:actiontech-mysql /opt/mysql/data/3306 && \
    chown -R actiontech-mysql:actiontech-mysql /opt/mysql/log/*
shell> systemctl start mysqld_3306

Additional One‑Line Solution for XtraBackup

Using sshpass to supply the password, the entire stream‑to‑remote‑restore can be performed in a single command:

shell> innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf \
    --user=user_A --password=password_A --socket=/opt/mysql/data/3306/mysqld.sock \
    --no-timestamp --compress --stream=xbstream /tmp/backup_xtrabackup \
    | sshpass -p '666666a' ssh [email protected] "mkdir -p /root/xtrabackup_20210402 && \
    xbstream -x -C /root/xtrabackup_20210402 && \
    innobackupex --decompress /root/xtrabackup_20210402 && \
    innobackupex --apply-log /root/xtrabackup_20210402 && \
    find /root/xtrabackup_20210402 -name '*.qp' | xargs rm -f && \
    innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf --move-back /root/xtrabackup_20210402"

Summary

Both mysqlbackup and Percona XtraBackup support streaming backups to a remote MySQL server, and mysqlbackup can even restore directly on the remote host, making it simpler. XtraBackup requires additional steps such as decompression and xbstream handling, but it is also viable when SSH key authentication is configured.

References

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-backup-streaming.html

https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/streaming_backups_innobackupex.html