mysql, gzip and ssh

I'm starting to really like scripting in Linux.  I've been trying to come up with a simple backup strategy for my MySql databases and the command line tools are very well thought out and flexible.  It turns out I only need to use mysqldump to generate a huge SQL script that can be run to recreate your database:

mysqldump -u user -p password MyDatabase > MyDatabase.sql


That's great, but it takes up a lot of drive space. To solve that, just pipe the output to gzip.

mysqldump -u user -p password MyDatabase | gzip > MyDatabase.sql.gz


So now I have a compressed backup of my database, but I want to store the backup on a different machine. I could use scp to copy the files, but the ssh command supports both remote command execution and I/O redirection.

mysqldump -u user -p password MyDatabase | gzip | ssh backup.server "gunzip > MyDatabase.sql.gz"

If I want to restore from a backup file I can use the same ssh redirection.

ssh backup.server "cat MyDatabase.sql.gz" | gunzip | mysql -uroot MyDatabase

You can also use these same command line tools to easily copy databases between MySql servers while compressing the data before sending without ever writing anything to a file.