If you’ve been working with PHP/MySQL based websites for any amount of time, you’ve probably heard about the MySQL command line tools for importing and exporting your database.
The syntax is quite simple, for exports you run
mysqldump -udbuser -p dbname > dbfile.sql
which exports the database to a file on the local machine. For imports you run
mysql -udbuser -p dbname < dbfile.sql
which imports the contents of the local file (dbfile.sql) into the empty database.
This is usually the quickest way to export a database from one location to import it into another.
One problem I’ve experienced from time to time is exporting and importing large databases. By default the tools don’t output any progress indicator (or anything at all really), and using the -v (verbose) switch outputs every single MySQL command being run from the import file, which is like watching the code in the Matrix.
Fortunately, there is a way you can run imports and exports and show a progress bar, with Pipe Viewer.
Pipe Viewer is a terminal-based tool for monitoring the progress of data through a pipeline. The Pipe Viewer homepage has all the install commands for the various operating systems, and Ubuntu has an official package for it, so you can install it through apt.
sudo apt-get install pv
Once pipe viewer is installed, you merely need to make two small changes to you import and export commands.
pv dbfile.sql | mysql -udbuser -p dbname
mysqldump -udbuser -p dbname | pv -W > dbfile.sql
And viola! You will be presented with a progress monitor that shows file size imported, time taken, percentage progress. Much better than staring at a blank screen.
Leave a Reply