Xtrabackup backup to a different server

HI Team,

I have a server running with mysql with huge db of 1 TB.

Now, I want to take full backup through percona xtra backup, because incremental backup is easy with percona xtrabackup.

Now,if I initiate taking backup in that server,storage is getting consumed(I dont want this).
So, I am trying to initate backup in mysql server but the backup need to be stored in a remote server so the existing server storage is not effected.

I have tried the below commands, but even though I have specied remote server ip , it is getting stored in the existing server and storage is getting consumed.

xtrabackup --backup --remote-host=qrnet@10.16.124.208 /home/qrnet/temp

innobackupex --remote-host=qrnet@10.16.124.208 /home/qrnet/temp

so, 10.16.124.208 is the remote server where I need to backup my sql files so that existing server storage does not impact.

But , it is getting stored in the existing server if I perform above commands where the sql is existing.

How to store the backups remotely without effecting existing storage and are there any alternative ways?

1 Like

You must be using an old version of xtrabackup as I cannot find any references to the --remote-host option. In any case, that is not the correct method to stream a backup to another host. Check out our documentation for examples.

1 Like

From the Percona documentation:

On the destination host:

$ nc -l 9999 | cat - > /data/backups/backup.xbstream

On the source host:

$ xtrabackup --backup --stream=xbstream ./ | nc desthost 9999
1 Like

HI Team,

I have followed the below steps.

  1. stopped mysql service in target server.
    performed below command target server.
    nc -l -p 2112 | unpigz -c | xbstream -x -C /var/lib/mysql

2)on source server ,
xtrabackup --backup --stream=xbstream --parallel=4 --target-dir=/data/backup | pigz -c --fast | nc -w 2 ssh qrnet@10.16.12.114 2112

Now, it is getting failed with below error.what could be the issue, I could not find it.

root@e2e-78-247:~# xtrabackup --backup --stream=xbstream --parallel=4 --target-dir=/data/backup | pigz -c --fast | nc -w 2 ssh qrnet@ip 2112
nc: service “qrnet@ip” unknown
220328 06:19:56 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ (using password: NO).
220328 06:19:56 version_check Connected to MySQL server
220328 06:19:56 version_check Executing a version check against the server…
220328 06:19:56 version_check Done.
220328 06:19:56 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.7.33-0ubuntu0.18.04.1
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 500000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
220328 06:19:57 >> log scanned up to (85455085555)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2619 for qr_credit_track/jobCreditsTransaction, old maximum was 0
xtrabackup: Starting 4 threads for parallel data files transfer
220328 06:19:57 [02] Streaming ./qr_credit_track/jobServiceUserBalance.ibd
220328 06:19:57 [04] Streaming ./qr_credit_track/jobCreditsTransaction.ibd
220328 06:19:57 [03] Streaming ./qr_credit_track/jobServiceUserTransaction.ibd
220328 06:19:57 [01] Streaming ./ibdata1
220328 06:19:57 [04] …done
220328 06:19:57 [04] Streaming ./qr_credit_track/jobCreditsAccount.ibd
220328 06:19:57 [04] …done
220328 06:19:57 [02] …done
220328 06:19:57 [04] Streaming ./qr_credit_track/jobServicePricing.ibd
220328 06:19:57 [04] …done
220328 06:19:57 [03] …done
220328 06:19:57 [02] Streaming ./qr_credit_track/campaign.ibd
220328 06:19:57 [02] …done
220328 06:19:57 [02] Streaming ./qr_credit_track/impressionAudit.ibd
220328 06:19:57 [02] …done
220328 06:19:57 [02] Streaming ./qr_credit_track/idconfig.ibd
220328 06:19:57 [02] …done
220328 06:19:57 [02] Streaming ./quickride/PassengerRideEx.ibd
220328 06:19:57 [02] …done
220328 06:19:57 [02] Streaming ./quickride/UserAppDeviceDetails.ibd
220328 06:19:57 [02] …done
xtrabackup: Error writing file ‘UNOPENED’ (Errcode: 32 - Broken pipe)
[02] xtrabackup: Error: failed to copy datafile.
xtrabackup: Error writing file ‘UNOPENED’ (Errcode: 32 - Broken pipe)
[03] xtrabackup: Error: failed to copy datafile.

1 Like

Your #2 command is wrong. You are doing nc + ssh. Check your command again. If using nc on receiver side, only use nc on sender side.

1 Like