Backing up a MySQL Database remotely

So, you might want to backup a MySQL database remotely, like one of our customers did today. This is relatively simply utilizing the inbuilt mysqldump facility. This customer in particular was running varnish in front of his apache2 webserver so setting up phpmyadmin wasn’t entirely straight forward for this non technical customer. It’s easily achievable with something like;

Specific database

ssh -l user "mysqldump -mysqldumpoptions databasenamegoeshere | gzip -3 -c" > /localpath/localfile.sql.gz 

All databases

mysqldump -uroot -ppassword -h162.13.137.249 > backup.sql

The formatting of the command should look like

mysqldump -u root -p[root_password] -h [hostname] [database_name] > dumpfilename.sql