Comment by gdee

  > ssh prod-db 
  > mysqldump what-i-need > file.sql
  > exit
  > scp prod-db:file.sql .

You can definitely do better than that with ssh:

  ssh prod-db "mysqldump what-i-need" > file.sql

If the dump is large and the network is the bottleneck, you can also do it like this:

  ssh prod-db "mysqldump what-i-need | p7zip" | p7zip -d > file.sql

Edit: Noste gave the right oneliner, sorry I didn't notice it at first. Consider mine only as an expansion of his second point.