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

I remember running across a tutorial/blog post once about how to automatically setup back-channel on an ssh connection so that you could do:

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

Edit: Here's a similar article (the one I was thinking about is older than this one) --

article: http://codysoyland.com/2010/jun/6/ssh-tip-automatic-reverse-...

hn discussion: http://news.ycombinator.com/item?id=1409263