52

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.
Ryan
  • 14,682
  • 32
  • 106
  • 179

2 Answers2

142

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
Naftali
  • 144,921
  • 39
  • 244
  • 303
Ali
  • 5,021
  • 4
  • 26
  • 45
  • Thanks. Can you demonstrate how this would work in the context of unzipping and importing into MySQL to achieve meaningful progress on the whole process? – Ryan Oct 28 '13 at 04:26
  • 4
    # pv gunzip < /path/to/database1.sql.gz | mysql -uUSER -p database1; this will show you the progress bar like this: 96.8MB 0:00:17 [5.51MB/s] [==> ] 11% ETA 0:02:10 – Ali Oct 28 '13 at 04:38
  • Update: This solution is fantastic! As good as can be expected. The ETA indicator fluctuates more than I'd like, but it's to be expected as various processes slow down or speed up along the way. @factorde7 deserves major upvotes for this solution. I wish I knew of it years ago. – Ryan Oct 28 '13 at 14:24
  • I use this nearly every day now. I'm surprised this isn't garnering more upvotes for you @factorde7. Is `pv` just common knowledge that's somehow escaped my attention? – Ryan Oct 31 '13 at 14:32
  • Interesting ... pv upon `mysqldump` only yields an oscillating `<=>` instead of a progress bar. An extra `+50` points to anyone who shares a way to track `mysqldump` as perfectly as `pv` mysql import. – Ryan Nov 19 '13 at 01:41
  • 1
    @Ryan In order for `pv` to give you a progress indicator, it needs to know the expected size of the output. When you use `pv` on a file directly, it can gather that information. When you're piping commands, you have to supply it. This link contains a solution: https://dubbs.github.io/blog/2013/09/05/mysql-import-slash-export-progress-bar/ – Timothy Zorn Dec 28 '15 at 10:10
  • 1
    @Ryan `mysqldump -uroot -p database1 > /tmp/dump.sql && pv /tmp/dump.sql | gzip -9 > database1.sql.gz && rm /tmp/dump.sql` – Timothy Zorn Dec 08 '16 at 07:21
  • what's the difference - ```gzip -9``` and ```gzip``` ? – Akhil Jan 29 '21 at 04:04
3

I think I would perform a drop database before loading up the data, and after it happened I could run a continuous "du -sh" in the databases directory. If I know the size the original database directory (Why wouldn't I?) then I can use it as a progress monitor. Are the db's are droppable?

banyek
  • 238
  • 2
  • 8