MySQL Backup over SSH to Another Host

A problem that I’ve had a few times is to backup a MySQL database before decommissioning a server. Oftentimes the server is not large enough to accommodate a backup on the local disk store so a remote backup is required. This is not too hard, athough with hundreds of gigabytes or serveral terrabytes, it can take quite some time and be costly in bandwidth costs.

Here is how to do it:

mysqldump -u${msyqluser}  -p${mysql_password} ${mysql_database} \
   | gzip -c | ssh -l $user $host 'cat > /path/to/backup/location/file.sql.gz'

It’s not pretty, but it’s effective.

At this point, the astute reader is asking, why doesnt the regularly schedule backup get copied to the final destination? Or possibly, why aren’t backups being taken locally?

Great questions all, but that’s not always possible, depending on the customer and particular strategy in play.

Whenever I setup a new server, I try to satisfy some basic requirements that make my life easier for on-going maintenance:
1. backups in place
2. break/fix and performance monitoring in place
3. high availability (mysql slave, load balanced web server, etc..)
4. configuration management, backed by source control

If these 4 steps are followed and documented, life is much easier!