Read more

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

Arne Hartherz
March 12, 2013Software engineer at makandra GmbH

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

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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.

Posted by Arne Hartherz to makandra dev (2013-03-12 17:35)