Java Developer

Restore mysql backup with progress bar

I wanted to restore a large production database before in my home computer, to replicate a particular bug. Since the database dump is so large, it took a lot of time. Around 30 minutes. The issue with such operation is that you don't know if the MySQL process hanged-up, or if it is still normally progressing. A good solution is to have a progress bar.

Before I start with restoring a database, I wish to recap on how to create a data dump.

$ mysqldump -u [username] -p[password] [database_name] > dumpfilename.sql

For example:

$ mysqldump -u root -pSecret app_db > dumpfilename.sql

Normally, this is how we restore it:

$mysql
mysql> create schema app_db;
mysql> exit;
$ mysql -u root -pSecret app_db < dumpfilename.sql

If you want a progress bar, a simple trick is to install and use the pv command

apt-get install pv

And then use this for restoring

$ pv dumpfilename.sql | mysql -u root -pSecret app_db

You will have a nice progress bar at the bottom.
Note: When I optimized my MySQL database configuration, my restore time was reduced from 30 minutes to around 2 minutes. Check it out here.
Tags: backup, dump, mysql, progress bar, pv, restore