Loading dumps via SSH, unpacking and sourcing them, all with a progress bar

Here is a hacky way to load dumps directly from the source server, without fully copying them over and extracting them first.

It may break horribly for you. This is the dark side of the force.

1. Install pipe viewer, if you don't have it already: sudo apt-get install pv
2. Know the location of the dump file on the remote server. We'll use /mnt/dumps/my_project.dump.bz2 in the example below.
3. Find out the size of the (bzipped) file in bytes on the server (ls -l). In our case, it's 1234567890.
4. Know your (local) MySQL root password. We'll use SECRET below.
5. Know your (local) target database name. For us: my_project_development.
6. Go wild:
   ^
   ssh user@example.com "cat /mnt/dumps/my_project.dump.bz2" | pv -s 1234567890 | bzip2 -d | mysql -uroot -pSECRET my_project_development

That will:

• Print the (bzipped) file to stdout on the server (cat), which sends it to you via SSH,
• pipe it through pv to show a progress bar (pv just sends the input stream back out again),
• unpack the bzipped stream,
• and finally pipe the unpacked dump to mysql.

Your output will be something like this:
1,63GB 17:14:40 [ 192kB/s] [==============================================> ] 81% ETA 3:49:30

Please note that this probably breaks if you do not use the same encoding on your machine, the remote machine, in your database, and when building the dump. If you can, you should always prefer using mysqldump -r and SOURCE.