One problem I have experienced during logical exporting and importing databases is the tools don’t output any progress indicator (or anything at all really) and using the -v (verbose) switch outputs every single MySQL command being run from the import file. As, importing and exporting of databases  is one of the most frequent activity being performed , I Just felt the need of some tool that could help me check the approximate progress of import and export of databases going on.

So, I searched and fortunately could find a tool named Pipeviewer (PV) which allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA .

Installation :

On Centos and RHEL :

 
[root@vm1 vagrant]# yum install pv
	

[root@vm1 vagrant]# pv –version

pv 1.4.6 – Copyright(C) 2012 Andrew Wood

On Debian , Ubuntu and Linux Mint :

 
# apt-get install pv
	

OPTIONS :

pv takes many options, which are divided into display switches, output modifiers, and general options.

Below mentioned are some of the Display Switches :

    * Color in the red shows the Display Switches –

• -p, –progress :

Turn the progress bar on.  If standard input is not a file and no size was given (with the -s modifier), the progress bar cannot indicate how close to completion the transfer is, so it will just move left and right to indicate that data is moving.

 
144MiB 0:00:10 [13.9MiB/s] 
 
[==========================> 75% ETA 0:00:03
	

• -t, –timer :

This will display the total elapsed time that pv has been running for

 
144MiB 0:00:10 [13.9MiB/s] 

[=====================================>     75% ETA 0:00:03
	

• -e, –eta :

  This will attempt to guess, based on previous transfer rates and the   total data size, how long it will be before completion.  This option   will have no effect if the total data size cannot be determined.

 
144MiB 0:00:10 [13.9MiB/s] 


[=====================================>     75% ETA 0:00:03
	

• -r, –rate :

This will display the current rate of data transfer.

 
144MiB 0:00:10 [13.9MiB/s] 

[=====================================>     75% ETA 0:00:03
	

• -b, –bytes :

This will display the total amount of data transferred so far.

 
144MiB 0:00:10 [13.9MiB/s] 

[=====================================>     75% ETA 0:00:03
	

• -n, –numeric :

Numeric output. Instead of giving a visual indication of progress, pv will give an integer percentage, one per line .

• -a, –average-rate

 This will display the average rate of data transfer so far.

 
0:00:02 [56.4MiB/s] [60.5MiB/s] 

[==================================>  ] 60% ETA 0:00:01
	

Some of the options related to output modifiers are mentioned below :

• OUTPUT MODIFIERS  :

• -s SIZE, –size SIZE  :

Assume the total amount of data to be transferred if size  when calculating percentages and ETAs. The same suffixes of “k”, “m”, ”g” etc can be used with it .

• -i SEC, –interval SEC  :

Wait SEC seconds between updates.  The default is to update every second. Note that this can be a decimal such as low as 0.1 (sec )

• Using PV –

• Command for importing data into  database  :

Now , as an example  I would like to start  using PV while importing a file into   database.

 
[root@vm vagrant]pv pv_dump.sql |mysql --login-path=mypath sbtest
	

‍

 
144MiB 0:00:10 [13.9MiB/s]
[=====================================>     75% ETA 0:00:03
	

You can see the progress bar along with ETA , rate of data transfer ,   time elapsed , percentage completed .

• Command for importing database using numeric :

 
[root@vm vagrant] pv pv_dump.sql -n| mysql --login-path=mypath sbtest

- 6 

- 12 

- 17 

- 23 
	

• Command for exporting a Database Table   :

 
[root@vm1 vagrant] mysqldump --login-path=mypath sbtest sbtest4 |  pv  --progress  --size  200m  -t  -e  -r  -a > dump.sql
	

 
0:00:02 [56.4MiB/s] [60.5MiB/s] 

[==================================>  ] 60% ETA 0:00:01
	

You can get an approximate idea of how long your data export might take .

Summary :

This is a good terminal-based tool that you can use with tools that do not have the ability, to monitor the progress of your database operations such as importing or exporting and get an approximate estimate of how long it has taken, how near to completion it is, and how long it will be until completion.