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
Great tip … I love linux one-liners to perform handy tasks. We loaded the above command into one of our deployment scripts so we can easily pull down a copy of the remote production database for local accurate debugging.
Thanks for TL;DR !
Commands typed with a leading space are not stored in bash history (keep that secret, secret).