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