Saturday, May 21st, 2022

Using pip viewer for large migrations

In WordPress, most migrations can be done easily:

  • Using a web-based utility like MigrateDB Pro or PhpMyAdmin,
  • Using a database front-end application such as TablePlus,
  • or even using mysql Command-line for importing SQL dumps.

Neither of these are a bad choice, but each presents its own set of challenges, especially with a large migration:

  • Web-based applications can timeout,
  • The front-end application may be lacking some functionality to gain insight into what’s going on (or what isn’t giving clear information like lock Its process is during inventory),
  • and terminal applications such as mysql Might work but give very little information on import status.

If you’re working with a relatively small dataset, either of the above options will be fine (and I’m usually partial to Tableplus) or command-line but when importing a database that was about 30GB in size, I found It needed something else to help.

  • A web application will time out,
  • A front-end application lacked timely or in-progress feedback,
  • A terminal application was waiting for this process to finish, with no insight into what was happening.

and right here pv Comes to play.

pip 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 common pipeline between two processes to give a visual indication of how fast the data is passing, how long it has taken, how close it is to completion, and an estimate of how close it is to completion. How long will it take to complete.

pip viewer homepage

In other words, whenever you’re sending information, such as someone’s content sql file, in another application, such as MySQL, pipe viewer Makes it possible to see what is happening between two applications.

So if you have a big migration to make or a big database to import, this lets you know:

  • how much time has passed,
  • how fast the 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 can take eight hours, it is better to know that a process has stalled.

setting up PV

Earlier this year, I wrote about how I’ve been using Homebrew to manage approx. All of applications on my system. So using it to set up PV is no different.

Assuming you’ve got Homebrew installed, just enter the following in your terminal:

~ brew install pv

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

Using PV with MySQL

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

From there, enter the following in your terminal:

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

It will ask you to enter the password for your database user and then start the import.

As always, this will start the standard procedure that MySQL follows; However, it will now be piped 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 up any output when it’s done.

Source link