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.