pv | mysql

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.

[email protected]:~$ mysql -u root db_name < backup.sql

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

[email protected]:~$ 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.

[email protected]:~$ 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.

Michael is a polyglot software engineer, committed to reducing complexity in systems and making them more predictable. Working with a variety of languages and tools, he shares his technical expertise to audiences all around the world at user groups and conferences. You can follow @mheap on Twitter

Thoughts on this post

Leave a comment?

Leave a Reply