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 1.1.1.1 "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

2 thoughts on “Backing up a MySQL Database remotely

  1. What’s the point of invoking to commands via SSH in the first one?

    just use the below:

    mysqldump -u -p -h > /path/to/dump.sql

  2. Hey Blake,

    Good question man. I included 3 ways to do it just randomly and to show there were some different approaches. Perhaps if you are on the remote you would prefer the oneliner using ssh, but mainly you would use ssh -l for automation of tasks (namely ones that have to be executed in a specific order). I admit that this a very ‘oldschool’ way of thinking. And in this simple case it doesn’t really apply, but it’s worth knowing there’s different ways to do the same thing, because sometimes one has good fit wheras other might not.

    There is stuff like ansible for that now, and, in this example there is no real reason why you would need to use ssh, unless you wanted to validate or log the transactions of the database backups on the remote server, but that would kind of be overkill. But. you know, some customers want maximum thrust

    Best wishes,
    Adam

Leave a Reply

Your email address will not be published. Required fields are marked *