mysqlsqlprogresssql-insertgunzip

Is there any way to show progress on a `gunzip < database.sql.gz | mysql ...` process?

Once a week I need to run a giant database update into my local development environment like so:

$ gunzip < /path/to/database1.sql.gz | mysql -uUSER -p database1 &
$ gunzip < /path/to/database2.sql.gz | mysql -uUSER -p database2 &
$ gunzip < /path/to/database3.sql.gz | mysql -uUSER -p database3 &

I try to run these overnight since it can take several hours to complete.

Can you help me come up with a way to show progress on these tasks?

Here are some guesses:

  1. Get the uncompressed filesize of the db and compare with my local db size
  2. Run show processlist in mysql to see what table it's currently inserting (my current method, but some tables are huge, and at least one db of mine only has one giant table so the bulk of the process is stuck in this table, leaving this option less than helpful)

All of the db.sql.gz files are standard gzipped mysqldumps, so I don't think I can build anything into the dumps to give me an update. (But I'm open to that if I'm missing something)

Bounty Rules

Answers must:

  1. Provide useful and reasonably accurate progress (either visual like scp (preferred!) or through a simple progress database table that could be accessed easily).
  2. Not break regular mysqldump export or regular gunzip ... | mysql import (for other engineers who may not use whatever you come up with)
  3. Not give my DBA a heart attack — so stay easy on special mysqldump or alternative mysql branch requests.
Solution

  • You may use -v : Verbose mode (show progress) in your command, or there's another method using Pipe Viewer (pv) which shows the progress of the gzip, gunzip command as follows:

    $ pv database1.sql.gz | gunzip | mysql -u root -p database1

    This will output progress similar to scp:

    $ pv database1.sql.gz | gunzip | mysql -uroot -p database1
  593MiB 1:00:33 [ 225kiB/s] [====================>              ] 58% ETA 0:42:25

    You can also use Pipe Viewer to monitor mysqldump:

    mysqldump -uroot -p database1 | pv | gzip -9 > database1.sql.gz

    If you don't already have pv, you can install it with:

    yum install pv

    or with macports

    sudo port install pv

    or with homebrew

    brew install pv