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