30

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.

3
  • 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, 2012 at 19:13
  • pv did the trick indeed! If somebody can make this an answer, I can accept this!
    – User402841
    Jun 14, 2012 at 11:34

4 Answers 4

42

There is a 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

NOTE: Please check this blog http://blog.larsstrand.no/2011/12/tip-pipe-viewer.html for more insights.

NOTE: Even better solution with FULL progress bar. To do it you have to use two build-in pv options. One is --progress to indicate the progress bar and the 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 itself, 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 contains uncompressed information about our gzipped file. Unfortunately, it is in a table format so you need to extract it before it can be used it. All together can be seen below:

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

NOTE: This is the list of most common archive tools and methods how to extract a number of uncompressed bytes from those archives:

 tar -tvf database.sql.tar | awk '{print $3}' | paste -sd+ | bc

 unzip -Zt database.sql.zip | awk '{print $3}'

 unrar l database.sql.rar | tail -n2 | head -n1 | awk '{ print $1 }'

 7z l database.sql.7z | tail -n1 | awk '{ print $3 }'

Uff.. so the last thing you need to do is just combine it 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 progress 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%

For quick usage, you can create a custom function.

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

..and then use it like this:

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

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

NOTE: You can add functions among aliases, e.g. in ~/.bash_aliases file.

2
  • this is awesome.
    – dave
    Sep 26, 2017 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 Jul 4, 2019 at 9:21
33

Why so complicated ?

This works fine :

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

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;
3

sobi3ch's answer is great for most situations, but Pipe Viewer doesn't work well in use cases where a tty isn't available, like when monitoring a mysql docker container's initialization output or when you want to log the progress to a file.

Pipe Monitor (github) is an alternative designed to output updates to a log stream via STDERR. Disclaimer: I am the author.

Their basic functionality is very similar: Read from STDIN or a file. Pipe the contents to STDOUT. Show progress. However, whereas Pipe View uses terminal control sequences to update a visual progress bar on a single line, Pipe Monitor outputs text updates appropriate for non terminal applications.

Pipe Monitor supports the following basic options. Output is customizable via the --format option:

Usage: pm [--size SIZE] [--name NAME] [--format FORMAT] INPUT_FILE

Positional arguments:
  INPUT_FILE             Optional input file. If not provided input will be read from STDIN

Options:
  --size SIZE, -s SIZE   Size of input from STDIN. Ignored if using INPUT_FILE
  --name NAME, -n NAME   A NAME tag for this output. Will be pre-pended to default FORMAT string
  --format FORMAT, -f FORMAT
                         Output format string. Allowed keys: %name, %size, %time, %eta, %percent, %written, %buffered
  --help, -h             display this help and exit

Here is a comparison of the output of each in a non terminal environment.

Pipe Viewer (non terminal):

$ pv -nf testin > testout
40
70
77
84
90
96
100

Pipe Monitor:

$ pm testin > testout
Processed 0 bytes of 2456678400 (0% complete). 0 bytes buffered. Running 0s, eta: <unknown>
Processed 1750794240 bytes of 2456678400 (71% complete). 327680 bytes buffered. Running 2s, eta: 1s
Processed 2106937344 bytes of 2456678400 (85% complete). 700416 bytes buffered. Running 4s, eta: 1s
Processed 2419339264 bytes of 2456678400 (98% complete). 2871296 bytes buffered. Running 6s, eta: 0s
Processed 2456678400 bytes of 2456678400 (100% complete). 0 bytes buffered. Running 6s, eta: 0s
2
  • Really nice work! I gave your project a star and upvote your answer.
    – sobi3ch
    Jan 28 at 8:26
  • @sobi3ch, thank you for the support :-)
    – cmorris
    Jan 29 at 16:45

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.