I have the following:
mysqldump -u xxxx
-h localhost
--password=xxxxx databasename |
ssh [email protected] "dd of=httpdocs/backup`date +'%Y-%m-%d-%H-%M-%S'`.sql"
...which SSH's a mysqldump to a remote machine.
I need to compress the mysqldump before it is SSH'd as the dump is 500mb and its eating up my bandwidth allowance.
You can add the -C flag to the ssh call to automatically compress the transmitted data.
mysqldump ... | gzip -9 | ssh ...
or
mysqldump ... | bzip2 -9 | ssh ...
or, if you want it uncompressed on the other end
mysqldump ... | bzip2 -9 | ssh machine "bzip2 -d >..."
mysqldump ... | gzip -9 | ssh machine "gzip -d >..."
You need to call gzip between mysqldump and ssh, like:
mysqldump [mysql options] | gzip | ssh [ssh options]
I would recommend changing the saved file extension to ".sql.gz" as well.
This has already been answered and accepted, but I thought you might find this an interesting alternative.
Percona's OpenSource xtrabackup application will perform compressed (TAR) backups on the fly - along with lots of other interesting things.
I couldn't find an anchor on the page, but scroll down to "Compressed Backups".