Server Administration & Management

How to monitor MySQL restore progress using PV

pv command allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA. To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.

A great application of pv is when you’re restoring large amounts of data into MySQL, especially if you’re restoring data under duress due to an accidentally-dropped table or database. The standard way of restoring data is something we’re all familiar with:

# mysql -uroot -p < database_backup.sql

The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they’re created, but that can be hard to follow. Toss in pv and that problem is solved:

# pv database_backup.sql | mysql -p
96.8MB 0:00:17 [5.51MB/s] [=======================>          ] 71% ETA 0:02:10

When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.

February 1, 2013 - Posted by | MySQL | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: