Using Pipe Viewer for Large Migrations

Using Pipe Viewer for Large Migrations

In WordPress, most migrations can be easily done by:

  • Using a web-based utility such as Migrate DB Pro or PhpMyAdmin,
  • Using a database front-end application like TablePlus,
  • Or even using the mysql command-line for importing an SQL dump.

None of these are bad options but each present their own set of challenges especially with large migrations:

  • web-based applications can timeout,
  • front-end applications may lack some functionality for getting insight into what’s going on (or not giving clear information on what something such as a lock is during its process list),
  • and terminal applications like mysql can do the job but give very little insight on to where to status of the import stands.

If you’re working with a relatively small dataset, then any of the above options would be fine (and I’m usually partial to TablePlus 🙂) or the command-line but when importing a database that was roughly 30GB in size, I needed something else to help.

  • A web application would time out,
  • A front-end application lacked feedback on time or progress,
  • A terminal application was just waiting for the process to finish with no insight as to what’s going on.

And this is where pv comes into play.

Pipe Viewer for Large Migrations

pv or Pipe Viewer is:

a terminal-based tool for monitoring the progress of data through a pipeline. It can be inserted into any normal pipeline between two processes to give a visual indication of how quickly data is passing through, how long it has taken, how near to completion it is, and an estimate of how long it will be until completion.

Pipe Viewer homepage

In other words, whenever you’re sending information, like the contents of an sql file, into another application, like MySQL, Pipe Viewer makes it possible to see what’s happening between the two applications.

So if you have a large migration to make or a large database to import, this gives you insight into:

  • how much time has past,
  • how fast data is passing in terms of bytes per second,
  • and a progress bar of how much time is left.

Speaking from experience, even though a process may take eight hours, it’s better than knowing that a process has hung.

Installing PV

Earlier this year, I wrote about how I’ve been using Homebrew to manage almost all of the applications on my system. So using it to install PV is no different.

Assuming you have Homebrew installed, just enter the following in your terminal:

~ brew install pv

And once that’s done, you’ll see the usual output from Homebrew and you’ll be ready to start using it.

Using PV with MySQL

Let’s say you have a directory structure where a database export resides in acme/export.sql. Next, you want to import the database. You may have an empty database with no tables or no database at all; it shouldn’t matter assuming the export is properly formatted.

From there, enter the following in your terminal:

~ pv acme/export.sql | mysql -u root -p

This will ask you to enter the password for your database user and will then begin the import.

As usual, this will begin the standard process MySQL follows; however, it’ll now be piped into pv and will show something like the following:

19.4GiB 5:08:31 [0.00 B/s] [==========>          ] 73% ETA 1:50:07

Which is obviously much more helpful than waiting for a process to give any output when it’s done.

Advertisement

About The Author

Tom

Archives