Faster remote mysqldump

I am always looking for a faster way to export data from production to seed my development box. I try to keep my development cycle slim so I can iterate quickly. This little trick isn’t anything fancy but I felt it was clever enough to write about.

Create a file named `remote.sh`.

# this will be executed on the remote machine
# but exists on your local machine
mysqldump -h db.url -uUSERNAME -pPASSWORD | gzip -9

Run the next command in your terminal.

ssh user@host sh -s < remote.sh > FILE.sql.gz

So lets talk about what is going on here. We are executing the `sh` command on the remote machine and the `-s` option tells it to read commands from `stdin`. Then we pass it the contents of `remote.sh`. On the remote machine the `mysqldump` output is being piped through `gzip`, which is being streamed directly to your terminal. So we can then redirect the output of this remote command to a file on your local machine `FILE.sql.gz`. The result will be gzipped content streaming directly to you. Since SQL is just text it should compress a fair amount. I was able to pipe a `4.2gb` sql dump through `gzip` and ended up only downloading a `260mb` file locally. It took around 4 minutes to download ~1.083mb/s.

I could have very well just run and compressed the whole thing on the remote machine and used `scp` to download it but this felt cleaner since I didn’t need to clean up after my script.

This worked well for a relatively small dataset. But what do you do when you need to download much larger exports? You certainly don’t want to be running `mysqldump` on your production machine. In another post I will go over a more automated solution using AWS Data Pipelines to move data from MySQL to S3.