Create mysql backup from server, download locally with scp and replace mamp database

Question

I'm creating a snippet to be used in my Mac OS X terminal (bash) which will allow me to do the following in one step:

1. Log in to my server via ssh
2. Create a mysqldump backup of my Wordpress database
3. Download the backup file to my local harddrive
4. Replace my local Mamp Pro mysql database

The idea is to create a local version of my current online site to do development on. So far I have this:

ssh server 'mysqldump -u root -p'mypassword' --single-transaction wordpress_database > wordpress_database.sql' && scp [email protected]:~/wordpress_database.sql /Users/me/Downloads/wordpress_database.sql && /Applications/MAMP/Library/bin/mysql -u root -p'mylocalpassword' wordpress_database < /Users/me/Downloads/wordpress_database.sql

Obviously I'm a little new to this, and I think I've got a lot of unnecessary redundancy in there. However, it does work. Oh, and the ssh command ssh server is working because I've created an alias in a local .ssh file to do that bit.

Here's what I'd like help with:

1. Can this be shortened? Made simpler?
2. Am I doing this in a good way? Is there a better way?
3. How could I add gzip compression to this?

I appreciate any guidance on this. Thank you.

Answer 1

That's the way I would do it too.

To answer your question #3:

Q: How could I add gzip compression to this?

A: You can run gzip wordpress_database.sql right after the mysqldump command and then scp the gzipped file instead (wordpress_database.sql.gz)

Answer 2

You can dump it out of your server and into your local database in one step (with a hint of gzip for compression):

ssh server "mysqldump -u root -p'mypassword' --single-transaction wordpress_database | gzip -c" | gunzip -c | /Applications/MAMP/Library/bin/mysql -u root -p'mylocalpassword' wordpress_database

The double-quotes are key here, since you want gzip to be executed on the server and gunzip to be executed locally.

I also store my mysql passwords in ~/.my.cnf (and chmod 600 that file) so that I don't have to supply them on the command line (where they would be visible to other users on the system):

[mysql]
password=whatever

[mysqldump]
password=whatever