Friday, 6 May 2011

Viewing the MySQL dump import progress

A couple of years ago I wrote a patch for the MySQL command line client which shows the progress of a import as it happens (I also created a similar patch for mysqldump which later made it into Drizzle).  I don't have the blog archives from back then but Harrison Fisk commented suggesting I use a utility called 'bar' instead.

The 'bar' utility actually is a lot better than the patch I wrote and I highly recommend it when you are importing a large dump file.  To use it simply run:
shell> bar -if=data.sql | mysql

This will generate an output such as:



If you are using Ubuntu then it is a simple case of 'sudo apt-get install bar' to install it.  Enjoy!

9 comments:

  1. Bar is great. I wrote a utliity called pipemeter which is quite similar to bar, though not as widely available.

    Even better is pv. This one lets you do multiple pipes so you could do pv -cN gzip < data.sql.gz | zcat | pv -cN mysql | mysql

    http://spamaps.org/pipemeter.php
    http://www.ivarch.com/programs/pv.shtml

    I've used both in production for exactly what you describe above.. its quite nice to know you have 10 more minutes left to go get coffee. ;)

    ReplyDelete
  2. Seconded for pv, although I typically do
    pv -cN gzip data.sql.gz | gzip -d | pv -cN mysql | mysql

    If you use the funky < data.sql.gz redirect, it doesn't know the size of the file, when you pass the file directly, it does and gives you a eta.

    ReplyDelete
  3. I had a feeling people would comment with other tools to do similar things when I posted this. Cool stuff! :)

    ReplyDelete
  4. [...] Andrew Hutchings blogs about viewing the MySQL dump import progress as it happens and then talks about the bar utility. [...]

    ReplyDelete
  5. Jonas Oreland9 May 2011 09:15

    Anyone happen to know what fedora package is called for this command ?

    ReplyDelete
  6. Hey Jonas,

    I can't find a Fedora repo with it in. But this RPM should work: http://sourceforge.net/projects/clpbar/files/clpbar/bar-1.11.1/

    Let me know if it doesn't, should be easy enough to build one.

    ReplyDelete
  7. [...] Shared Viewing the MySQL dump import progress. [...]

    ReplyDelete
  8. zcat dump.gz | pv -s $( gzip -l dump.gz | tail -n1 | awk '{ print $2 }' ) | mysql -uuser -ppassword

    ReplyDelete