Backup progress trying with pv command

MySQL & MariaDB Percona XtraBackup
1

Hi, I am trying to take backup using xtrabackup, and also using pv utility present in linux, but my ETA shows as 100000%

sudo docker run --name percona-xtrabackup-2.4 --volumes-from percona/percona-xtrabackup:2.4 xtrabackup --backup --target-dir=/tmp/basebkp --stream=xbstream --host=mysql-0 --user= --password= --port=3306 --socket=/var/run/mysqld/mysqld.sock | pv --size $(sudo du -sb /tmp/basebkp | awk ‘{print $1}’) --progress --format “Total Transferred: %b %t [%r] %p ETA: %e” | gzip > backup.xb.gz

230417 12:16:04 Finished backing up non-InnoDB tables and files
230417 12:16:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘18982767950’
xtrabackup: Stopping log copying thread.
.230417 12:16:04 >> log scanned up to (18982767959)

230417 12:16:04 Executing UNLOCK TABLES
230417 12:16:04 All tables unlocked
230417 12:16:04 [00] Streaming ib_buffer_pool to
230417 12:16:04 [00] …done
230417 12:16:04 Backup created in directory ‘/tmp/basebkp/’
230417 12:16:04 [00] Streaming
230417 12:16:04 [00] …done
230417 12:16:04 [00] Streaming
230417 12:16:04 [00] …done
xtrabackup: Transaction log of lsn (18982767950) to (18982767959) was copied.
230417 12:16:04 completed OK!
Total Transferred: 6.41GiB 0:05:19 [[20.5MiB/s]] [========================================================================================>] 100000% ETA:

I could see ETA is changing every time, sometimes even 100000 also. Any one has any idea of usage it?

2

So, --size shows the estimated actual data size. What you’re fetching is sizes of ‘–target-dir’ which should be the actual backup size.
Try replacing ‘/tmp/basebkp’ with ‘/var/lib/mysql’ (<-- datadir)
If you want the estimation based on last backup size, then you might use the byte size of backup.xb.gz known to you. But then you will have to move the “pv” command after “gzip” as you need to compare the progression on the compressed stream.

sudo docker run --name percona-xtrabackup-2.4 --volumes-from percona/percona-xtrabackup:2.4 xtrabackup --backup --target-dir=/tmp/basebkp --stream=xbstream --host=mysql-0 --user= --password= --port=3306 --socket=/var/run/mysqld/mysqld.sock | pv --size $(sudo du -sb **DATADIR** | awk ‘{print $1}’) --progress --format “Total Transferred: %b %t [%r] %p ETA: %e” | gzip > backup.xb.gz

You may also want to only print progress as an output and save the backup log in a file. You should be able to do that using:

sudo docker run --name percona-xtrabackup-2.4 --volumes-from percona/percona-xtrabackup:2.4 xtrabackup --backup --target-dir=/tmp/basebkp --stream=xbstream --host=mysql-0 --user= --password= --port=3306 --socket=/var/run/mysqld/mysqld.sock 2>backup.log | pv --size $(sudo du -sb **DATADIR** | awk ‘{print $1}’) --progress --format “Total Transferred: %b %t [%r] %p ETA: %e” | gzip > backup.xb.gz

Let me know how it goes.
Thanks,
K

1 Like