Remote mysqldump without Temp Files

TL;DR

$ ssh me@remoteserver 'mysqldump -u user -psecret production_database | \
    gzip -9' | gzip -d | mysql local_database

Use Some Pipes Bro

If you pass a command to ssh as an argument, it will be executed on the remote server instead of starting an interactive shell. The output from the remote command will be printed to STDOUT on your local machine:

$ ssh me@remoteserver 'ls -l /usr'
total 104
drwxr-xr-x   2 root root 36864 Apr 27 10:48 bin
drwxr-xr-x   3 root root  4096 Jul 30  2007 doc
drwxr-xr-x  66 root root 12288 Apr 26  2008 include
lrwxrwxrwx   1 root root     5 Nov  2  2010 lib -> lib64
drwxr-xr-x   4 root root  4096 Aug  9  2006 lib32
drwxr-xr-x  25 root root 20480 Jun 12  2009 lib64
drwxr-xr-x   4 root root  4096 Jan  2  2008 libexec
drwxr-xr-x   9 root root  4096 Jul 13  2009 local
drwxr-xr-x 161 root root  4096 Jun 12  2009 portage
drwxr-xr-x   2 root root  4096 Nov  3  2008 sbin
drwxr-xr-x  79 root root  4096 Jun 12  2009 share
drwxr-xr-x   3 root root  4096 Jun  1  2007 src

Since the output of the remote ls command is being printed through STDOUT of the ssh command, we can pipe it to another command running on the local machine. In this example, wc (word count) is running on our local machine, counting the lines of output from ls, which is running on the remote server:

$ ssh me@remoteserver 'ls -l /usr' | wc -l
      15

The output of mysqldump can be captured just as easily, and stored to a local file without creating a temporary backup of the data on the remote server:

$ ssh me@remoteserver 'mysqldump -u user -psecret production_database' > dump.sql

Now let’s load it directly into our local mysql instance instead of saving it to a file:

$ ssh me@remoteserver 'mysqldump -u user -psecret production_database' | \
    mysql local_database

If we have a lot of production data, it might be a good idea to compress it before it goes over the network to speed things up a bit (don’t forget to decompress it locally before sending it to msyql):

$ ssh me@remoteserver 'mysqldump -u user -psecret production_database | \
    gzip -9' | gzip -d | mysql local_database

I use this trick on most web app projects I work on to quickly setup staging and development databases.

We can also go the other way, with CAUTION:

$ mysqldump local_database | gzip -9 | ssh me@remoteserver 'gzip -d | mysql \ 
    -u user -psecret production_database'

Another use for this trick is getting a backup of a remote directory in tar.gz format locally:

$ ssh me@remoteserver 'tar czf - somedir/' > somedir.tar.gz