Get a progress indicator when importing MySQL databases
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
}
usage:
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.
Why so complicated ?
This works fine :
pv dump.sql.gz | zcat | mysql -u user -ppasswd database
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;