pv | mysql

15 May 2013 in Tech

As a developer, I spend a lot of time importing backups generated with mysqldump into local mysql instances for testing. Most of the time
they're small enough that it's instant, but sometimes it takes quite a while if there's tables with millions of rows in there.

This is how I always used to import databases. Open up a mysql connection and stream "backup.sql" into it using input redirection.

vagrant@precise64:~$ mysql -u root db_name < backup.sql

Did you know that you can also pipe data into mysql using pipes? This also works:

vagrant@precise64:~$ cat backup.sql | mysql -u root db_name

This brings us on to pv, a utility that allows us to monitor the progress of data through a pipe. You can place it anywhere in a pipeline and see information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred and ETA.

pv also has a useful function that will copy each supplied file in turn to standard output. This essentially turns it into a replacement for cat that also provides a progress bar.

vagrant@precise64:~$ pv backup.sql | mysql -u root db_name
128kB 0:00:02 [43.5kB/s] [==============> ] 58% ETA 0:00:01

There's many more examples for pv available on the man page, just run man pv to see them.