The problem

Today, I need to export a MySQL database and then import it to a new server. The database is not too big, just a few GBs. But it was taking so long, and I wonder when this will be finished? By default, when you run mysqldump command to export the database and then mysql to import it, it doesn’t show any progress. I'm not sure whether the process is stuck or running.

root@server:~# mysqldump some_database > backup.sql

# Nothing to see here.
# The terminal shows nothing until the process is finished.

The solution

Then I discovered Pipe Viewer (PV). According to the website, Pipe Viewer is:

Pipe Viewer - is a terminal-based tool for monitoring the progress of data through a pipeline and modifying its flow. It can be inserted into any normal pipeline between two processes to give a visual indication of how quickly data is passing through, how long it has taken, how near to completion it is, and an estimate of how long it will be until completion. Data flow rate, error handling strategy, buffer size, and cache interaction can all be adjusted.

Let’s see how it works:

# Install the pv package
root@47be583c9aaf:/# apt update && apt install -y pv

# Let's export a database.
# Using mysqldump, there’s no "total expected size".
# While PV can show progress percentages only when it knows the total size.
# I know the DB size. So I tell PV using the "-s" option.
root@localhost:~# mysqldump some_database | pv  --size 3G > some_database.sql
36MiB 0:00:09 [65.8MiB/s] [========>                           ] 17% ETA 0:00:42

# Now let's import the database
# Now PV know the size of the DB dump, so I don't need the "-s" option.
root@47be583c9aaf:~# pv some_database.sql | mysql -p some_database
5.09GiB 0:06:47 [12.8MiB/s] [==================================>] 100%

Now I don’t have to worry, knowing when it will be finished and the process speed.

PV is not exclusive to MySQL-related activity. But it can also be used for other use cases. Such as monitoring the progress of file compression and decompression, copying a file, and many more.