I often import MySQL databases, and this can take a while. There is no progress indicator whatsoever. Can one be shown, somehow? Either records imported, MB imported, or tables imported... anything is better than just waiting. Anybody any idea?

I use this command:

mysql -uuser -p -hhost database < largefile.sql

Files are between 40-300 MB, and the host is within the local network.

  • 5
  • @sr_ I think pv is exactly what the questioner searches for. I just installed it on CentOS via rpmforge. If given the size-parameter it will even show an ETA. – Nils Apr 18 '12 at 19:13
  • pv did the trick indeed! If somebody can make this an answer, I can accept this! – User402841 Jun 14 '12 at 11:34

There is nice tool called pv

# On Ubuntu/Debian system
$ sudo apt-get install pv

# On Redhat/CentOS
$ sudo yum install pv

then e.g. you can use it like this

$ zcat dbpackfile.sql.gz | pv -cN zcat | mysql -uuser -ppass dbname

Please check UPDATE 2 for my latest version

ps: check this blog http://blog.larsstrand.org/2011/12/tip-pipe-viewer.html

UPDATE: seems like above link is broken but I found same article here http://blog.larsstrand.no/2011/12/tip-pipe-viewer.html

UPDATE 2: Even better solution with FULL progress bar. To do it you need to use 2 build in pv options. One is --progress to indicate progress bar and second is --size to tell pv how large the overall file is.

pv --progress --size UNPACKED-FILE-SIZE-IN-BYTES

..the problem is with .gz original file size. You need somehow get unpacked original file size information without unpacking it self, otherwise you will lose precious time to unpack this file twice (first time for pv and second time for zcat). But fortunately you have gzip -l option that contain uncompressed information about our gziped file. Unfortunattly you have it in table format so you need to extract before it can be use it. All together can be seen below:

gzip -l /path/to/our/database.sql.gz | sed -n 2p | awk '{print $2}'

Uff.. so the last thing you need to do is just combine all together.

zcat /path/to/our/database.sql.gz | pv --progress --size `gzip -l %s | sed -n 2p | awk '{print $2}'` | mysql -uuser -ppass dbname

To make it even nicer you can add progres NAME like this

zcat /path/to/our/database.sql.gz | pv --progress --size `gzip -l %s | sed -n 2p | awk '{print $2}'` --name '  Importing.. ' | mysql -uuser -ppass dbname

Final result:

Importing.. : [===========================================>] 100%

UPDATE 3: For quick usage create custom function.

mysql_import() {
  zcat $2 | pv --progress --size `gzip -l %s | sed -n 2p | awk '{print $2}'` --name '  Importing.. ' | mysql -uuser -ppass $1


mysql_import dbname /path/to/our/database.sql.gz

If you don't know where to put it, read this answer: https://unix.stackexchange.com/a/106606/20056

You can add functions among aliases. So you can use e.g. ~/.bash_aliases file.

  • this is awesome. – dave Sep 26 '17 at 21:08
  • 1
    A one liner with a better result is this can be this: pv --progress --name 'DB Import in progress' -tea /path/to/our/database.sql.gz | zcat | mysql -h db_host -u db_user -pdb_password db_name – Denis Pitzalis Jul 4 '19 at 9:21

Why so complicated ?

This works fine :

pv dump.sql.gz | zcat | mysql -u user -ppasswd database
  • Much simpler and cleaner! – donquixote Jan 13 '19 at 21:23
  • My first example is almost the same – sobi3ch Aug 12 '20 at 6:40

I always import databases from the MySql shell. It does not provide a progress indicator, but it does (quickly) scroll the actions it is performing so I know it's working.

# mysql -u user -p -h host database
> source /path/to/some/largefile.sql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.