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.
- 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